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 (
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
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.
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.

Kevin Baker
President
Data Services Partners

### Hi, I'm Kevin.

As former Director of Data Services at Ensign Services, a publicly traded company, I have an extensive background and expertise in the Skilled Nursing sector and the needs of both the management and information technology sides of the business. We're experts in the systems and metrics that support Skilled Nursing and Post Acute Healthcare businesses.

My passion is helping businesses transform their data into actionable information to make better business decisions. We are developers you can trust. Data is our passion.