sql-server-2008 - 计算两个日期之间的天数，然后按月求和并分组

Question

我正在查询一个 View ，该 View 为我提供了有关多个医院患者发作的数据。它包括两列，分别为我提供剧集的开始日期和结束日期。

我正在尝试做的(床位计算，适用于任何 NHS)是计算这两个日期之间有多少天(简单)，然后按它所在的月份对天数进行分组。

例如如果 Startdate = 2016 年 11 月 17 日，并且 Enddate = 2017 年 1 月 3 日，我正在按照以下方式输出:

Year | Month | No. Bed Days |
-----------------------------
2016 |  11   |     13       |
2016 |  12   |     31       |
2017 |  1    |      3       |

我有一些代码可以根据输入的日期参数按照我想要的方式工作:

declare @dtFrom date 
declare @dtTo date

select @dtFrom = '2016-11-17'
      ,@dtTo   = '2017-01-03'


select year(dt) [Year], month(dt) [Month],  count(*) 'No. Bed Days'
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]

但是，因为数据包含数百个情节，我想计算所有这些的住院天数并将其汇总到一个表中，而不是提供日期参数，我想将代码指向 View Inpatients .vw_IP_Episodes 并使用以下变量提供数据中的参数:Episode_Start_Date Episode_End_Date。

我尝试修改代码以将变量名称声明为参数，并尝试修改代码以指向 View (如下所示)，但我只是遇到语法错误。

请问任何人都可以就如何正确制定语法提出建议吗？

select year(dt) [Year], month(dt) [Month],  count(*) 'No. Bed Days'
from Inpatients.vw_IP_Episodes
where
(select top(datediff(d, Episode_Start_Date, Episode_End_Date)) dateadd(d,  row_number() over (order by (select null)), Episode_Start_Date) dt
      from sys.columns) q
group by year(dt), month(dt)
order by [Year], [Month]

Answer 1

如果您想在两个日期之间生成天数，请检查下面的方法

declare @dtFrom date    = '2016-11-17'
declare @dtTo date      = '2017-01-03'

select  year(RowN)  as [Year],
        Month(RowN) as [Month],
        count(*)    as [No of Bed Days] from (
    select top(datediff(day, @dtfrom, @dtto)+1) RowN = dateadd(day, ( row_number() over(order by (select null)) - 1), @dtFrom) 
    from master..spt_values s1, master..spt_values s2
) a
group by year(a.RowN), Month(a.RowN)

输出:

+------+-------+----------------+
| Year | Month | No of Bed Days |
+------+-------+----------------+
| 2017 |     1 |              3 |
| 2016 |    11 |             14 |
| 2016 |    12 |             31 |
+------+-------+----------------+

我认为我的查询也考虑了第一天，如果您不需要，您可以相应地进行更改。如果您在不同的表中有开始日期和结束日期，您也可以交叉应用到您的表。但理想的性能方案是像生成的表一样具有日期维度，并加入这是更快的方法。

我使用交叉应用将您的查询更改如下

select year(dt) [Year], month(dt) [Month],  count(*) 'No. Bed Days'
from Inpatients.vw_IP_Episodes
cross apply
(
    select top(datediff(day, fromdatefromyourtable, todatefromyourtable)+1) dt = dateadd(day, ( row_number() over(order by (select null)) - 1), fromdatefromyourtable) from master..spt_values s1, master..spt_values s2
) q
group by year(dt), month(dt)
order by [Year], [Month]

您需要将 fromdatefromyourtable 替换为表格的 fromdate 列，并将 todatefromyourtable 替换为表格的 todate 列。