在SQL Server中创建日历表 [英] Create calendar table in SQL Server

问题描述

我正在使用Microsoft SQL Server 2012，并且正在创建下表：

I am using Microsoft SQL Server 2012 and I am creating the following table:

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_days_passed'列，该列实际上是计算从开始日期起的天数：

I adapted the code here from @JohnCappelletti, but I'm struggling to include the 'D_days_passed' column which essentially counts the number of days since the Start_date:

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

如果有时间，

问题：如何将代码调整为，而不是将每个诊所的日期从每天的日期改为另一个日期，我想为每个诊所介绍从同一天开始的同一周时间，例如星期日：

Question: how could the code be adjusted to, instead of including every day from one date to another per Clinic, I wanted to introduce the same week period per clinic, such as this, from Saturday-Sunday:

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

推荐答案

您可以只使用窗口函数来获取最早的日期，然后使用 datediff（）：

You can just use window functions to get the earliest date and then datediff():

Select t.*, B.D,
       datediff(day, min(date) over (), date) + 1 as days_passed
From  #MyTable t 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;

这也应该可以工作数周。

This should also work for weeks.

这篇关于在SQL Server中创建日历表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！