动态 SQL - 将午夜计数分配给正确的月份

Question

这是一个相当普遍的问题，我见过很多人把解决方案放在一起，但它从来都不太正确。希望这个论坛能解决这个问题。我有一张桌子：

create table temp
(   PatientID varchar(12),
AdmitDate datetime,
DischargeDate datetime
)
insert into temp values ('Patient1','1/30/2020 13:23:44', '2/2/2020 15:12:52')

我想计算的是患者在正确月份入院的午夜次数。因此，在上面的例子中，患者将在 1/31、2/1 和 2/2 日期的午夜入院。所以我在 sql 中的输出应该看起来像这样：

01-2020         02-2020
-------         --------
1                2

我知道它必须是动态 SQL，因为需要根据查询的日期范围创建列。虽然，我对下一步感到非常困惑。

Answer 1

create table #temp 
(   PatientID varchar(12),
AdmitDate datetime,
DischargeDate datetime
)
insert into #temp values ('Patient1','1/30/2020 13:23:44', '2/2/2020 15:12:52')
--Virtually creates a dates table
;with dates(thedate) as (
select dateadd(yy,years.number,0)+days.number
from master..spt_values years
join master..spt_values days
on days.type='P' and days.number < datepart(dy,dateadd(yy,years.number+1,0)-1)
where years.type='P' and years.number between 100 and 150
-- note: 100-150 creates dates in the year range 2000-2050
--       adjust as required
)
select dateadd(m,datediff(m, 0, d.thedate),0) [Month], count(1) PatientDays
from dates d
join #temp t on d.thedate between t.[AdmitDate] and t.[DischargeDate]
group by datediff(m, 0, d.thedate)
order by [Month];