SSRS Patient List Report
This report details patient admissions, diagnoses, and test results.
Top Hospitals and Physician By Medical Condition
This report presents a ranked view of the top 10 hospitals by total billing for each medical condition. Users can expand each hospital to view the top 10 physicians within that hospital.
I started building this report by identifying the top 10 hospitals for each medical condition, ranked by total hospital billing.
WITH TotalHospitalBilling AS (
SELECT
hd.Medical_Condition
,hd.Hospital
,SUM(hd.billing_amount) Total_billing
FROM HealthCareKaggle.dbo.healthcare_dataset hd
GROUP BY hd.Medical_Condition, hd.Hospital
),
RankedHospital AS (
SELECT
thb.medical_condition
,thb.hospital
,thb.Total_Billing
,ROW_NUMBER() OVER (PARTITION BY thb.medical_condition ORDER BY thb.total_billing DESC) AS Rank
FROM totalhospitalbilling thb
)
SELECT *
FROM RankedHospital
WHERE rank <= 10
After that, I wanted to drill down further to find the top 10 providers within each of those hospitals, based on individual doctor billing.
WITH DoctorBilling AS (
SELECT
medical_condition
,doctor
,SUM(billing_amount) AS Total_Billing
FROM HealthCareKaggle.dbo.healthcare_dataset hd
GROUP BY hd.medical_condition, hd.doctor
),
RankedDoctor AS (
SELECT
medical_condition
,doctor
,Total_Billing
,ROW_NUMBER() OVER (PARTITION BY medical_condition ORDER BY total_billing DESC) AS DoctorRank
FROM doctorbilling
)
SELECT
medical_condition
,doctor
,FORMAT(total_billing,'c') AS DoctorBilling
,doctorrank
FROM rankeddoctor
WHERE doctorrank <= 10
ORDER BY medical_condition
Initially, I used CTEs to structure the logic, but performance became an issue as execution times grew too long.
To improve efficiency, I refactored the logic using temporary tables and wrapped the entire process in a stored procedure.
CREATE PROCEDURE dbo.RankedHospitalsAndRankedDoctors
AS
BEGIN
SET NOCOUNT ON;
--Total Hospital Billing
SELECT
hd.Medical_Condition
,hd.Hospital
,SUM(hd.billing_amount) Total_billing
INTO #TotalHospitalBilling
FROM HealthCareKaggle.dbo.healthcare_dataset hd
GROUP BY hd.Medical_Condition, hd.Hospital
--Ranked Hospital
SELECT
thb.medical_condition
,thb.hospital
,thb.Total_Billing
,ROW_NUMBER() OVER (PARTITION BY thb.medical_condition ORDER BY thb.total_billing DESC) AS Rank
INTO #RankedHospital
FROM #totalhospitalbilling thb
--DoctorBilling
SELECT
hd.medical_condition
,hd.Hospital
,hd.doctor
,SUM(hd.billing_amount) AS Total_Billing
INTO #DoctorBilling
FROM HealthCareKaggle.dbo.healthcare_dataset hd
GROUP BY hd.medical_condition, hd.doctor,hd.Hospital
--Ranked Doctor
SELECT
db.medical_condition
,db.hospital
,db.doctor
,db.Total_Billing AS DoctorBilling
,ROW_NUMBER() OVER (PARTITION BY db.medical_condition, db.hospital ORDER BY db.total_billing DESC) AS DoctorRank
INTO #RankedDoctor
FROM #doctorbilling db
SELECT
rh.medical_condition
,rh.hospital AS Hospital
,FORMAT(rh.total_billing, 'c0') AS Total_Hospital_Billing
,rh.Rank AS HospitalRank
,rd.doctor AS Doctor
,FORMAT(rd.doctorbilling,'c0') AS Doctor_Billing
,rd.DoctorRank
FROM #rankedhospital rh
LEFT JOIN #RankedDoctor rd
ON rh.medical_condition = rd.medical_condition AND rh.hospital = rd.hospital
WHERE rh.rank <= 10
AND (rd.doctorrank <= 10 OR rd.doctorrank IS NULL)
ORDER BY rh.medical_condition, rh.rank, rd.doctorrank
END
EXECUTE dbo.RankedHospitalsAndRankedDoctors
This stored procedure was then easily integrated into SSRS to generate the final report.