我有一个现有的项目,它是一份周报。其中一个参数是周范围。这很好,但他们需要重新提交报告,而不是在一年的第一天开始一周,他们需要在第一个星期四开始一周并以结婚结束。我有这个问题
SELECT MIN([DATE]) as StartWeek
,MAX([DATE]) as EndWeek
,DATENAME(WEEK,Convert(datetime,CONVERT(VARCHAR(10),CONVERT(DATE,[DATE]),101))) as [WEEK]
FROM [somedb]
WHERE DATENAME(Year,Convert(datetime,CONVERT(VARCHAR(10),CONVERT(DATE,[DATE]),101))) = 2018
GROUP BY DATENAME(WEEK,Convert(datetime,CONVERT(VARCHAR(10),CONVERT(DATE,[DATE]),101)))
此查询的输出为
Start Week End Week WEEK
2018-01-01 2018-01-07 1
预期输出应为
Start Week End Week Week
2018-1-4(since its the first thursday of the week) 2018-1-10 1
是的,我可以DATEADD
,但周号仍将确认2018-1-8为WEEK 2
DECLARE @WeekDay Varchar(500),
@Add Int,
@DATE Date;
SET @DATE = '2018-01-01';
SELECT @WeekDay = DATENAME(DW,@DATE);
SELECT @Add = CASE
WHEN @WeekDay = 'Thursday' THEN
0
WHEN @WeekDay = 'Friday' THEN
6
WHEN @WeekDay = 'Saturday' THEN
5
WHEN @WeekDay = 'Sunday' THEN
4
WHEN @WeekDay = 'Monday' THEN
3
WHEN @WeekDay = 'Tuesday' THEN
2
WHEN @WeekDay = 'Wednesday' THEN
1
END
SELECT DATEADD(DD,@Add, CONVERT(DATE,@DATE)) AS WeekStart,
DATEADD(DD,@Add+6, CONVERT(DATE,@DATE)) AS WeekEnd,
1 AS Week
我们可以使用SET DATEFIRST语句将一周的第一天设置为星期四。这意味着,进一步的计算将需要周四到周三的一周,而不是通常的一周。
然后,您可以将数据限制为从一年中的第一个星期四开始,最后,只需从WEEK
中减去1即可获得所需的输出。
set datefirst 4 --Set Thursday as first day of the week
SELECT MIN(dt) as StartWeek, MAX(dt) as EndWeek, DATENAME(WEEK,dt)-1 as [WEEK]
FROM #tbl
WHERE YEAR(dt) = 2018
AND dt >= (select min(dt) from #tbl where datename(WEEKDAY,dt) = 'Thursday')
GROUP BY DATENAME(WEEk,dt)
如果从筛选器中删除AND
条件,则第一行将是第0周,其中包含一年中第一个星期四之前的三天。即便如此,从2018年1月4日开始的一周仍然是数字1。
更新:以下内容应该能够在2年内对周数进行调整。
set datefirst 4 --Set Thursday as first day of the week
select *, case when datepart(week,d1) > datepart(week,d2) then datepart(week,d1) else datepart(week,d2)-1 end from
(
select d1, max(d2) d2 from
(select t1.dt d1, t2.dt d2
from #tbl t1
inner join #tbl t2 on datediff(day,t1.dt,t2.dt) between 1 and 6
and t1.dt < t2.dt
and datename(weekday, t1.dt) = 'Thursday') s
group by d1) t
- 再次将本周的开始时间设置为周四
- 接下来,获取数据集中的所有星期四,并找到相应的周末
- 最后,检查一周是否在同一年——如果不是,则取前一年开始日期的周数。这将给你带来你所期望的连续性
我在这里设置了一个测试:https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=75582819ff0defb7366592f4a89e9604