Determine Patient Days for a set month SQL-CodePudding

I have written a query that will return total patient days. However what I need is to return patient days for the precious month. I am using the latest version of SQL and have read only access to the server so I do not think I can create a calendar table. I have tried a recursive but could not get that to work. Any assistance would be appreciated.

DECLARE @report_date DATETIME
SET @report_date = GETDATE()

SELECT OrgKey
    ,visID
    ,visPatID
    ,visInternal
    ,visName
    ,visAssignedNS
    ,visAssignedRoom
    ,visAssignedBed
    ,visAdmitDT
    ,ISNULL(visDischargeDT,@report_date) AS disDT
    ,DATEDIFF(DAY,visAdmitDT,ISNULL(visDischargeDT,@report_date))AS PT_Days
  FROM MH_Pharmacy_Hub.MC.dbo_Visits
  WHERE visDischargeDT IS NOT NULL

CodePudding user response：

This looks like a job for EOMONTH().

The expression EOMONTH(@report_date, -2) gets you midnight on the last day of the month before last.

So, DATEADD(DAY, 1, EOMONTH(@report_date, -2)) gets you midnight on the first day of last month.

Similarly, DATEADD(DAY, 1, EOMONTH(@report_date, -1)) gets you midnight on the first day of the present month.

Then you use

WHERE visDischargeDT IS NOT NULL
  AND visAdmitDT >= DATEADD(DAY, 1, EOMONTH(@report_date, -2))
  AND visAdmitDT <  DATEADD(DAY, 1, EOMONTH(@report_date, -1))

to filter the rows for which the admit date is within the last month but before the current month. Notice the < at the end of the date range.

This crudely filters on admit date. It doesn't split up day reporting for patients admitted in one month and discharged in a subsequent month. If you want that, please ask another question and spell out the requirements for that.

CodePudding user response：

While the EOMonth answer is helpful, this will only get you admissions that start AND end in the last month. What about if they were admitted 2 months ago and discharged last month, or admitted last month and discharged this month? That was still patient hours in the last month you need to count.

This query UNIONS 3 queries together, one for each of these 3 conditions so you catch all patient hours in the previous month.

DECLARE @report_date DATETIME
SET @report_date = GETDATE()

DECLARE @LastMonthStart DATETIME =DATEADD(DAY, 1, EOMONTH(@report_date, -2))
DECLARE @LastMonthEnd DATETIME = DATEADD(DAY, 1, EOMONTH(@report_date, -1))     

SELECT OrgKey
    ,visID
    ,visPatID
    ,visInternal
    ,visName
    ,visAssignedNS
    ,visAssignedRoom
    ,visAssignedBed
    ,visAdmitDT
    ,ISNULL(visDischargeDT,@report_date) AS disDT
    ,DATEDIFF(DAY,visAdmitDT,visDischargeDT)AS PT_Days
  FROM MH_Pharmacy_Hub.MC.dbo_Visits
  WHERE visDischargeDT IS NOT NULL
  AND  (visAdmitDT BETWEEN @LastMonthStart AND @LastMonthEnd AND visDischargeDT BETWEEN @LastMonthStart AND @LastMonthEnd ) -- whole visit in last month
UNION ALL
SELECT OrgKey
    ,visID
    ,visPatID
    ,visInternal
    ,visName
    ,visAssignedNS
    ,visAssignedRoom
    ,visAssignedBed
    ,visAdmitDT
    ,ISNULL(visDischargeDT,@report_date) AS disDT
    ,DATEDIFF(DAY,@LastMonthStart,visDischargeDT)AS PT_Days
 FROM MH_Pharmacy_Hub.MC.dbo_Visits
 WHERE visDischargeDT IS NOT NULL
  AND (visAdmitDT < @LastMonthStart AND visDischargeDT BETWEEN @LastMonthStart AND @LastMonthEnd) -- begin of last month span
  UNION ALL 
SELECT OrgKey
    ,visID
    ,visPatID
    ,visInternal
    ,visName
    ,visAssignedNS
    ,visAssignedRoom
    ,visAssignedBed
    ,visAdmitDT
    ,ISNULL(visDischargeDT,@report_date) AS disDT
    ,DATEDIFF(DAY,visAdmitDT,@LastMonthEnd)AS PT_Days
FROM MH_Pharmacy_Hub.MC.dbo_Visits
WHERE visDischargeDT IS NOT NULL
AND (visDischargeDT > @LastMonthEnd AND visAdmitDT BETWEEN @LastMonthStart AND @LastMonthEnd) -- end of last month span