I need to determine the top 10 rates for each measure ID. There are over 100 measure ID's and I have finally came up with the below listed SQL, however, it arbitrarily displays anywhere from two rates per measure id up to 7 rates for other measure ids. Any idea how I can fix this issue? I think it might have to do with my order by, however, I haven't been able to figure out how to get the top 10 rates for each measure id.
And no, I can't change the tables as it comes from an outside vendor and that is how I get the data.
Thanks.
SELECT subtblHEDISMeasureID.[Measure ID], [tblHEDIS2010-AllMeasures].Rate, [tblHEDIS2010-AllMeasures].Name, [tblHEDIS2010-AllMeasures].ShortName, [tblHEDIS2010-AllMeasures].MeasureNameAbbreviation, [tblHEDIS2010-AllMeasures].MeasureName
FROM subtblHEDISMeasureID LEFT JOIN [tblHEDIS2010-AllMeasures] ON subtblHEDISMeasureID.[Measure ID] = [tblHEDIS2010-AllMeasures].MeasureID
GROUP BY subtblHEDISMeasureID.ActiveMeasure, subtblHEDISMeasureID.[Measure ID], [tblHEDIS2010-AllMeasures].Rate, [tblHEDIS2010-AllMeasures].Name, [tblHEDIS2010-AllMeasures].ShortName, [tblHEDIS2010-AllMeasures].MeasureNameAbbreviation, [tblHEDIS2010-AllMeasures].MeasureName, [tblHEDIS2010-AllMeasures].ReportingProduct
HAVING (((subtblHEDISMeasureID.ActiveMeasure)=True) AND (([tblHEDIS2010-AllMeasures].Rate) In (Select Top 10 [tblHEDIS2010-AllMeasures].[Rate] from [tblHEDIS2010-AllMeasures] Where [subtblHEDISMeasureID].[Measure ID] = [tblHEDIS2010-AllMeasures].[MeasureID] and [tblHEDIS2010-AllMeasures].[Rate] Not In ("NR","NA","ND","NB","EXC") Order by [Rate] Desc)) AND (([tblHEDIS2010-AllMeasures].ReportingProduct)="HMO"))
ORDER BY subtblHEDISMeasureID.[Measure ID], [tblHEDIS2010-AllMeasures].Rate DESC;
Tidak ada komentar:
Posting Komentar