Let’s talk about how to calculate a payer length of stay!  Warning, we are going to get technical!

PCC tracks a history of a patient stays via census item records.  This view only shows changes for a resident.  For example, if their care level changes or payer changes a new record will show up.  On the other hand, if a resident has nothing to update, there will be no additional records.

Because the data is structured in this way, once a new record is added, the previous record’s “EndEffectiveDate” is updated with the new records “BeginEffectiveDate”.  What this does is creates a history of a patient’s stay over time.

*Note: since the records in this view are added on resident changes you will find active residents always have one record where the “EndEffectiveDate” is NULL or empty.  This just means that this record is still open and the resident has not been discharged from the operation.

The Challenge

The challenge with this data set is that you can’t just do a left outer join on itself to find a records “next” record.  The reason is that each patient has varying amounts of next records.

The Solution

In cases like this, we need to have the view join on itself, but recursively.  The best way to do this using T-SQL is with CTE (common table expressions).

Example Patient Stay Query

WITH cte
AS (
--Patient Census Admission Activity
SELECT
    --WE USE THIS TO CALCULATE A PATIENT STAY, THIS BUNDLES RECORDS TOGETHER FOR THEIR FIRST ADMIT TO THEIR OFFICIAL DISCHARGE
    ROW_NUMBER() OVER (PARTITION BY [PatientID] ORDER BY A.[BeginEffectiveDate] ASC) AS [Stay],
    A.[CensusID],
    A.[FacilityID],
    A.[PatientID],
    A.[BedID],
    A.[BeginEffectiveDate],
    A.[EndEffectiveDate],
    A.[PayerID],
    A.[CareLevelCode]
FROM [PCC].[dbo].[view_ods_patient_census] A
JOIN [PCC].[dbo].[view_ods_census_codes] B
    ON A.[ActionCodeID] = B.[ItemID]
    AND
    --SPECIFIES THE PATIENT IS ACTIVE (SELECT * FROM [PCC].[dbo].[view_ods_census_codes] WHERE [ItemID] = 42)
    A.[StatusCodeID] = 42
WHERE B.[ActionType] IN ('Admission', 'Readmission')
UNION ALL
--All Activity that is not a discharge
SELECT
    B.[Stay],
    A.[CensusID],
    A.[FacilityID],
    A.[PatientID],
    A.[BedID],
    A.[BeginEffectiveDate],
    A.[EndEffectiveDate],
    A.[PayerID],
    A.[CareLevelCode]
FROM [PCC].[dbo].[view_ods_patient_census] A
INNER JOIN cte B
    ON A.[PatientID] = B.[PatientID]
    AND
    --MATCH THE CURRENT RECORD WITH THE "NEXT" Record Based on Dates
    A.[BeginEffectiveDate] = B.[EndEffectiveDate]
    AND
    --Excludes records with matching begin and end dates
    --Since they are already accounted for in the previous
    --records EndEffectiveDate
    A.[BeginEffectiveDate] != ISNULL(A.[EndEffectiveDate], '1/1/1899')
JOIN [PCC].[dbo].[view_ods_census_codes] C
    ON A.[ActionCodeID] = C.[ItemID]
WHERE C.[ActionType] NOT IN ('Discharge', 'Death'))
--This example query helps you see the history of a patient and groups up their activity into patient stays.
--This section below is what you can replace to answer specific questions about your residents!
SELECT
    A.*
FROM cte A

How to use the query above

For this example, we would replace the “SELECT A.* FROM cte A” with the query below to calculate the average length of stay for Medicare in July 2017.

SELECT
[FacilityID], 
SUM(DATEDIFF(day, BeginEffectiveDate, EndEffectiveDate))/COUNT(*) As [ALOS]
FROM cte A
WHERE [PayerID] = 4 --Medicare A
AND -- In July 2017
[EndEffectiveDate] >= '20170701' AND [EndEffectiveDate] < '20170801'
GROUP BY [FacilityID]

If you have any questions, please leave a comment or reach out directly.  We'd be more than happy to help!  Monitoring your length of stays is important to ensure quality, outcomes, and relationships.