I'm querying a view that gives me data about multiple hospital patient episodes. It includes two columns that give me the start date of an episode, and an end date.
What I'm attempting to do (bed stay calculations, for anyone NHS) is calculate how many days fall between the 2 dates (easy), but then group the day count by the month it falls in.
e.g. if Startdate = 17th November 2016, and Enddate = 3rd Jan 2017, I'm after an output along the lines of:
Year | Month | No. Bed Days |
-----------------------------
2016 | 11 | 13 |
2016 | 12 | 31 |
2017 | 1 | 3 |
I've got some code that kind of works along the lines I'm after, based on inputting date parameters:
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]
However, because the data contains hundreds of episodes and I want to calculate and sum the bed days for all of these into the single table, rather than supplying date parameters, I want to point the code to the view Inpatients.vw_IP_Episodes and use the following variables to supply the parameters from the data: Episode_Start_Date Episode_End_Date.
I've tried modifying the code to declare the variable names as the parameters, and tried modifying the code to point to the view (as below), but I'm just getting syntax errors.
Please can anyone advise on how to correctly formulate the syntax?
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]
With thanks to @Kannan_Kandasamy, the solution was obtained by combining a cross apply approach to the original query, as below:
select year(dt) [Year], month(dt) [Month], count(*) 'No. Bed Days'
from Inpatients.vw_IP_Episodes
cross apply
(
select top(datediff(day, 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]
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments