Patient Days calculcation

Question

I am currently trying to write a query to calculate patient days. My source table has VisitID, AdmitDateTime, and DischargeDateTime. A patient day is defined as a patient is in a bed at midnight. So for example, if there were 5 patients admitted on 2016-01-01 and no other patients were in the hospital from before then Patient Days would equal 5 for 2016-01-02.

I would like to display the results with the columns being date and count. I thought about building a calendar table with a CTE but I'm unsure of what the join should be to my source table. If there are no patients in a bed for a certain day I would want the count to be 0. I'm fairly new to SQL any and all help would be greatly appreciated.

What have you tried so far, what is the problem with your solution? Please read [ask] in [help]. Showing some effort highly increases our will to help. — Pred, Dec 14 '16 at 14:43
Typically, you would handle the logic of 'missing' days in application level code. — Strawberry, Dec 14 '16 at 14:44
@Strawberry would you? I've come across this kind of problem (where you have records which represent events with dates and some dates have no events, but you want to return a count for each day - even those with a count of zero) a few times in the past and have usually found it is solved in the reporting layer, rather than the application layer? — 3N1GM4, Dec 14 '16 at 17:24
There are a few suggestions on this similar (I think) question: [How do I include empty rows in a single GROUP BY DAY(date_field) SQL query?](http://stackoverflow.com/questions/592983/how-do-i-include-empty-rows-in-a-single-group-by-daydate-field-sql-query), or this one: [How to return empty groups in SQL GROUP BY clause](http://stackoverflow.com/questions/1980678/how-to-return-empty-groups-in-sql-group-by-clause). — 3N1GM4, Dec 14 '16 at 17:26
In fact, just Googling for `sql query include empty groups` returns plenty of results (both from SE and elsewhere) which should allow you to solve your own problem. — 3N1GM4, Dec 14 '16 at 17:28

score 0 · Answer 1 · answered Dec 14 '16 at 19:25

I highly recommend using a Calendar table, but if that's not an option for you then here's a solution using a recursive CTE to build your master date list.

Please be aware that this won't work if your overall date-range is more than 100 days due to limitation with cte recursion. (Again. Try and use a calendar table it will be far more efficient.)

If you're having to report on a specific month then you'll need to alter the initial cte that defines [FirstDate] and [LastDate]

DROP TABLE #Clientvisit

CREATE TABLE #ClientVisit(
VisitID INT
,AdmitDateTime DATETIME
,DischargeDateTime DATETIME
)

INSERT INTO #ClientVisit
VALUES
(1,'20160604 13:50','20160606 03:50')
,(2,'20160604 13:50','20160609 13:50')


--IF YOU'RE NOT USING A CALENDAR TABLE THEN USE RECURSIVE CTE TO BUILD DATE LIST
;WITH cte
    AS
    (
    SELECT
        MIN(CAST(AdmitDateTime AS DATE)) AS FirstDate
        ,MAX(CAST(DischargeDateTime AS DATE)) AS LastDate
    FROM
        #ClientVisit
    )
,cte_R
    AS
    (
    SELECT FirstDate,LastDate FROM cte
    UNION ALL
    SELECT DATEADD(DD,1,FirstDate),LastDate FROM cte_R
    WHERE DATEADD(DD,1,FirstDate) <= LastDate
    )
SELECT
    c.FirstDate
    ,COUNT(v.VisitID) AS BedDays
FROM
    cte_R c
        LEFT JOIN #ClientVisit v
            ON
            DATEADD(DD
                    ,1  --PLAY WITH THIS VALUE EITHER -1/0/1 TO ALLIGN THE BEDDAYS WITH THE DESIRED DATE
                    ,c.FirstDate) BETWEEN v.AdmitDateTime AND v.DischargeDateTime
GROUP BY
    c.FirstDate

Patient Days calculcation

1 Answers1