在两个日期之间获取每月确切的天数值

Question

我正在尝试获取两个日期之间某个月份的天数。理想情况下，代码应该如下所示：

Month | Days
 Jan  |  5
 Feb  |  28
March |  5

日期需要来自一个看起来像这样的表格 -

Name | Age | Address   | AddressStartDate| AddressEndDate 
Steve| 19  | randomAdd | 2018-01-26      | 2018-03-05
Steve| 21  | randomAdd | 2018-01-26      | 2018-03-05

我遇到的问题是在表格中可能会有多个具有相同开始和结束日期的项目。

我不想制作一个日历表，但我找到了一个解决方法 -

Declare @dtFrom date 
Declare @dtTo date


select @dtFrom = 2018-01-26
      ,@dtTo   = 2018-03-05

select 
    year(dt) [Year], month(dt) [Month],  
    count(*)
from 
(
    select top(datediff(d, @dtFrom, @dtTo)) dateadd(d,  row_number() over 
(order by (select null)), @dtFrom) dt
    from sys.columns) q
    group by year(dt), month(dt
)
order by [Year], [Month]

我无法想出如何将它们分开。有没有办法让它们在一个表格中呈现？

我目前大约有1500行数据，需要为其复制这些数据！

- sam smith

---

你的最终结果是什么样子？是所有行都有一个月份集合吗？还是每一行都有对应的金额？ - Gordon Linoff

如果日期跨越超过一年怎么办？ - Salman A

理想的结果是在y轴上有一列名称，在x轴上有一列月份-年份！ - sam smith

Answer 1

你走在正确的道路上，只需要使用CROSS APPLY。 示例

Declare @YourTable Table ([Name] varchar(50),[Age] int,[Address] varchar(50),[AddressStartDate] date,[AddressEndDate] date)  
Insert Into @YourTable Values 
 ('Steve',19,'randomAdd','2018-01-26','2018-03-05')
,('Steve',21,'randomAdd','2018-01-26','2018-03-05')

Select A.* 
      ,B.*
 From @YourTable A
 Cross Apply (
                Select Month=DateName(MONTH,D)
                     ,Days = count(*)
                 From (
                        Select Top (DateDiff(DAY,[AddressStartDate],[AddressEndDate])+1) D=DateAdd(DAY,-1+Row_Number() Over (Order By (Select Null)),[AddressStartDate]) 
                         From  master..spt_values n1,master..spt_values n2
                      ) B1
                 Group By Year(D),DateName(MONTH,D)
             ) B

返回

编辑 - 更新了NULL EndDate的情况

最初的回答

Select A.* 
      ,B.*
 From @YourTable A
 Cross Apply (
                Select Month=DateName(MONTH,D)
                     ,Days = count(*)
                 From (
                        Select Top (DateDiff(DAY,[AddressStartDate],IsNull([AddressEndDate],GetDate()))+1) D=DateAdd(DAY,-1+Row_Number() Over (Order By (Select Null)),[AddressStartDate]) 
                         From  master..spt_values n1,master..spt_values n2
                      ) B1
                 Group By Year(D),DateName(MONTH,D)
             ) B

最终编辑 -- 将年份和月份连接起来

Select A.* 
      ,B.*
 From @YourTable A
 Cross Apply (
                Select Month=concat(DateName(MONTH,D),'-',year(D))
                     ,Days = count(*)
                 From (
                        Select Top (DateDiff(DAY,[AddressStartDate],IsNull([AddressEndDate],GetDate()))+1) D=DateAdd(DAY,-1+Row_Number() Over (Order By (Select Null)),[AddressStartDate]) 
                         From  master..spt_values n1,master..spt_values n2
                      ) B1
                 Group By Year(D),DateName(MONTH,D)
             ) B

- John Cappelletti

---

@samsmith 请看编辑 - 已更新为NULL EndDate。这里我们使用IsNull(EndDate,GetDate())。 - John Cappelletti

我也认为这可能与列名datetime而不是date有关。我的错。 - sam smith

@samsmith 不应该有影响。它仍然可以使用datetime或date。你可能有一些虚假数据。选择* from YourTable where EndDate<StartDate，然后尝试选择* from YourTable where StartDate为空。 - John Cappelletti

明白了，谢谢！有什么办法可以将年份添加到月份后面吗？例如，如果史蒂夫在该物业居住了3年，则我可能会返回4月|30，最多3次。 - sam smith

我可以问一下我们在 spt_values 表里面做什么吗？只是为了我的未来知识！谢谢。 - sam smith