提问人:Goatfarmer03 提问时间:11/18/2023 最后编辑:Joel CoehoornGoatfarmer03 更新时间:11/20/2023 访问量:106
在 SELECT 中将日期格式化为双周存储桶
Date Format Into BiWeekly Buckets In a SELECT
问:
我有一个数据结构
开始时间 | 结束时间 | BunchOfOtherColumns |
---|
我需要创建一个新列,该列添加了一个格式化的日期,该日期落入双周存储桶(周一至周日)。SELECT
例如,对于结果中的每一行,如果日期介于 10 月 2 日至 10 月 15 日之间,则新列将显示 。如果日期介于 10 月 16 日至 10 月 29 日之间,则新列将显示 ,依此类推。EndTime
10/15/23
EndTime
10/29/23
我们可以假设我只会处理 2018 年 10 月 1 日或之后发生的数据,因此这可能是双周周期开始时的零点。第一个双周存储桶为 2018 年 10 月 1 日 - 2018 年 10 月 14 日,标记为 。10/14/2018
如果可能的话,这需要完全包含在语句中,而不是使用 .我只是将一个额外的计算列作为结果集添加到现有表中SELECT
GROUP BY
我目前正在使用以下内容,它将格式化为周一至周日的每周存储桶。SELECT
EndTime
SELECT
BunchOfOtherColumns
,FORMAT(DATEADD(DAY, 7 - DATEPART(WEEKDAY, EndTime), EndTime), 'MM/dd/yy')
FROM ...
这给了我一个很好的输出
BunchOfOtherColumns | 日期桶 |
---|
但现在我需要双周存储桶
下面是一批示例数据:
开始时间 | 结束时间 | 其他列 |
---|---|---|
2023-10-01 10:00:00 | 2023-10-01 23:59:00 | 不 |
2023-10-02 09:00:00 | 2023-10-02 12:59:00 | 滩 |
2023-10-12 15:00:00 | 2023-10-12 17:59:00 | 奈特 |
2023-10-27 10:00:00 | 2023-10-27 23:59:00 | 没有 |
预期输出
其他列 | 双周刊 |
---|---|
不 | 10/01/2023 |
滩 | 10/15/2023 |
奈特 | 10/15/2023 |
没有 | 10/29/2023 |
答:
假设你有某种类型的或列:DATE
DATETIME
SET DATEFIRST 1
SELECT i.*, DATEADD(DAY,1-CASE WHEN DATEPART(WEEK,InvoiceDateTimeUTC) % 2 = 0 THEN DATEPART(WEEKDAY,InvoiceDateTimeUTC)
ELSE 7+DATEPART(WEEKDAY,InvoiceDateTimeUTC)
END,CAST(InvoiceDateTimeUTC AS DATE)) AS BiWeeklyStartDate,
CASE WHEN DATEPART(WEEK,InvoiceDateTimeUTC) % 2 = 0 THEN DATEPART(WEEKDAY,InvoiceDateTimeUTC)
ELSE 7+DATEPART(WEEKDAY,InvoiceDateTimeUTC)
END AS DayNumber
FROM Invoices i
ORDER BY CAST(InvoiceDateTimeUTC AS DATE);
发票 ID | 发票日期时间UTC | 客户 ID | 员工 ID | 存储 ID | BiWeeklyStartDate 双周开始日期 | 天数 |
---|---|---|---|---|---|---|
58405 | 2021-06-06 19:46:15.9633333 | 241 | 59 | 47 | 2021-05-24 | 14 |
59390 | 2021-06-06 19:46:15.9633333 | 979 | 60 | 15 | 2021-05-24 | 14 |
15356 | 2021-06-07 19:48:15.9633333 | 807 | 16 | 28 | 2021-06-07 | 1 |
70300 | 2021-06-07 19:47:15.9633333 | 21 | 70 | 5 | 2021-06-07 | 1 |
24578 | 2021-06-07 19:48:15.9633333 | 784 | 25 | 57 | 2021-06-07 | 1 |
您可以使用更改模数来调整开始日期,也可以调整周数。SET DATEFIRST
评论
DATEPART(WEEK, ...)
DATEPART()
DATEDIFF(week, 0, date)
(如果使用 SQL Server 2022 或更高版本,请参阅 Charlieface 提供的更简单的答案。
给定一个定义任何两周存储桶的第一个星期一的参考日期,您可以计算天数差,然后计算以获取存储桶编号,或计算计算相对于包含两周存储桶的开始星期一的天数。FLOOR(diff / 14.0)
((diff % 14) + 14) % 14
需要使用 instead 而不是 just 来处理负偏移。同样,还需要双模输入将负数映射到所需的 0..13 范围。FLOOR(diff / 14.0)
diff / 14
((diff % 14 + 14) % 14
DECLARE @ReferenceDate DATE = '2023-10-02' -- Reference Monday
;WITH CTE_Dates AS (
SELECT CONVERT(DATETIME, '2023-09-01 13:45') Dt
UNION ALL
SELECT DATEADD(day, 1, D.Dt) AS Dt
FROM CTE_Dates D
WHERE D.Dt < '2023-10-31'
)
SELECT
D.Dt,
DATENAME(weekday, D.Dt) AS WeekDay,
DATEDIFF(day, @ReferenceDate, D.Dt) AS DayDiff,
FLOOR(DATEDIFF(day, @ReferenceDate, D.Dt) / 14.0) AS BucketNo,
((DATEDIFF(day, @ReferenceDate, D.Dt) % 14) + 14) % 14 AS BucketDay,
CONVERT(DATE, DATEADD(day, - ((DATEDIFF(day, @ReferenceDate, D.Dt) % 14) + 14) % 14, D.Dt)) AS BucketStartDt,
CONVERT(DATE, DATEADD(day, 13 - ((DATEDIFF(day, @ReferenceDate, D.Dt) % 14) + 14) % 14, D.Dt)) AS BucketEndDt
FROM CTE_Dates D
OPTION (MAXRECURSION 1000)
部分结果:
Dt | 工作日 | 日差异 | 铲斗否 | 水桶日 | BucketStartDt | BucketEndDt |
---|---|---|---|---|---|---|
2023-10-01 13:45 | 星期日 | -1 | -1 | 13 | 2023-09-18 | 2023-10-01 |
2023-10-02 13:45 | 星期一 | 0 | 0 | 0 | 2023-10-02 | 2023-10-15 |
2023-10-03 13:45 | 星期二 | 1 | 0 | 1 | 2023-10-02 | 2023-10-15 |
2023-10-04 13:45 | 星期三 | 2 | 0 | 2 | 2023-10-02 | 2023-10-15 |
2023-10-05 13:45 | 星期四 | 3 | 0 | 3 | 2023-10-02 | 2023-10-15 |
2023-10-06 13:45 | 星期五 | 4 | 0 | 4 | 2023-10-02 | 2023-10-15 |
2023-10-07 13:45 | 星期六 | 5 | 0 | 5 | 2023-10-02 | 2023-10-15 |
2023-10-08 13:45 | 星期日 | 6 | 0 | 6 | 2023-10-02 | 2023-10-15 |
2023-10-09 13:45 | 星期一 | 7 | 0 | 7 | 2023-10-02 | 2023-10-15 |
2023-10-10 13:45 | 星期二 | 8 | 0 | 8 | 2023-10-02 | 2023-10-15 |
2023-10-11 13:45 | 星期三 | 9 | 0 | 9 | 2023-10-02 | 2023-10-15 |
2023-10-12 13:45 | 星期四 | 10 | 0 | 10 | 2023-10-02 | 2023-10-15 |
2023-10-13 13:45 | 星期五 | 11 | 0 | 11 | 2023-10-02 | 2023-10-15 |
2023-10-14 13:45 | 星期六 | 12 | 0 | 12 | 2023-10-02 | 2023-10-15 |
2023-10-15 13:45 | 星期日 | 13 | 0 | 13 | 2023-10-02 | 2023-10-15 |
2023-10-16 13:45 | 星期一 | 14 | 1 | 0 | 2023-10-16 | 2023-10-29 |
2023-10-17 13:45 | 星期二 | 15 | 1 | 1 | 2023-10-16 | 2023-10-29 |
2023-10-18 13:45 | 星期三 | 16 | 1 | 2 | 2023-10-16 | 2023-10-29 |
2023-10-19 13:45 | 星期四 | 17 | 1 | 3 | 2023-10-16 | 2023-10-29 |
请参阅此 db<>fiddle。
原始帖子和随后的评论在哪些星期一构成两周桶的开始或中间方面并不一致,但可以根据需要进行更改以适应最终决定。(它甚至不需要是星期一。@ReferenceDate
评论
您可以使用简单的算术来计算这一点。
考虑
(DATEADD(DAY, (FLOOR((1 - (DATEDIFF(DAY, '2018-9-30', EndTime) / 14.0 % 1)) * 14)) + 7, EndTime))
SELECT BunchOfOtherColumns
,FORMAT((DATEADD(DAY,(FLOOR((1 - (DATEDIFF(DAY,'2018-9-30',EndTime) / 14.0 % 1)) * 14)) + 7,EndTime)) ,'MM/dd/yy')
FROM ...
我不太明白 10 年 15 月 23 日将如何结束,所以也许我离开了。我认为这将是第 8 个或第 22 个,但出于这个原因,我添加了一个 +7。
评论
在 SQL Server 2022 中,只能使用 DATE_BUCKET
函数。
这可以将日期分成几组,您可以为其指定分段的类型和大小以及来源。
似乎与您所说的相反,为了让水桶按照您所说的方式掉出来,您需要将原点移动到 .20180924
DATE_BUCKET(week, 2, EndTime, CAST('20180924' AS datetime))
要获得存储桶的末尾,只需添加 13 天
DATEADD(day, 13, DATE_BUCKET(week, 2, EndTime, CAST('20180924' AS datetime)))
评论
SELECT @@version;
会告诉你你的版本号。SSMS 有时会对语法感到困惑,尤其是在未使用最新版本的 SSMS 时。
评论
DATEDIFF