SQL中一个月内一周的天数

Question

使用SQL Server 2019，因此也使用T-SQL，我试图获得特定周的天数，由ISO 8601周定义确定，落在特定月份内。

包含数据的表有以下字段:

Year |  Month  |  IsoWeek
-----------------------
2020 |   12    |   50
2020 |   12    |   51
2020 |   12    |   52
2020 |   12    |   53
2021 |   01    |   01
2021 |   01    |   02
2021 |   01    |   03

例如，2020年的最后一周，第53周，从2020年12月28日星期一开始，到2021年1月3日星期日结束。

所以我想为Dec2020获得4，为Jan2021获得3，以便创建如下所示的新表:

Year |  Month  |  IsoWeek  |  WeekDays
------------------------------------
2020 |   12    |     52    |     7
2020 |   12    |     53    |     4
2021 |   01    |     53    |     3
2021 |   01    |     01    |     7
2021 |   01    |     02    |     7

是否有一种方法来做它完全在SQL?

Answer 1

这是一种SQL Server设置(或技巧)，因为1900年1月1日是星期一。因为这是SQL Server开始计数的地方，所以更容易定位任何一个月的第一个星期四。顺便感谢Jeff Moden。我从他写的东西里得到了这个。也许现在有更好的方法，idk

with iso_dts_cte(yr, mo, wk) as (
select * from (values ('2020', '12', '50'),
('2020', '12', '51'),
('2020', '12', '52'),
('2020', '12', '53'),
('2021', '01', '01'),
('2021', '01', '02'),
('2021', '01', '03')) v(yr, mo, wk))
select iso.*, v.*
from iso_dts_cte iso
cross apply (values (cast(dateadd(wk,datediff(wk,0,'01/04/'+iso.yr),0)+((iso.wk-1)*7) as date), 
cast(dateadd(wk,datediff(wk,0,'01/04/'+iso.yr),0)+((iso.wk)*7)-1 as date))) v(start_dt, end_dt);

yr      mo  wk  start_dt    end_dt
2020    12  50  2020-12-07  2020-12-13
2020    12  51  2020-12-14  2020-12-20
2020    12  52  2020-12-21  2020-12-27
2020    12  53  2020-12-28  2021-01-03
2021    01  01  2021-01-04  2021-01-10
2021    01  02  2021-01-11  2021-01-17
2021    01  03  2021-01-18  2021-01-24

要将周范围扩展为天数，然后按日历年和日历月计数，您可以尝试这样做。

[编辑]我的理解是你要找的日期结构是1)公历年，2)公历月，3)iso星期。现在输出似乎与示例匹配。但是，没有一种方法可以像示例那样显示ORDER BY。

with
iso_dts_cte(yr, mo, wk) as (
select * from (values ('2020', '12', '50'),
('2020', '12', '51'),
('2020', '12', '52'),
('2020', '12', '53'),
('2021', '01', '01'),
('2021', '01', '02'),
('2021', '01', '03')) v(yr, mo, wk)),
days_cte(n) as (
select * from (values (1),(2),(3),(4),(5),(6),(7)) v(n))
select year(dt.calc_dt) cal_yr, month(dt.calc_dt) cal_mo, iso.wk, count(*) day_count
from iso_dts_cte iso
cross apply (values (cast(dateadd(wk,datediff(wk,0,'01/04/'+iso.yr),0)+((iso.wk-1)*7) as date), 
cast(dateadd(wk,datediff(wk,0,'01/04/'+iso.yr),0)+((iso.wk)*7)-1 as date))) v(start_dt, end_dt)
cross join days_cte d
cross apply (values (dateadd(day, d.n-1, v.start_dt))) dt(calc_dt)
group by year(dt.calc_dt), month(dt.calc_dt), iso.wk;

cal_yr  cal_mo  wk  day_count
2020    12      50  7
2020    12      51  7
2020    12      52  7
2020    12      53  4
2021    1       01  7
2021    1       02  7
2021    1       03  7
2021    1       53  3

Answer 2

我假设您可以使用:

Select DatePart(weekday, '2021/01/01')

和每个月的循环，这里的值是5，因为一周的一天是星期五，因此第一个星期有3天，上一个月有4天，因为每周有7天。

如果你有周数1到53和年份，如果它是一个月的第一周或最后一周，你需要使用上面写的工作日逻辑来计算。否则就是7。

您可以创建一个具有类似逻辑的标量函数，并使用其中的函数创建表。我相信你在期待一些更简单的事情，但现在我想不起来。

也可以使用"SET DATEFIRST 1;"更改一周的第一天(星期一/星期日)。

最终创建了一个概念证明:

CREATE FUNCTION calculateWeekDays(@y int,@m int,@w int)
RETURNS INT
AS BEGIN
DECLARE @numberOfDays INT = 7;
DECLARE @firstDayOfMonth datetime = DATEFROMPARTS (@y, @m, 1);
DECLARE @nextMonth datetime = (SELECT DATEADD(MONTH, 1, @firstDayOfMonth));
DECLARE @lastDayOfMonth datetime = (SELECT DATEADD(DAY, -1, @nextMonth));
DECLARE @weekOfYearStartOfMonth INT = (select DatePart(week, @firstDayOfMonth));
DECLARE @weekOfYearEndOfMonth INT = (select DatePart(week, @lastDayOfMonth));
DECLARE @firstWeekOfMonth INT = (select datediff(week, dateadd(week, datediff(day,0,dateadd(month, datediff(month,0,@firstDayOfMonth),0))/7, 0),@firstDayOfMonth-1) + 1);
DECLARE @lastWeekOfMonth INT = (select datediff(week, dateadd(week, datediff(day,0,dateadd(month, datediff(month,0,@lastDayOfMonth),0))/7, 0),@lastDayOfMonth-1) + 1);
IF @w - @weekOfYearStartOfMonth % 52 = 0
SET @numberOfDays = (select 8-DatePart(weekday, @firstDayOfMonth));
ELSE IF @w = @weekOfYearEndOfMonth
SET @numberOfDays = (select DatePart(weekday, @nextMonth)-1);
RETURN @numberOfDays;
END
GO

从标量函数，你可以使用

得到结果:

select yr, mo, wk, dbo.calculateWeekDays(yr,mo,wk) as wd from (
values (2020, 12, 50),
(2020, 12, 51),
(2020, 12, 52),
(2020, 12, 53),
(2021, 01, 01),
(2021, 01, 02),
(2021, 01, 03),
(2021, 03, 13),
(2021, 03, 14),
(2021, 04, 14),
(2021, 04, 15)) v(yr, mo, wk)

它给出如下结果:

<表类>年莫周wdtbody><<tr>202012507202012517202012527202012534202111320211272021137,,,,20213137202131432021414420214157