I have a database that gets fed three tables each year from a vendor. The tables contain nation wide data just for that year so you can guage your performance against other companies in the US. Each year, I have to build numerous queries to grab the data and display different views for 5 years. The problem is, I have to prepare numerous reports and graphs so each year I have to update at least 20 queries.
Here are some of the things I have to capture and group (I didn't make these names up, I inherited this database and can't make changes):
1. HEDIS measure name rates for Commerical and Medicare.
2. I have to capture what my company scored and what the 50th, 75th, and 90th percentile is for the nation and how many points we need to reach the next percentile.
3. HEDIS measures where my company is in the top ten in the nation.
This is just a small portion of what I have to add to the queries each year.
My question is, is there someway I can build a table with the years I want to pull (say 2008, 2009, 2010, 2011, and 2012) and grab and group the data the way I need it? To display it in the various reports, I have to name each years data like: 2008 Commercial Rate, 2008 Medicare rate etc. How would I update each years field names so that it would group and sort the way the user needs it?
This is just one of the many queries that I have to build each year to capture data for the new year.
SELECT DISTINCT subtblHEDISMeasureID.[Measure ID], tblCOMPANYSpecificRates.AccreditationID, subtblHEDISMeasureID.RequiredForAccreditation, subtblEntity.EntityType, [tblHEDIS2012-Percentiles].AverageID, [tblHEDIS2012-Percentiles].AverageName, subtblHEDISMeasureID.ActiveMeasure, subtblHEDISMeasureID.[Domain Name], tblCOMPANYSpecificRates.ReportingYear, f_qryCOMPANYExecutiveSummarySelectBestInCO2012.COMPANYShortName, subtblHEDISMeasureID.ShortName, tblCOMPANYSpecificRates.LineOfBusiness, tblCOMPANYSpecificRates.ServiceArea, IIf(tblCOMPANYSpecificRates.ReportingYear=2012,tblCOMPANYSpecificRates.Rate,0) AS 2012Rate, [tblHEDIS2012-Percentiles].[10thPercentile] AS 201210th, [tblHEDIS2012-Percentiles].[25thPercentile] AS 201225th, [tblHEDIS2012-Percentiles].[50thPercentile] AS 201250th, [tblHEDIS2012-Percentiles].[75thPercentile] AS 201275th, [tblHEDIS2012-Percentiles].[90thPercentile] AS 201290th, f_qryCOMPANYExecutiveSummary2012GetInvertedRates.[2012CorrectedPercentile] AS 2012Percentile, f_qryCOMPANYExecutiveSummary2012GetInvertedRates.[2012CorrectedNextPercentile] AS 2012NextPercentileRate, [f_qryCOMPANYExecutiveSummary2011-For2012].[2011Rate], [f_qryCOMPANYExecutiveSummary2011-For2012].[2011CorrectedPercentile] AS 2011Percentile, Val([f_qryCOMPANYExecutiveSummarySelectBestInCO2012].[Rate]) AS BestInCO, f_qryCOMPANYExecutiveSummary2012SoCO.[2012SoCORate], IIf([2012Percentile]="N/A" Or [2011Percentile]="N/A","N/A",IIf([2012Percentile]="<10th" And [2011Percentile]="10th","¡è",IIf([2012Percentile]>[2011Percentile],"¡è",IIf([2012Percentile]<[2011Percentile],"¡é",IIf([2012Percentile]=[2011Percentile],"¡ê"))))) AS PercentileChange, subtblHPRRankingWeights.HPRCAHPSComm, subtblHPRRankingWeights.HPRTreatmentComm, subtblHPRRankingWeights.HPRPreventionComm, subtblFiveStars.Medicare1StarRate, subtblFiveStars.Medicare2StarRate, subtblFiveStars.Medicare3StarRate, subtblFiveStars.Medicare4StarRate, subtblFiveStars.Medicare5StarRate, IIf([HPRTreatmentComm]=True Or [HPRPreventionComm]=True Or [HPRCAHPSComm]=True,"*") AS HPRRankingEntityComm, IIf([HPRTreatmentMedicare]=True Or [HPRPreventionMedicare]=True Or [HPRCAHPSMedicare]=True,"*") AS HPRRankingEntityMed, subtblHEDISMeasureID.RequiredForFiveStar, "0" AS RequiredForAccredComm, "0" AS RequiredForAccredMedicare INTO tblCOMPANYExecutiveSummary
FROM (((subtblHEDISMeasureID RIGHT JOIN (((((tblCOMPANYSpecificRates LEFT JOIN [tblHEDIS2012-Percentiles] ON tblCOMPANYSpecificRates.MeasureID = [tblHEDIS2012-Percentiles].MeasureID) LEFT JOIN f_qryCOMPANYExecutiveSummarySelectBestInCO2012 ON [tblHEDIS2012-Percentiles].MeasureID = f_qryCOMPANYExecutiveSummarySelectBestInCO2012.MeasureID) LEFT JOIN f_qryCOMPANYExecutiveSummary2012GetInvertedRates ON tblCOMPANYSpecificRates.MeasureID = f_qryCOMPANYExecutiveSummary2012GetInvertedRates.MeasureID) LEFT JOIN [f_qryCOMPANYExecutiveSummary2011-For2012] ON tblCOMPANYSpecificRates.MeasureID = [f_qryCOMPANYExecutiveSummary2011-For2012].[Measure ID]) LEFT JOIN f_qryCOMPANYExecutiveSummary2012SoCO ON tblCOMPANYSpecificRates.MeasureID = f_qryCOMPANYExecutiveSummary2012SoCO.[Measure ID]) ON subtblHEDISMeasureID.[Measure ID] = tblCOMPANYSpecificRates.MeasureID) LEFT JOIN subtblHPRRankingWeights ON tblCOMPANYSpecificRates.AccreditationID = subtblHPRRankingWeights.AccreditationID) LEFT JOIN subtblFiveStars ON tblCOMPANYSpecificRates.AccreditationID = subtblFiveStars.AccreditationID) LEFT JOIN subtblEntity ON tblCOMPANYSpecificRates.AccreditationID = subtblEntity.AccreditationID
WHERE (((subtblEntity.EntityType)="Executive Summary") AND (([tblHEDIS2012-Percentiles].AverageID)=1) AND (([tblHEDIS2012-Percentiles].AverageName)="National - All LOBs: Average") AND ((subtblHEDISMeasureID.ActiveMeasure)=True) AND ((tblCOMPANYSpecificRates.ReportingYear)=2012) AND ((tblCOMPANYSpecificRates.LineOfBusiness)="Commercial") AND ((tblCOMPANYSpecificRates.ServiceArea)="Regional (all service areas)"))
ORDER BY subtblHEDISMeasureID.[Measure ID], subtblHEDISMeasureID.ShortName;
John F
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (1) |
Tidak ada komentar:
Posting Komentar