CTE: Make Your Data Easier to Query

Ever wish your data was a little different so it is easier to query? Don’t fret, we have all had those queries that are gigantic and become too cumbersome to read through or modify. A CTE, a.k.a. Common Table Expression, can be the savior you have been wishing for. They are great for separating different pieces of your query and performing some tasks before your query runs.

To use a CTE:

1. Use a WITH clause.
2. Add a name for the CTE followed by AS.
3. Within parentheses, put in the query you want to run before your main query.

Sounds easy, right? I am not pulling the wool over your eyes here. It is that easy. Let’s break it down with a simple example.

Example

Objective

Output total charges for each patient.

Tables

1. Visit

2. Charge

Steps

1. Build CTE
Reshape the charge data to the format that is easiest to query. SUM the amount column and group by VisitId to return total charge by visit.

WITH 
Charges AS 
( 
    SELECT VisitId, SUM(Amount) AS 'Charges' 
    FROM dbo.Charge 
    GROUP BY VisitId 
)

With this we have successfully transformed the charge table:

2. Build Main Query
Join the Visit table to Charges to get the total charges for each patient.

SELECT VIS.VisitId, CHG.Charges
FROM dbo.Visit AS VIS
JOIN Charges AS CHG
    ON VIS.VisitId = CHG.VisitId

3. Execute Script

WITH Charges
AS
(
    SELECT VisitId, SUM(Amount) AS 'Charges'
    FROM dbo.Charge
    GROUP BY VisitId
)
SELECT VIS.VisitId, CHG.Charges
FROM dbo.Visit AS VIS
JOIN Charges AS CHG
    ON VIS.VisitId = CHG.VisitId

Just when you think it can’t get any better it does!

We can take this a step further and use more than one CTE in a query.  In the next example we will use two to solve a different type of problem.

Example

Objective

Output current price and CPT code for each charge in the charge dictionary.

Tables

1. ChargeDictionary

2. ChargePrice

3. ChargeCode

Steps

1. Build PriceDate CTE
Reshape the ChargePrice table to output the latest EffectiveDate. Notice the comma at the end of this one. That is how we tell SQL Server we want more than one CTE.

WITH
PriceDate AS
(
SELECT ChargeProcedure, MAX(EffectiveDate) AS 'EffectiveDate'
FROM dbo.ChargePrice
GROUP BY ChargeProcedure
),

2. Build CodeDate CTE
Reshape the ChargeCode table to output the latest EffectiveDate.

CodeDate AS
(
SELECT ChargeProcedure, MAX(EffectiveDate) AS 'EffectiveDate'
FROM dbo.ChargeCode
GROUP BY ChargeProcedure
)

3. Build Main Query
Join the PriceDate and CodeDate along with the ChargePrice and ChargeCode tables to get only the latest price and code for each charge.

SELECT CD.ChargeProcedure, CP.Price, CC.Code
FROM 
    dbo.ChargeDictionary AS CD
    JOIN PriceDate AS P ON P.ChargeProcedure = CD.ChargeProcedure
    JOIN dbo.ChargePrice AS CP ON CD.ChargeProcedure = CP.ChargeProcedure 
        AND CP.EffectiveDate = P.EffectiveDate
    JOIN CodeDate AS C ON C.ChargeProcedure = CD.ChargeProcedure
    JOIN dbo.ChargeCode AS CC ON CD.ChargeProcedure = CC.ChargeProcedure 
        AND CC.EffectiveDate = C.EffectiveDate

4. Execute Script

WITH
PriceDate AS
(
SELECT ChargeProcedure, MAX(EffectiveDate) AS 'EffectiveDate'
FROM dbo.ChargePrice
GROUP BY ChargeProcedure
),
CodeDate AS
(
SELECT ChargeProcedure, MAX(EffectiveDate) AS 'EffectiveDate'
FROM dbo.ChargeCode
GROUP BY ChargeProcedure
)

SELECT CD.ChargeProcedure, CP.Price, CC.Code
FROM 
    dbo.ChargeDictionary AS CD
    JOIN PriceDate AS P ON P.ChargeProcedure = CD.ChargeProcedure
    JOIN dbo.ChargePrice AS CP ON CD.ChargeProcedure = CP.ChargeProcedure 
        AND CP.EffectiveDate = P.EffectiveDate
    JOIN CodeDate AS C ON C.ChargeProcedure = CD.ChargeProcedure
    JOIN dbo.ChargeCode AS CC ON CD.ChargeProcedure = CC.ChargeProcedure 
        AND CC.EffectiveDate = C.EffectiveDate

I hope these two examples have helped you understand how to make your data easier to query.  For more reading on this topic check out the articles listed below. Let me know in the comments how you used a CTE and how it helped solve a problem.

Additional Resources
redgate
Microsoft
StackOverflow