2019: Week 20 Solution
You can find our full solution workflow below and download it here!
Once again, I’d like to preface this by saying there is single correct solutions; there are many ways to achieve the correct outcome as evidenced this week on everyone’s Twitter solution posts! With that said, here’s how we went about doing it.
Something I learnt from this is that you can replace a field using a new calculated field with the same name. This is great when we don’t want to have to create a new separate field.
Now we need to prepare the data on the CPV side. To do this we simply need to split [Length of Stay] on the hyphen (‘-‘). This gives us (what we can rename into) [Min Length of Stay] and [Max Length of Stay] for each cost bracket.
Finally, we can use another inner join between the ‘Scaffold’ and ‘Patient’ data and the CPV data on the following two conditions:
This gives us the total cost, average cost per patient, and number of patients for every date in our data.
This gives us the total cost and average cost per day for each patient. Finally, all that is left is to use the ROUND() function to round all our data to two decimal places.
Once again, I’d like to preface this by saying there is single correct solutions; there are many ways to achieve the correct outcome as evidenced this week on everyone’s Twitter solution posts! With that said, here’s how we went about doing it.
Join the ‘Scaffold’ to the ‘Patient’ table to get all the dates
To start with, we aim to get all the actual dates where a patient is in the hospital. To do this we simply start by using an inner join between ‘Scaffold’ and ‘Patient’ on [Length of Stay] > [Value]. We can then get the complete list of dates by using the DATEADD() function to add [Value] to [Date].|
Date
|
|
DATE(
DATEADD('day',[Value],[Date])
)
|
Something I learnt from this is that you can replace a field using a new calculated field with the same name. This is great when we don’t want to have to create a new separate field.
Join the ‘Cost per Visit’ table in to get the cost for each patient for each day
We’re going to use the [Value] field from before to join the data from the ‘Cost per Visit’ (CPV) table. However [Value] starts from zero, whilst the [Length of Stay] field in CPV starts from 1. To make sure we can join later, we first need to simply add ‘1’ to the [Value] field.|
Value
|
|
[Value] + 1
|
Now we need to prepare the data on the CPV side. To do this we simply need to split [Length of Stay] on the hyphen (‘-‘). This gives us (what we can rename into) [Min Length of Stay] and [Max Length of Stay] for each cost bracket.
Finally, we can use another inner join between the ‘Scaffold’ and ‘Patient’ data and the CPV data on the following two conditions:
- [Value] >= [Min Length of Stay]
- [Value] <= [Max Length of Stay]
Aggregating to get our final outputs
Now we have all our data we just need to aggregate it correctly. As we want to do two different aggregations using the [Cost Per Day] (one where we sum it up, and one where we take the average), we first need to duplicate it. I’ve named my duplicate [Avg Cost Per Day] since that’s what we’ll be doing with it.Daily Costs
In order to get the daily costs, we need to use the following aggregation:- GROUP on [Date]
- AVG of [Avg Cost Per Day]
- SUM of [Cost Per Day]
- CNTD of [Name]
This gives us the total cost, average cost per patient, and number of patients for every date in our data.
Patient Costs
In order to get the individual patient costs we need to use the following aggregation:- GROUP on [Name]
- AVG of [Avg Cost Per Day]
- SUM of [Cost Per Day]
This gives us the total cost and average cost per day for each patient. Finally, all that is left is to use the ROUND() function to round all our data to two decimal places.
