Reduce Hospital Readmissions with SQL

Introduction

30 Second Summary

When a senior is discharged from the hospital only to be readmitted within 30 days, it signals a breakdown in the care transition. For senior care programs, understanding which patients are coming back and why is the foundation of every intervention that prevents it.

In this project, you will build an end-to-end analytics deliverable using SQL Server and Power BI that identifies 30-day readmissions from CMS synthetic Medicare claims data, uncovers the clinical and demographic drivers behind them, and delivers an executive drill-through dashboard with strategic recommendations for clinical leadership.

What You'll Build

You can open your Power BI dashboard, show a clinical leader the overall 30-day readmission rate, let them click into the chronic condition driving the most readmissions, and present a data-backed recommendation for a targeted post-discharge intervention program.

By the end of this project, you'll have:

• A baseline readmission rate that immediately reveals the scope of the problem, showing exactly what percentage of seniors with multiple chronic conditions are returning to the hospital within 30 days.
• Segmented analytical views that break readmissions by chronic condition, age cohort, and cost tier, translating clinical risk into the financial language leadership uses to prioritize interventions.
• An executive drill-through dashboard where leadership sees top-level KPIs, clicks a condition in the bar chart, and instantly lands on the specific patient cohorts and cost breakdowns driving that number.
• Secret Mission: Build a SQL-based patient risk stratification score and display it as a tiered risk matrix in Power BI.

Are there any prerequisites?

This project assumes 10+ years of analytics experience, strong SQL proficiency, and familiarity with Power BI. You will need a Windows machine (or Windows VM) to run SQL Server 2022 Express and Power BI Desktop.

Before We Start

Before diving into the data, let's ground ourselves in the clinical problem this analysis solves and why senior care leadership would act on the answer.

Set Up Your SQL Environment and Load the Data

Healthcare analytics teams model claims data in relational databases because they need to join beneficiary demographics to inpatient claims for longitudinal analysis. A flat CSV file sitting on disk cannot answer questions like "which seniors with heart failure were readmitted within 30 days."

Your SQL Server instance and the CMS DE-SynPUF CSV files are ready from the previous setup. In this step, you will create a dedicated database and load the Medicare claims data into structured tables that make readmission analysis possible.

In this step, get ready to:

• Create the SeniorCareReadmissions database in SSMS.
• Import three CMS DE-SynPUF CSV files into structured tables.
• Verify the data loaded correctly with a row count query.

Create the SeniorCareReadmissions database

Before you can import any data, you need a dedicated database to hold your readmission analysis tables. This keeps your project isolated from any other databases on your local instance.

• In SSMS, open a new query window by clicking New Query in the toolbar.
• Create the database by running this SQL:

-- Create a dedicated database for the readmission analysis
CREATE DATABASE SeniorCareReadmissions;
GO

• Click Execute (or press F5) to run the statement.

What does this do?

This creates an empty database called SeniorCareReadmissions on your local SQL Server Express instance. The GO keyword signals the end of a batch so SQL Server processes the statement immediately.

• In the Object Explorer panel on the left, right-click Databases and select Refresh.

You should see SeniorCareReadmissions appear in the list of databases.

Don't see the new database?

Make sure you clicked Execute and the Messages tab at the bottom shows "Command(s) completed successfully." If you see an error about permissions, confirm you are connected with Windows Authentication (the default for Express installs).

help me troubleshoot why my CREATE DATABASE command failed in SSMS

Import the beneficiary and claims CSV files

The CMS DE-SynPUF data ships as comma-separated CSV files. SSMS has a built-in Import Flat File wizard that auto-detects column types from CSV headers, so you do not need to manually define a 32-column CREATE TABLE statement.

• In Object Explorer, expand Databases, then right-click SeniorCareReadmissions.
• Point to Tasks, then select Import Flat File.
• On the Specify Input File page, click Browse and navigate to the unzipped 2008 Beneficiary Summary CSV file.
• Set the New Table Name to Beneficiary_Summary_2008.
• Click Next through the Preview Data and Modify Columns pages. The wizard auto-detects column names and types from the CSV header row.
• On the final page, click Finish to import the data.

What is the Import Flat File wizard doing?

The wizard reads your CSV, creates a new table with columns matching the file headers, infers data types using the first rows of data, and bulk-loads all rows into the table. This is equivalent to writing a CREATE TABLE statement with 32 columns followed by a BULK INSERT, but without the manual typing.

Confirm the first import succeeded before continuing.

• Run this query in a new query window (make sure your database context is SeniorCareReadmissions):

USE SeniorCareReadmissions;
GO

SELECT COUNT(*) AS TotalRows
FROM Beneficiary_Summary_2008;

You should see approximately 116,352 rows returned. This confirms Sample 1 loaded correctly.

• Now repeat the Import Flat File wizard for the remaining two files. Right-click SeniorCareReadmissions, select Tasks then Import Flat File again.
• Import the 2009 Beneficiary Summary CSV with the table name Beneficiary_Summary_2009.
• Import the 2008-2010 Inpatient Claims CSV with the table name Inpatient_Claims.

Large file import times

The Inpatient Claims file contains roughly 27,000 rows per year (about 66,000 total across 2008-2010 for Sample 1). The import should complete within a minute on most machines. If it seems to hang, wait for the progress bar to finish before closing the wizard.

Import Flat File option missing?

If you do not see Import Flat File under Tasks, your version of SSMS may be outdated. Update to the latest SSMS from the Microsoft download center. Alternatively, you can use a BULK INSERT statement with the file path and column definitions to load each CSV manually.

help me import a CSV file into SQL Server when the Import Flat File wizard is not available

Verify all tables loaded correctly

Before you move on, confirm that all three tables exist and have data.

• Run this verification query:

USE SeniorCareReadmissions;
GO

-- Check row counts for all three tables
SELECT 'Beneficiary_Summary_2008' AS TableName,
       COUNT(*) AS RowCount
FROM Beneficiary_Summary_2008
UNION ALL
SELECT 'Beneficiary_Summary_2009',
       COUNT(*)
FROM Beneficiary_Summary_2009
UNION ALL
SELECT 'Inpatient_Claims',
       COUNT(*)
FROM Inpatient_Claims;

What should I see?

Your results should show three rows. Beneficiary_Summary_2008 should have approximately 116,352 rows. Beneficiary_Summary_2009 should have a similar count (around 114,000-116,000). Inpatient_Claims should have around 66,000 rows (spanning 2008-2010 combined).

Before you run the final check, what row count do you expect for the 2008 Beneficiary Summary table?

• Run the outline's verification query:

SELECT COUNT(*) FROM Beneficiary_Summary_2008;

You should see approximately 116,352 rows returned. This confirms your CMS Medicare claims data is ready for readmission analysis.

Seeing zero rows or an error?

If you see 0 rows, the import wizard may have created the table structure without loading data. Re-run the Import Flat File wizard and confirm you selected the correct CSV file (the 2008 Beneficiary Summary, not an empty file).

If you see an "Invalid object name" error, make sure your query window is connected to the SeniorCareReadmissions database. Check the database dropdown in the SSMS toolbar, or run USE SeniorCareReadmissions; first.

help me troubleshoot why my table has no data after importing a CSV in SSMS

Your Medicare claims data is now structured and ready for analysis. Next up, you will write the SQL logic to identify which seniors were readmitted within 30 days and calculate your baseline readmission rate.

Calculate the Baseline 30-Day Readmission Rate

Your database is loaded and ready to query. Now you need to answer the first question any clinical leader will ask: how bad is the readmission problem right now?

Before you can target interventions, you need a single headline number that frames the scope of the issue. This step produces that number by identifying which seniors are being readmitted within 30 days of discharge, then calculating the overall rate as a percentage.

In this step, get ready to:

• Define readmission logic using a SQL window function.
• Calculate the overall 30-day readmission rate as a percentage.
• Validate the results with a manual spot check of 5 beneficiaries.

Define readmission logic in SQL

A 30-day readmission occurs when a beneficiary is discharged from one inpatient stay and admitted again within 30 days. To detect this pattern, you need to compare each claim's discharge date against the next admission date for the same beneficiary.

The LEAD window function is perfect for this. It peeks at the next row's value within a partition, so you can grab the next admission date for each beneficiary without a self-join.

• In SSMS, make sure you have the SeniorCareReadmissions database selected in the database dropdown.
• Open a new query window and paste the following query to preview readmission flags:

-- Preview readmission flags using LEAD window function
WITH Claim_Sequence AS (
    SELECT
        DESYNPUF_ID,
        CLM_ADMSN_DT,
        NCH_BENE_DSCHRG_DT,
        LEAD(CLM_ADMSN_DT) OVER (
            PARTITION BY DESYNPUF_ID
            ORDER BY CLM_ADMSN_DT
        ) AS Next_Admission_Date
    FROM Inpatient_Claims
    WHERE CLM_ADMSN_DT IS NOT NULL
      AND NCH_BENE_DSCHRG_DT IS NOT NULL
)
SELECT TOP 20
    DESYNPUF_ID,
    NCH_BENE_DSCHRG_DT AS Discharge_DT,
    Next_Admission_Date AS Next_Admit_DT,
    DATEDIFF(DAY,
        CONVERT(DATE, CAST(NCH_BENE_DSCHRG_DT AS CHAR(8)), 112),
        CONVERT(DATE, CAST(Next_Admission_Date AS CHAR(8)), 112)
    ) AS Days_To_Readmit,
    CASE
        WHEN DATEDIFF(DAY,
            CONVERT(DATE, CAST(NCH_BENE_DSCHRG_DT AS CHAR(8)), 112),
            CONVERT(DATE, CAST(Next_Admission_Date AS CHAR(8)), 112)
        ) <= 30 THEN 1
        ELSE 0
    END AS Readmission_Flag
FROM Claim_Sequence
WHERE Next_Admission_Date IS NOT NULL;

What does this query do?

• The CTE (Claim_Sequence) uses LEAD(CLM_ADMSN_DT) partitioned by DESYNPUF_ID and ordered by admission date. This gives each row the next admission date for that same beneficiary.
• CONVERT(DATE, CAST(... AS CHAR(8)), 112) converts the YYYYMMDD integer dates into proper DATE types so DATEDIFF can calculate the number of days between discharge and next admission.
• The CASE expression flags a claim as a readmission (1) when the gap is 30 days or fewer.

• Click Execute (or press F5) to run the query.

You should see 20 rows in your results grid, each showing a beneficiary ID, their discharge date, the next admission date, the number of days between them, and whether that counts as a readmission (1 or 0).

Why convert dates with style 112?

The CMS DE-SynPUF stores dates as 8-digit integers in YYYYMMDD format (for example, 20080115 means January 15, 2008). SQL Server's DATEDIFF function requires proper date types, not raw integers. Style 112 tells CONVERT to interpret the string as yyyymmdd format, which works regardless of your server's language or dateformat settings.

Seeing a conversion error?

• If you see an arithmetic overflow or conversion error, some rows may have NULL or zero values in the date columns. The WHERE CLM_ADMSN_DT IS NOT NULL clause should filter these out. Check that you copied the full WHERE clause from both the CTE and the outer query.
• If the column was imported as a numeric type rather than varchar, the CAST(... AS CHAR(8)) handles it. Make sure you included that cast step.

help me troubleshoot a date conversion error in my readmission query

Calculate the overall readmission rate

Now that you can identify individual readmissions, you need the headline number: what percentage of beneficiaries with any inpatient claim had at least one 30-day readmission?

Before you run this, think about what you expect. Do you think the readmission rate will be closer to 5%, 15%, or 30%?

• Run the following query to calculate the baseline 30-day readmission rate:

-- Calculate the baseline 30-day readmission rate
WITH Claim_Sequence AS (
    SELECT
        DESYNPUF_ID,
        NCH_BENE_DSCHRG_DT,
        LEAD(CLM_ADMSN_DT) OVER (
            PARTITION BY DESYNPUF_ID
            ORDER BY CLM_ADMSN_DT
        ) AS Next_Admission_Date
    FROM Inpatient_Claims
    WHERE CLM_ADMSN_DT IS NOT NULL
      AND NCH_BENE_DSCHRG_DT IS NOT NULL
),
Readmissions AS (
    SELECT DISTINCT DESYNPUF_ID
    FROM Claim_Sequence
    WHERE DATEDIFF(DAY,
        CONVERT(DATE, CAST(NCH_BENE_DSCHRG_DT AS CHAR(8)), 112),
        CONVERT(DATE, CAST(Next_Admission_Date AS CHAR(8)), 112)
    ) BETWEEN 1 AND 30
)
SELECT
    COUNT(DISTINCT r.DESYNPUF_ID) AS Readmitted_Beneficiaries,
    (SELECT COUNT(DISTINCT DESYNPUF_ID)
     FROM Inpatient_Claims) AS Total_Beneficiaries,
    CAST(COUNT(DISTINCT r.DESYNPUF_ID) AS FLOAT) /
    (SELECT COUNT(DISTINCT DESYNPUF_ID)
     FROM Inpatient_Claims) * 100
        AS Readmission_Rate_Pct
FROM Readmissions r;

What does this query do?

• The Readmissions CTE finds distinct beneficiaries who had at least one readmission within 1 to 30 days. Using BETWEEN 1 AND 30 excludes same-day transfers (0 days) which are not true readmissions.
• The final SELECT divides readmitted beneficiaries by total beneficiaries with any inpatient claim, then multiplies by 100 to get a percentage.
• CAST(... AS FLOAT) ensures decimal division rather than integer division, which would round down to zero.

• Click Execute to run the query.

Your results pane shows three columns: the count of readmitted beneficiaries, the total count of beneficiaries with inpatient claims, and the readmission rate as a percentage. This single number is what you would present to leadership as the baseline.

Why does this number matter?

The CMS Hospital Readmissions Reduction Program uses a 30-day all-cause readmission window as its standard measure. Your baseline rate gives leadership a single headline number to compare against national benchmarks and track improvement over time.

Getting zero or NULL for the rate?

• If Readmission_Rate_Pct returns 0, you may be missing the CAST(... AS FLOAT) around the count. Without it, SQL Server performs integer division and rounds the result down to 0.
• If you see NULL, confirm that the Readmissions CTE is returning rows. Run just the CTE portion with a SELECT COUNT(*) to verify.

help me debug my readmission rate calculation

✔️ Awesome, I've got everything!

Great. Make sure you note your baseline readmission rate percentage. You will reference it in the next step when comparing rates across chronic conditions.

ⓧ I'd like to double check the full code

Scroll up and confirm you have both queries entered in SSMS:

• The readmission preview query (readmission_preview.sql) that shows 20 flagged rows with Days_To_Readmit and Readmission_Flag columns.
• The baseline rate query (baseline_rate.sql) that produces Readmitted_Beneficiaries, Total_Beneficiaries, and Readmission_Rate_Pct.

Validate with a spot check

A rate percentage is only meaningful if the underlying logic is correct. You need to manually verify that the beneficiaries flagged as readmitted actually have admission dates that fall within 30 days of a prior discharge.

• Run the following query to pull 5 specific readmitted beneficiaries with their date pairs:

-- Spot check: verify 5 readmitted beneficiaries
WITH Claim_Sequence AS (
    SELECT
        DESYNPUF_ID,
        CLM_ADMSN_DT,
        NCH_BENE_DSCHRG_DT,
        LEAD(CLM_ADMSN_DT) OVER (
            PARTITION BY DESYNPUF_ID
            ORDER BY CLM_ADMSN_DT
        ) AS Next_Admission_Date
    FROM Inpatient_Claims
    WHERE CLM_ADMSN_DT IS NOT NULL
      AND NCH_BENE_DSCHRG_DT IS NOT NULL
)
SELECT TOP 5
    DESYNPUF_ID,
    CONVERT(DATE, CAST(NCH_BENE_DSCHRG_DT AS CHAR(8)), 112)
        AS Discharge_Date,
    CONVERT(DATE, CAST(Next_Admission_Date AS CHAR(8)), 112)
        AS Next_Admit_Date,
    DATEDIFF(DAY,
        CONVERT(DATE, CAST(NCH_BENE_DSCHRG_DT AS CHAR(8)), 112),
        CONVERT(DATE, CAST(Next_Admission_Date AS CHAR(8)), 112)
    ) AS Days_Between
FROM Claim_Sequence
WHERE DATEDIFF(DAY,
    CONVERT(DATE, CAST(NCH_BENE_DSCHRG_DT AS CHAR(8)), 112),
    CONVERT(DATE, CAST(Next_Admission_Date AS CHAR(8)), 112)
) BETWEEN 1 AND 30
ORDER BY Days_Between;

What should I see?

Five rows, each showing a beneficiary ID, a discharge date, a next admission date, and the number of days between them. Every Days_Between value should be between 1 and 30. You can mentally verify: does the next admission date fall within 30 days after the discharge date? If yes, your logic is correct.

• Click Execute and confirm all 5 rows show a Days_Between value between 1 and 30.
• Pick one row and manually verify: subtract the discharge date from the next admission date. The result should match the Days_Between column.

Dates don't look right?

• If Discharge_Date appears after Next_Admit_Date, your ORDER BY inside the LEAD function may be using the wrong column. Confirm it says ORDER BY CLM_ADMSN_DT.
• If you see negative values in Days_Between, the BETWEEN 1 AND 30 filter should exclude them. Check that you included it in the outer WHERE clause.

help me debug my readmission spot check query results

You now have a validated baseline number that frames the readmission problem. Next up, you will dig into which chronic conditions and patient segments are driving that number, so leadership knows where to focus their intervention resources.

Uncover the Clinical Drivers

Your baseline readmission rate tells leadership the size of the problem. It does not tell them where to focus.

In this step, you will segment readmissions by chronic condition, comorbidity count, and cost impact. This transforms a single headline number into a map that shows which patient populations are driving the most readmissions and costing the most money.

In this step, get ready to:

• Create a reusable analytical view joining readmission flags to beneficiary demographics and chronic conditions.
• Calculate condition-specific and comorbidity-count readmission rates.
• Quantify the financial cost impact per chronic condition segment.

Join beneficiary chronic conditions to readmission flags

Your readmission query from Step 2 flags individual claims. To understand clinical drivers, you need to attach each beneficiary's chronic condition profile to those flags.

You will create a view called vw_Readmission_Analysis that joins the Inpatient_Claims readmission logic to the Beneficiary_Summary_2008 table.

• In SSMS, open a new query window against your SeniorCareReadmissions database.
• Create the analytical view by running this query:

-- View combining readmission flags with beneficiary demographics
CREATE VIEW vw_Readmission_Analysis AS
WITH Readmissions AS (
    -- Reuse LEAD logic to flag 30-day readmissions per beneficiary
    SELECT
        DESYNPUF_ID, CLM_UTLZTN_DAY_CNT, CLM_PMT_AMT,
        CASE
            WHEN DATEDIFF(DAY,
                CONVERT(DATE, CAST(NCH_BENE_DSCHRG_DT AS CHAR(8)), 112),
                LEAD(CONVERT(DATE, CAST(CLM_ADMSN_DT AS CHAR(8)), 112))
                    OVER (PARTITION BY DESYNPUF_ID ORDER BY CLM_ADMSN_DT)
            ) <= 30 THEN 1 ELSE 0
        END AS readmission_flag
    FROM Inpatient_Claims
)
-- Join to beneficiary file for chronic conditions and age
SELECT
    r.DESYNPUF_ID, r.readmission_flag,
    b.SP_CHF, b.SP_DIABETES, b.SP_COPD, b.SP_CHRNKIDN,
    b.SP_DEPRESSN, b.SP_ISCHMCHT, b.SP_ALZHDMTA,
    b.SP_CNCR, b.SP_OSTEOPRS, b.SP_RA_OA, b.SP_STRKETIA,
    CASE
        WHEN DATEDIFF(YEAR, CONVERT(DATE, CAST(b.BENE_BIRTH_DT AS CHAR(8)), 112), '2008-01-01') < 70 THEN 'Under 70'
        WHEN DATEDIFF(YEAR, CONVERT(DATE, CAST(b.BENE_BIRTH_DT AS CHAR(8)), 112), '2008-01-01') BETWEEN 70 AND 79 THEN '70-79'
        ELSE '80+'
    END AS age_cohort,
    r.CLM_UTLZTN_DAY_CNT, r.CLM_PMT_AMT
FROM Readmissions r
JOIN Beneficiary_Summary_2008 b ON r.DESYNPUF_ID = b.DESYNPUF_ID;

What does this view do?

The CTE (WITH Readmissions) reuses the same LEAD window function logic from Step 2 to flag each claim as a readmission (1) or not (0).

The outer SELECT joins each claim to the beneficiary's 11 chronic condition indicators from Beneficiary_Summary_2008. It also derives an age cohort from BENE_BIRTH_DT and carries forward the length of stay and payment amount for later cost analysis.

Why CONVERT with style 112?

The DE-SynPUF stores dates as 8-digit integers in YYYYMMDD format (e.g., 19350115). Style 112 in SQL Server converts a YYYYMMDD string directly to a DATE type.

• Verify the view was created by running:

-- Quick check: confirm the view returns data
SELECT TOP 5 * FROM vw_Readmission_Analysis;

You should see five rows with columns including DESYNPUF_ID, readmission_flag, all 11 chronic condition columns, age_cohort, CLM_UTLZTN_DAY_CNT, and CLM_PMT_AMT.

Getting a conversion error?

If you see a message about converting a character string to a date, your date columns may have been imported as a different data type. Check the column type with sp_help 'Inpatient_Claims'. If CLM_ADMSN_DT is already a date type, remove the CONVERT/CAST wrappers and use the column directly.

If the column is varchar instead of numeric, change CAST(NCH_BENE_DSCHRG_DT AS CHAR(8)) to just NCH_BENE_DSCHRG_DT since it is already a string.

help me troubleshoot a date conversion error in my vw_Readmission_Analysis view

Calculate condition-specific readmission rates

Now that your view is ready, you can query it to see which chronic conditions are associated with the highest readmission rates. The DE-SynPUF uses a value of 1 to indicate a beneficiary has a given condition.

• Calculate readmission rates per chronic condition by running this query:

-- Readmission rate for each of the 11 chronic conditions
SELECT
    condition_name,
    COUNT(*) AS total_claims,
    SUM(readmission_flag) AS readmissions,
    CAST(SUM(readmission_flag) * 100.0 / COUNT(*) AS DECIMAL(5,2))
        AS readmission_rate_pct
FROM vw_Readmission_Analysis
CROSS APPLY (VALUES
    ('Heart Failure', SP_CHF),
    ('Diabetes', SP_DIABETES),
    ('COPD', SP_COPD),
    ('Chronic Kidney', SP_CHRNKIDN),
    ('Depression', SP_DEPRESSN),
    ('Ischemic Heart', SP_ISCHMCHT),
    ('Alzheimers', SP_ALZHDMTA),
    ('Cancer', SP_CNCR),
    ('Osteoporosis', SP_OSTEOPRS),
    ('RA/Osteoarthritis', SP_RA_OA),
    ('Stroke/TIA', SP_STRKETIA)
) AS conditions(condition_name, has_condition)
WHERE has_condition = 1
GROUP BY condition_name
ORDER BY readmission_rate_pct DESC;

What does CROSS APPLY VALUES do here?

Instead of writing 11 separate queries with UNION ALL, the CROSS APPLY (VALUES ...) pattern unpivots the 11 condition columns into rows. Each row in the output represents one condition for one claim. The WHERE clause filters to only claims where the beneficiary has that condition (value = 1).

The result is one row per condition showing total claims, readmission count, and the readmission rate as a percentage, ordered highest to lowest.

You should see a results table with 11 rows. Some conditions will show noticeably higher readmission rates than others. Note which conditions appear at the top.

• Next, calculate readmission rates by comorbidity count (1, 2, 3, 4+ conditions) by running:

-- Readmission rate by number of comorbidities per beneficiary
SELECT
    CASE
        WHEN comorbidity_count = 1 THEN '1 condition'
        WHEN comorbidity_count = 2 THEN '2 conditions'
        WHEN comorbidity_count = 3 THEN '3 conditions'
        ELSE '4+ conditions'
    END AS comorbidity_bucket,
    COUNT(*) AS total_claims,
    SUM(readmission_flag) AS readmissions,
    CAST(SUM(readmission_flag) * 100.0 / COUNT(*) AS DECIMAL(5,2))
        AS readmission_rate_pct
FROM (
    -- Sum all 11 condition flags (each is 1=yes, 2=no)
    SELECT *,
        CASE WHEN SP_CHF = 1 THEN 1 ELSE 0 END +
        CASE WHEN SP_DIABETES = 1 THEN 1 ELSE 0 END +
        CASE WHEN SP_COPD = 1 THEN 1 ELSE 0 END +
        CASE WHEN SP_CHRNKIDN = 1 THEN 1 ELSE 0 END +
        CASE WHEN SP_DEPRESSN = 1 THEN 1 ELSE 0 END +
        CASE WHEN SP_ISCHMCHT = 1 THEN 1 ELSE 0 END +
        CASE WHEN SP_ALZHDMTA = 1 THEN 1 ELSE 0 END +
        CASE WHEN SP_CNCR = 1 THEN 1 ELSE 0 END +
        CASE WHEN SP_OSTEOPRS = 1 THEN 1 ELSE 0 END +
        CASE WHEN SP_RA_OA = 1 THEN 1 ELSE 0 END +
        CASE WHEN SP_STRKETIA = 1 THEN 1 ELSE 0 END
        AS comorbidity_count
    FROM vw_Readmission_Analysis
) sub
WHERE comorbidity_count >= 1
GROUP BY CASE
    WHEN comorbidity_count = 1 THEN '1 condition'
    WHEN comorbidity_count = 2 THEN '2 conditions'
    WHEN comorbidity_count = 3 THEN '3 conditions'
    ELSE '4+ conditions'
END
ORDER BY MIN(comorbidity_count);

Why use CASE WHEN instead of adding directly?

In the DE-SynPUF, chronic condition flags use 1 for "yes" and 2 for "no." Simply summing the raw columns would count "no" as 2 instead of 0. The CASE WHEN pattern converts each flag to a proper 0/1 before summing.

Your results should show four rows. You will likely see the readmission rate climb as the comorbidity count increases. Beneficiaries with 4+ conditions have the highest readmission risk.

Seeing zero rows returned?

If the comorbidity query returns no results, confirm your WHERE clause uses has_condition = 1 in the condition-specific query and comorbidity_count >= 1 in the comorbidity query. A value of 2 means the beneficiary does NOT have the condition.

help me debug why my comorbidity count query returns zero rows from vw_Readmission_Analysis

Add cost impact per condition segment

Clinical risk alone does not drive leadership decisions. You need to translate each condition's readmission problem into dollars. This gives executives the financial language they need to prioritize interventions.

• Calculate the average and total claim payment for readmission claims per condition by running:

-- Financial impact of readmissions per chronic condition
SELECT
    condition_name,
    SUM(readmission_flag) AS readmission_claims,
    CAST(AVG(CASE WHEN readmission_flag = 1
        THEN CLM_PMT_AMT END) AS DECIMAL(10,2))
        AS avg_readmission_cost,
    CAST(SUM(CASE WHEN readmission_flag = 1
        THEN CLM_PMT_AMT ELSE 0 END) AS DECIMAL(12,2))
        AS total_readmission_cost
FROM vw_Readmission_Analysis
CROSS APPLY (VALUES
    ('Heart Failure', SP_CHF),
    ('Diabetes', SP_DIABETES),
    ('COPD', SP_COPD),
    ('Chronic Kidney', SP_CHRNKIDN),
    ('Depression', SP_DEPRESSN),
    ('Ischemic Heart', SP_ISCHMCHT),
    ('Alzheimers', SP_ALZHDMTA),
    ('Cancer', SP_CNCR),
    ('Osteoporosis', SP_OSTEOPRS),
    ('RA/Osteoarthritis', SP_RA_OA),
    ('Stroke/TIA', SP_STRKETIA)
) AS conditions(condition_name, has_condition)
WHERE has_condition = 1
GROUP BY condition_name
ORDER BY total_readmission_cost DESC;

What does this query reveal?

The AVG with a CASE filter gives you the average cost per readmission claim for each condition. The SUM gives the total financial burden. Ordering by total_readmission_cost DESC puts the most expensive conditions at the top.

A condition might have a lower readmission rate but a higher per-claim cost, making it just as important to address. This is the insight that distinguishes a Director-level analysis from a basic report.

Your results should show 11 rows ordered by total cost. Compare this ordering to the rate-based ordering from your previous query. The conditions that rank high on both lists are your highest-priority intervention targets.

Seeing NULL for avg_readmission_cost?

A NULL average means no readmission claims exist for that condition in your data. This can happen if the condition has very few inpatient claims overall. Confirm that readmission_claims is greater than 0 for that row. If it is 0, the condition has no readmitted beneficiaries in Sample 1.

help me understand why my cost impact query shows NULL values for some conditions

Before you move on, take a moment to consider: which conditions appear in both the top 3 by rate AND the top 3 by cost? Those are the cohorts you will recommend interventions for in Step 5.

✔️ Awesome, I've got everything!

Great. Make sure you have saved your query files for reference when you build the Power BI dashboard in the next step.

ⓧ I'd like to double check the full code

Here is the view creation query you should have executed. Confirm it matches what you ran:

CREATE VIEW vw_Readmission_Analysis AS
WITH Readmissions AS (
    SELECT
        DESYNPUF_ID, CLM_UTLZTN_DAY_CNT, CLM_PMT_AMT,
        CASE
            WHEN DATEDIFF(DAY,
                CONVERT(DATE, CAST(NCH_BENE_DSCHRG_DT AS CHAR(8)), 112),
                LEAD(CONVERT(DATE, CAST(CLM_ADMSN_DT AS CHAR(8)), 112))
                    OVER (PARTITION BY DESYNPUF_ID ORDER BY CLM_ADMSN_DT)
            ) <= 30 THEN 1 ELSE 0
        END AS readmission_flag
    FROM Inpatient_Claims
)
SELECT
    r.DESYNPUF_ID, r.readmission_flag,
    b.SP_CHF, b.SP_DIABETES, b.SP_COPD, b.SP_CHRNKIDN,
    b.SP_DEPRESSN, b.SP_ISCHMCHT, b.SP_ALZHDMTA,
    b.SP_CNCR, b.SP_OSTEOPRS, b.SP_RA_OA, b.SP_STRKETIA,
    CASE
        WHEN DATEDIFF(YEAR, CONVERT(DATE, CAST(b.BENE_BIRTH_DT AS CHAR(8)), 112), '2008-01-01') < 70 THEN 'Under 70'
        WHEN DATEDIFF(YEAR, CONVERT(DATE, CAST(b.BENE_BIRTH_DT AS CHAR(8)), 112), '2008-01-01') BETWEEN 70 AND 79 THEN '70-79'
        ELSE '80+'
    END AS age_cohort,
    r.CLM_UTLZTN_DAY_CNT, r.CLM_PMT_AMT
FROM Readmissions r
JOIN Beneficiary_Summary_2008 b ON r.DESYNPUF_ID = b.DESYNPUF_ID;

Scroll up and confirm that you also ran the condition-specific rates query, the comorbidity bucket query, and the cost impact query. Each should have returned results in SSMS.

Before you run the final verification, predict: do you expect conditions with high readmission rates to also have the highest total cost, or could a lower-rate condition outrank them on cost?

• Run the condition-specific rates query one more time and confirm you see clear variation across conditions:

-- Final verification: confirm segmented rates show variation
SELECT condition_name, readmission_rate_pct
FROM (
    SELECT
        condition_name,
        CAST(SUM(readmission_flag) * 100.0 / COUNT(*) AS DECIMAL(5,2))
            AS readmission_rate_pct
    FROM vw_Readmission_Analysis
    CROSS APPLY (VALUES
        ('Heart Failure', SP_CHF),
        ('Diabetes', SP_DIABETES),
        ('COPD', SP_COPD),
        ('Chronic Kidney', SP_CHRNKIDN),
        ('Depression', SP_DEPRESSN),
        ('Ischemic Heart', SP_ISCHMCHT),
        ('Alzheimers', SP_ALZHDMTA),
        ('Cancer', SP_CNCR),
        ('Osteoporosis', SP_OSTEOPRS),
        ('RA/Osteoarthritis', SP_RA_OA),
        ('Stroke/TIA', SP_STRKETIA)
    ) AS conditions(condition_name, has_condition)
    WHERE has_condition = 1
    GROUP BY condition_name
) rates
ORDER BY readmission_rate_pct DESC;

You should see a results table with 11 rows showing readmission rates broken out by chronic condition. The rates will vary, with some conditions showing significantly higher percentages than others. This variation is exactly what leadership needs to prioritize interventions.

You now have the analytical foundation. Your vw_Readmission_Analysis view, condition-level rates, comorbidity segmentation, and cost impact queries give you everything needed to build a compelling visual story. Next up, you will bring these numbers to life in a Power BI drill-through dashboard that lets executives explore from headline KPI down to the cohorts driving it.

Build the Executive Drill-Through Dashboard in Power BI

In the previous step, you uncovered which chronic conditions drive the highest readmission rates and quantified their cost impact. Now you have the numbers. But a table of SQL results won't drive a leadership decision.

Clinical leaders need a visual story that starts at a single headline KPI and lets them drill into the root cause on demand. In this step, you will connect Power BI to your SQL Server database and build a two-page executive dashboard with drill-through navigation.

In this step, get ready to:

• Connect Power BI Desktop to your local SQL Server and import the readmission analysis view.
• Build an executive summary page with KPI card, bar chart, scatter plot, and age cohort slicer.
• Build a drill-through detail page that filters to a specific chronic condition on right-click.

Create a condition-level summary view in SQL

Power BI works best when it can pull pre-aggregated data for bar charts and scatter plots. Before you leave SSMS, you need a summary view that aggregates readmission rates and costs per chronic condition.

• Switch back to SSMS and open a new query window against SeniorCareReadmissions.
• Create the condition-level summary view by running this query:

CREATE VIEW vw_Condition_Summary AS
SELECT
    c.condition_name,
    COUNT(*) AS total_claims,
    SUM(CAST(readmission_flag AS INT)) AS readmissions,
    CAST(SUM(CAST(readmission_flag AS INT)) * 100.0
         / COUNT(*) AS DECIMAL(5,2)) AS readmission_rate,
    AVG(CLM_PMT_AMT) AS avg_claim_cost,
    SUM(CASE WHEN readmission_flag = 1
             THEN CLM_PMT_AMT ELSE 0 END) AS total_readmission_cost
FROM vw_Readmission_Analysis
CROSS APPLY (VALUES
    ('Heart Failure', SP_CHF),
    ('Diabetes', SP_DIABETES),
    ('COPD', SP_COPD),
    ('Chronic Kidney Disease', SP_CHRNKIDN),
    ('Depression', SP_DEPRESSN),
    ('Ischemic Heart Disease', SP_ISCHMCHT),
    ('Alzheimers/Dementia', SP_ALZHDMTA),
    ('Cancer', SP_CNCR),
    ('Osteoporosis', SP_OSTEOPRS),
    ('Rheumatoid Arthritis', SP_RA_OA),
    ('Stroke/TIA', SP_STRKETIA)
) AS c(condition_name, has_condition)
WHERE c.has_condition = 1
GROUP BY c.condition_name;

What does this query do?

The CROSS APPLY (VALUES ...) pattern unpivots the 11 chronic condition columns into rows. Each beneficiary claim appears once for every condition they have.

The GROUP BY then calculates the readmission rate and cost totals for each condition independently. This gives Power BI a clean table to plot directly.

• Verify the view works by running:

SELECT * FROM vw_Condition_Summary
ORDER BY readmission_rate DESC;

You should see 11 rows, one per chronic condition, ordered from highest to lowest readmission rate.

Seeing an error about the view already existing?

If you experimented with a similar view earlier, drop it first with DROP VIEW vw_Condition_Summary and re-run the CREATE statement.

help me fix my vw_Condition_Summary view creation error

Connect Power BI Desktop to your SQL Server

With both views ready in SQL Server, you can now bring the data into Power BI for visualization.

• Open Power BI Desktop.
• On the Home tab in the ribbon, click Get data.
• Select SQL Server database from the list, then click Connect.
• In the Server field, enter your local server name (typically localhost or localhost\SQLEXPRESS).
• In the Database field, enter SeniorCareReadmissions.
• Under Data Connectivity mode, select Import.
• Click OK.

Why Import mode instead of DirectQuery?

Import mode loads the data into Power BI's in-memory engine. For a local Express instance with a fixed dataset like DE-SynPUF, Import gives you faster visual rendering and full DAX support without query latency.

• If prompted about encryption, click OK to connect with an unencrypted connection (this is a local instance).
• If prompted for credentials, select Windows authentication and click Connect.
• In the Navigator window, expand the SeniorCareReadmissions database.
• Check the boxes next to vw_Readmission_Analysis and vw_Condition_Summary.
• Click Load.

Wait for Power BI to finish loading. You should see both tables listed in the Data pane on the right side.

• Save your report by pressing Ctrl+S and naming it SeniorCareReadmissions.pbix.

Power BI can't connect to SQL Server?

Make sure the SQL Server service is running. Open SSMS and confirm you can query the database there first.

If you installed a named instance, use localhost\SQLEXPRESS as the server name. If using a default instance, just localhost works.

help me connect Power BI Desktop to my local SQL Server Express

Build the KPI overview page

This first dashboard page gives leadership a single-screen view of the readmission problem: how bad it is overall, which conditions drive it, and where cost concentrates.

• Double-click the page tab at the bottom of the canvas and rename it to Executive Summary: 30-Day Readmissions.
• In the Visualizations pane, select the Card visual.
• From the Data pane, drag readmission_flag from vw_Readmission_Analysis into the card's Fields well.
• Click the dropdown arrow on the field and change the aggregation to Average.
• In the Format visual pane, set the display units to None and format as percentage with 1 decimal place.

Why average of readmission_flag works as a rate

Because readmission_flag is 1 for readmissions and 0 otherwise, the average of this column equals the proportion of claims that are readmissions. Multiplying by 100 gives you the percentage rate.

You should now see a card displaying the overall readmission rate as a percentage.

• Click an empty area of the canvas to deselect the card.
• In the Visualizations pane, select the Clustered bar chart visual.
• From vw_Condition_Summary, drag condition_name to the Y-axis well.
• Drag readmission_rate to the X-axis well.
• Click the dropdown on readmission_rate in the X-axis well and ensure it is set to Don't summarize (since the view already calculated it).

You should see a horizontal bar chart with conditions ranked by readmission rate.

• Click an empty area of the canvas to deselect.
• Select the Scatter chart visual from the Visualizations pane.
• Drag avg_claim_cost to the X-axis well.
• Drag readmission_rate to the Y-axis well.
• Drag condition_name to the Details well (this creates one dot per condition).
• Set both measures to Don't summarize.

The scatter plot now shows which conditions are both expensive and high-readmission. The upper-right quadrant represents the highest-priority intervention targets.

• Click an empty area of the canvas.
• Select the Slicer visual from the Visualizations pane.
• From vw_Readmission_Analysis, drag age_cohort into the slicer's Field well.
• Save the file with Ctrl+S.

Your executive summary page now has four visuals: a KPI card, a condition bar chart, a cost-vs-rate scatter plot, and an age cohort slicer. Try clicking different age cohorts in the slicer to confirm the other visuals filter dynamically.

Build the drill-through detail page

The executive summary shows the headline story. Now you need a detail page that answers the follow-up question: who are the specific patients behind a given condition's readmission rate?

• At the bottom of the report canvas, click the + icon to create a new page.
• Right-click the new page tab and select Rename Page.
• Name it Condition Detail.
• In the Visualizations pane on the right, scroll down to the Drillthrough section and expand it.
• From the Data pane, drag condition_name from vw_Condition_Summary into the Drillthrough filters well.

What does the drillthrough filter do?

When you add a field to the Drillthrough filters well, Power BI designates this page as a drill-through destination. From the summary page, a user can right-click a data point that contains that same field and navigate directly here, filtered to that specific value.

Power BI also automatically adds a back button so the user can return to the summary page.

• Now add visuals to this page. Select the Table visual from the Visualizations pane.
• From vw_Readmission_Analysis, drag these fields into the table's Columns well: DESYNPUF_ID, readmission_flag, CLM_UTLZTN_DAY_CNT, CLM_PMT_AMT.
• Click an empty area, then select the Clustered column chart visual (this will serve as the length-of-stay histogram).
• Drag CLM_UTLZTN_DAY_CNT to the X-axis and DESYNPUF_ID (set to Count aggregation) to the Y-axis.
• Click an empty area, then select another Card visual.
• Drag CLM_PMT_AMT into it and set the aggregation to Sum for total cost breakdown of readmissions in this cohort.

Finally, add the strategic recommendation as a text box.

• On the Insert tab in the ribbon, click Text box.
• Type a placeholder recommendation: Heart Failure cohort: 48-hour post-discharge nurse visit program could reduce readmissions by targeting medication reconciliation.
• Position the text box below or beside the visuals.
• Save the file with Ctrl+S.

Update the text box per condition

In a production dashboard, you would use conditional logic or a lookup table to display different recommendations for each condition. For now, the text box shows a static recommendation that you can manually update as you drill through different conditions.

Test the drill-through navigation

The moment of truth. You will now verify that the drill-through connection works end to end.

• Click the Executive Summary: 30-Day Readmissions page tab at the bottom to navigate back to page 1.

Before you test, predict: when you right-click the Heart Failure bar in your condition chart, what do you expect to see on the detail page?

• In the bar chart, right-click the Heart Failure bar.
• In the context menu, hover over Drillthrough and select Condition Detail.

The detail page opens, filtered to show only beneficiaries and claims associated with Heart Failure. Your table visual should display only patients with SP_CHF = 1, and the length-of-stay chart reflects only that cohort.

Drillthrough option not appearing in the context menu?

The field you right-click in the source visual must match the field in the Drillthrough filters well on the target page. Verify that condition_name from vw_Condition_Summary is in both the bar chart's Y-axis and the Condition Detail page's Drillthrough well.

Also confirm that the field is not aggregated (summed or counted) in the bar chart. It should appear as a categorical axis, not a measure.

help me fix drill-through not appearing in Power BI

• Click the Back button on the detail page to return to the executive summary.
• Save the file with Ctrl+S.

Your two-page executive dashboard is complete. Leadership can see the headline readmission rate, identify the highest-risk conditions at a glance, and drill into patient-level detail on demand. Next up, you will add the strategic recommendations layer that transforms this descriptive dashboard into a prescriptive decision tool.

Add the Strategic Recommendations Layer

Your dashboard now shows leadership how bad the readmission problem is and which conditions are driving it. But a dashboard that diagnoses the problem without recommending solutions is only half the deliverable.

In this step, you will build the prescriptive layer that transforms your analysis from descriptive to actionable. You will model the financial impact of targeted interventions in SQL, then surface those recommendations in a dedicated Power BI page that tells leadership exactly where to invest and how much they stand to save.

In this step, get ready to:

• Create a SQL view that models intervention savings per chronic condition cohort.
• Build a Strategic Recommendations page in Power BI with a matrix visual and conditional formatting.
• Add an executive narrative text box summarizing the highest-impact opportunities.

Create the intervention impact model in SQL

Leadership prioritizes by dollars saved, not by percentages. You need to translate your clinical readmission rates into estimated cost savings that justify a targeted post-discharge program.

• Switch back to SSMS and make sure you are connected to the SeniorCareReadmissions database.
• Create the intervention impact view by running this query:

CREATE VIEW vw_Intervention_Impact AS
WITH condition_readmissions AS (
    -- Count readmissions and average cost per chronic condition
    SELECT condition_name, COUNT(*) AS readmission_count,
           AVG(CLM_PMT_AMT) AS avg_claim_payment
    FROM vw_Readmission_Analysis
    CROSS APPLY (VALUES
        ('Heart Failure', SP_CHF), ('Diabetes', SP_DIABETES),
        ('COPD', SP_COPD), ('Chronic Kidney Disease', SP_CHRNKIDN),
        ('Depression', SP_DEPRESSN), ('Ischemic Heart Disease', SP_ISCHMCHT),
        ('Alzheimers/Dementia', SP_ALZHDMTA), ('Cancer', SP_CNCR),
        ('Osteoporosis', SP_OSTEOPRS), ('Rheumatoid Arthritis', SP_RA_OA),
        ('Stroke/TIA', SP_STRKETIA)
    ) AS conditions(condition_name, condition_flag)
    WHERE readmission_flag = 1 AND condition_flag = 1
    GROUP BY condition_name
)
-- Estimate 20% reduction and assign recommended intervention
SELECT condition_name, readmission_count,
    CAST(readmission_count * 0.20 AS INT) AS reducible_readmissions,
    avg_claim_payment,
    readmission_count * 0.20 * avg_claim_payment AS estimated_annual_savings,
    CASE
        WHEN condition_name IN ('Heart Failure', 'Chronic Kidney Disease') THEN '48-hr post-discharge nurse visit'
        WHEN condition_name IN ('COPD', 'Diabetes') THEN 'Telehealth monitoring program'
        WHEN condition_name IN ('Depression', 'Alzheimers/Dementia') THEN 'Care coordinator follow-up'
        ELSE 'Medication reconciliation program'
    END AS recommended_intervention
FROM condition_readmissions;

What does this query do?

The CTE unpivots the 11 chronic condition columns into rows using CROSS APPLY (VALUES ...). This lets you calculate per-condition statistics without writing 11 separate queries.

The outer SELECT applies a conservative 20% reduction assumption to each cohort's readmission count, then multiplies by the average claim payment to estimate annual savings. The CASE statement assigns a clinically appropriate intervention type to each condition group.

• Verify the view was created successfully by running this query:

SELECT * FROM vw_Intervention_Impact
ORDER BY estimated_annual_savings DESC;

You should see a results table with one row per chronic condition, showing the readmission count, reducible readmissions, average claim payment, estimated annual savings, and recommended intervention type. The conditions with the highest savings appear at the top.

Seeing an error about the view?

If you get an "Invalid column name" error, confirm that your vw_Readmission_Analysis view contains columns named exactly SP_CHF, SP_DIABETES, readmission_flag, and CLM_PMT_AMT. Run SELECT TOP 5 * FROM vw_Readmission_Analysis to check the column names.

If you get "There is already an object named 'vw_Intervention_Impact'", run DROP VIEW vw_Intervention_Impact first and then re-run the CREATE statement.

Help me troubleshoot this SQL error.

Build the recommendations page in Power BI

Now you need to bring this intervention data into Power BI and present it as a matrix that leadership can scan in seconds.

• Switch back to Power BI Desktop with your SeniorCareReadmissions.pbix file.
• Click **Home** in the ribbon, then click **Refresh** to pull in the new view from SQL Server.
• In the **Data** pane on the right, confirm you can see vw_Intervention_Impact listed as a table.

Don't see the new view?

If vw_Intervention_Impact does not appear after refreshing, you may need to add it manually. Click **Home**, then **Get Data**, select **SQL Server**, enter your local server name and SeniorCareReadmissions database, and select the vw_Intervention_Impact view to import it.

Help me add the new view to my model.

• Create a new page by clicking the **+** icon at the bottom of the report canvas.
• Right-click the new page tab and select **Rename Page**.
• Type Strategic Recommendations and press **Enter**.
• In the **Visualizations** pane, click the **Matrix** visual icon to add a matrix to the canvas.
• From the **Data** pane, drag condition_name to the **Rows** well.
• Drag readmission_count to the **Values** well.
• Drag reducible_readmissions to the **Values** well.
• Drag estimated_annual_savings to the **Values** well.
• Drag recommended_intervention to the **Values** well.

You should now see a matrix with each chronic condition as a row and the intervention metrics as columns.

• Apply conditional formatting to the savings column. In the **Visualizations** pane, click the down-arrow next to estimated_annual_savings in the **Values** well.
• Select **Conditional formatting**, then select **Background color**.
• In the dialog, set **Format style** to **Gradient**.
• Set the **Minimum** color to green (low savings, lower priority).
• Set the **Maximum** color to red (high savings, highest priority for intervention).
• Click **OK** to apply.

The savings column now displays a gradient from green to red, drawing the eye immediately to the highest-impact cohorts.

Why red for highest savings?

In healthcare executive reporting, red signals urgency and the need for action. The highest-savings cohorts represent the biggest unaddressed problems. This visual encoding matches how clinical leadership already thinks: red means "act now."

Conditional formatting option not appearing?

Make sure you are clicking the down-arrow on the field in the **Values** well of the Visualizations pane, not in the Data pane. The conditional formatting option only appears in the context menu of fields placed inside a visual's field wells.

Help me find the conditional formatting option.

Add the executive narrative

The matrix gives leadership the data. The narrative tells them what to do with it. This is the text a Director of Analytics would read aloud in a leadership meeting.

• Click on an empty area of the Strategic Recommendations page canvas to deselect the matrix.
• In the ribbon, click **Insert**, then click **Text box**.
• Position the text box above the matrix visual.
• Type a 2-3 sentence executive summary. For example: "Heart Failure and Chronic Kidney Disease represent the highest-cost readmission cohorts. A targeted 48-hour post-discharge nurse visit program for these cohorts could avoid the largest share of estimated reducible readmission costs. Recommend piloting with the Heart Failure cohort first based on volume and savings potential."
• Bold the condition names in the text box for emphasis.
• Save your report by pressing **Ctrl+S**.

Why include a text box instead of just the visual?

Executive dashboards that only show charts leave interpretation to the viewer. A pre-written narrative ensures every person in the room draws the same conclusion and walks away with the same action item. This is standard practice in healthcare analytics leadership deliverables.

Before you check the final result, what do you expect to see when you look at this page as a whole? Think about what story it tells from top to bottom.

• Navigate to your Strategic Recommendations page in Power BI.

You should see a formatted matrix showing each high-risk cohort with its readmission count, estimated reducible readmissions, estimated cost savings (color-coded green to red), and recommended intervention program. Above the matrix, your executive narrative summarizes the top opportunities.

Matrix looks empty or shows zeros?

Confirm that the data refresh pulled in vw_Intervention_Impact with actual data. Switch back to SSMS and run SELECT COUNT(*) FROM vw_Intervention_Impact to verify rows exist. If the count is 0, your vw_Readmission_Analysis view may not have any rows where readmission_flag = 1. Re-check your readmission identification logic from Step 2.

Help me trace the data issue.

Your analysis is now complete. You have gone from raw CMS claims data all the way to a prescriptive recommendation backed by financial estimates. This is the kind of deliverable that drives real operational decisions in senior care programs.

Secret mission

Build a Patient Risk Stratification Score

Your dashboard shows cohort-level readmission drivers. But clinical teams need to know which individual patients are most at risk right now. Can you build a scoring algorithm that assigns every beneficiary a composite risk score and visualizes it as a tiered risk matrix in Power BI?

Clean Up Your Resources

Clean Up Your Resources

This project runs entirely locally on your machine. There are no cloud resources or ongoing costs to worry about.

Decide whether to keep your resources for portfolio use, pause them to free up memory, or delete them entirely.

Resources you used:

• SQL Server SeniorCareReadmissions database (local, no cost).
• Power BI SeniorCareReadmissions.pbix file (local, no cost).
• CMS DE-SynPUF CSV files on disk (free public-use synthetic data).

Keep everything running

No action needed. Choose this if you plan to extend this analysis, refine your dashboard, or use it as a portfolio artifact for interviews.

• Your SQL Server Express instance continues running as a Windows service in the background.
• The database and Power BI file remain on disk, ready to open anytime.
• The CMS CSV files stay in their download location for reference.

Pause - I'll come back to this later

Stop the SQL Server service to free up memory while keeping all your files intact. You can restart it whenever you want to resume.

• Open a Command Prompt as Administrator.
• Stop the SQL Server Express service by running:

net stop MSSQL$SQLEXPRESS

• When you want to resume, open an Administrator Command Prompt and run:

net start MSSQL$SQLEXPRESS

• Your database, Power BI file, and CSV files all remain on disk untouched.

Delete - I don't want to use this again

Remove all project resources and start fresh. This frees disk space but cannot be undone.

• Open SSMS and connect to your local SQL Server Express instance.
• Open a new query window and run the following command to drop the database:

DROP DATABASE SeniorCareReadmissions;

• Delete the CMS DE-SynPUF CSV files from your download folder. Open File Explorer, navigate to where you saved the ZIP files and extracted CSVs, and delete them.
• Delete the SeniorCareReadmissions.pbix file from wherever you saved it.

What about SQL Server Express and SSMS?

SQL Server Express and SSMS are general-purpose tools you may use for future projects. If you want to uninstall them, use Windows Settings. Open Apps & features, search for "SQL Server" and "SQL Server Management Studio", then click Uninstall on each.

Power BI Desktop can be uninstalled the same way if you no longer need it.

Nice Work!

Nice Work!

You just built the kind of end-to-end analytics deliverable that a Director of Analytics presents to clinical leadership. Starting from raw CMS synthetic Medicare claims data, you uncovered who is being readmitted, why, and what to do about it.

You've learned how to:

• Built a 30-day readmission identification model in SQL using ~116K beneficiaries, calculated a baseline rate, and segmented it by 11 chronic conditions, age cohorts, and comorbidity counts to pinpoint the highest-risk populations.
• Delivered an executive drill-through Power BI dashboard where leadership sees top-level KPIs, clicks into a chronic condition cohort, and lands on a detail page with patient-level data and cost breakdowns.
• Modeled intervention impact to translate clinical risk into estimated cost savings, giving leadership a data-backed case for targeted post-discharge programs.
• Secret Mission: Designed a weighted patient risk stratification score in SQL and visualized it as a tiered risk matrix in Power BI, moving the analysis from cohort-level insight to individual patient-level prediction.

Ready to quiz yourself?