CODEWITHSUNDEEP
sqlsql-server
Conger88
Conger88

Reputation: 55

SQL row for each Date beween two dates

I am using SQL Server Management Studio 18 and I am trying to return a row for each date between two dates. This is a bed census report with the following requirements.

All patients who are in that bed at midnight 23:59. If a person was admitted 2016-07-08 and discharged 2016-07-11 this will have 3 rows. Outputting something like this

Epidsode date ,admitted date, discharged date, Ward
2016-07-08  2016-07-08   2016-07-11  Ward a
2016-07-09  2016-07-08   2016-07-11  Ward a
2016-07-10  2016-07-08   2016-07-11  Ward b

Also if someone was admitted and discharged in the same day then this will show one row.

Is this something like this possible to do?

I have tried searching other topics but cannot find exact help Any help would be great

Upvotes: 0

Views: 100

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269445

You can use a recursive CTE:

with cte as (
      select admitted_date as episode_date, admitted_date, discharge_date, ward
      from t
      union all
      select dateadd(day, 1, episode_date), admitted_date, discharge_date, ward
      from cte
       where episode_date < dateadd(day, -1, discharge_date)
    )
select *
from cte
with option (maxrecursion 0);

Note: If there are ever more than 100 days, you will need option (maxrecursion 0).

Here is a db<>fiddle.

Upvotes: 2

Related Questions