Jumat, 08 Juli 2011

RE: [MS_AccessPros] Getting Top 10 Rates with multiple Measure IDs

Quick question, why is this a totals query when there are no aggregates (SUM, COUNT, AVG, MIN, MAX,...)?

This should be a little more readable version of the original SQL:
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;

Duane Hookom
MS Access MVP

To: MS_Access_Professionals@yahoogroups.com
From: jfakes@rocketmail.com
Date: Fri, 8 Jul 2011 16:56:14 +0000
Subject: [MS_AccessPros] Getting Top 10 Rates with multiple Measure IDs


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;


[Non-text portions of this message have been removed]

------------------------------------

Yahoo! Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/MS_Access_Professionals/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/MS_Access_Professionals/join
(Yahoo! ID required)

<*> To change settings via email:
MS_Access_Professionals-digest@yahoogroups.com
MS_Access_Professionals-fullfeatured@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
MS_Access_Professionals-unsubscribe@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/

Tidak ada komentar:

Posting Komentar