使用sql server创建每周日历表

b4wnujal 于 2021-07-24 发布在 Java

关注(0)|答案(1)|浏览(432)

我正在使用microsoft sql server 2012，希望创建下表（从2018-12-02到2019-08-31）：

Clinic_code Clinic_name        Day_start        Day_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

我正在使用以下代码：

DECLARE @fromstartdate date = '2018-12-02'
DECLARE @fromenddate date = '2018-12-08'

SELECT 
    #MyTable.*, B.Day_start, C.Day_end, 
    DATEDIFF(day, MIN(B.Day_start) OVER (), 
    B.Day_start) + 1 AS Weeks_passed
FROM  
    #MyTable
CROSS APPLY 
    (SELECT TOP (DATEDIFF(DAY, @fromstartdate, DATEADD(Month, 9, @fromstartdate))) 
         Day_start = CONVERT(date, DATEADD(DAY, -1 + 7 * ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), @fromstartdate))
     FROM master..spt_values n1) B
CROSS APPLY
    (SELECT TOP (DATEDIFF(DAY, @fromenddate, DATEADD(Month, 9, @fromenddate))) 
         Day_end = CONVERT(date, DATEADD(DAY, -1 + 7 * ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), @fromenddate))
     FROM master..spt_values n1) C

但我不是第一个 @fromstartdate 以及 @fromenddate 它将远远超过2019年8月31日。你能告诉我我做错了什么吗？

sql sql-server time-series

来源：https://stackoverflow.com/questions/62840166/creating-calendar-table-per-week-using-sql-server

1条答案

按热度按时间

rwqw0loc1#

我会选择创建递归公共表表达式的方法，并将其连接到表中。使用cte，您可以创建日期列表，并通过在一周的每个开始处添加6天来定义日期范围。经过的周数按递增计算。

DECLARE @fromstartdate date = '2018-12-02'
DECLARE @fromenddate date = '2019-08-31'

;WITH cteDateList AS
(
    SELECT @fromstartdate AS Day_start, DATEADD(D, 6, @fromstartdate) AS Day_end, 1 AS Weeks_passed
    UNION ALL
    SELECT DATEADD(D, 7, Day_start), DATEADD(D, 6, Day_end), Weeks_passed + 1 FROM cteDateList
    WHERE DATEADD(D, 7, Day_start) <= @fromenddate
)

SELECT mt.clinic_code, mt.clinic_name, dl.* FROM MyTable mt
CROSS APPLY (SELECT * FROM cteDateList) dl
ORDER BY dl.Day_start, mt.clinic_code;

赞(0）回复(0）举报 2021-07-24

我来回答

使用sql server创建每周日历表

1条答案

相关问题

热门标签

最新问答