我正在使用microsoft sql server 2012并创建下表:
Clinic_code Clinic_name D D_days_passed
------------------------------------------------------
A123 NAME1 2018-12-01 1
A124 NAME2 2018-12-01 1
A125 NAME3 2018-12-01 1
[...]
A123 NAME1 2018-12-02 2
A124 NAME2 2018-12-02 2
A125 NAME3 2018-12-02 2
[...]
A123 NAME1 2018-12-03 3
A124 NAME2 2018-12-03 3
A125 NAME3 2018-12-03 3
我在这里改编了@johncappelletti的代码,但是我很难包含'd\u days\u passed'列,它基本上统计自开始日期以来的天数:
DECLARE @fromdate date = '2018-12-01'
SELECT #MyTable.*, B.D
FROM #MyTable
CROSS APPLY
(SELECT TOP (DATEDIFF(DAY, @fromdate, DATEADD(Month, 9, @fromdate)) + 1)
D = CONVERT(date, DATEADD(DAY, -1 + ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), @fromdate))
FROM master..spt_values n1) B
如果你有时间,
问题:如何调整代码,而不是将每个诊所从一个日期到另一个日期的每一天都包括在内,我想为每个诊所引入相同的周周期,例如,从周六到周日:
Clinic_code Clinic_name D_start D_end Weeks_passed
-------------------------------------------------------------------------
A123 NAME1 2018-12-02 2018-12-08 1
A124 NAME2 2018-12-02 2018-12-08 1
A125 NAME3 2018-12-02 2018-12-08 1
[...]
A123 NAME1 2018-12-09 2018-12-15 2
A124 NAME2 2018-12-09 2018-12-15 2
A125 NAME3 2018-12-09 2018-12-15 2
[...]
A123 NAME1 2018-12-16 2018-12-22 3
A124 NAME2 2018-12-16 2018-12-22 3
A125 NAME3 2018-12-16 2018-12-22 3
1条答案
按热度按时间p1tboqfb1#
您只需使用窗口函数来获取最早的日期,然后
datediff()
:这也应该持续几个星期。