March 18, 2011 at 11:27 am
I need to group by ae.DM_AuditNurseID and GMPI but I need only the first 25 gmpi's .
Maybe I need to put in a HAVING ae.gmpi = (select top 25 ae.gmpi from ae)???
SELECT ae.DM_AuditNurseID as 'Nurse_Code', an.NL_NurseFirst 'Nurse_First_Name',an.NL_NurseLast as 'Nurse_Last_Name', GMPI = (select top 25 GMPI from tblDM_AuditEncounter)
,ap.PL_ProvFirst 'Prov_First_Name', ap.PL_ProvLast 'Prov_Last_Name', ae.DM_ProviderID
,gpm.FirstName 'Memb_First_Name', gpm.LastName 'Memb_Last_Name',ae.GMPI 'MemberID', convert(varchar(12),gpm.Birthdate) 'Memb_DOB'
,ad.AD_ICD9, hcc.HCC 'HHC_Code'--, Validation(boolean),Submitted_2010(boolean)
FROM dbo.tblDM_AuditEncounter ae JOIN dbo.tblDM_NurseLookup an
ON ae.DM_AuditNurseID = an.DM_NurseID
JOIN dbo.tblDM_ProvidersLookup ap
ON ap.DM_ProviderID = ae.DM_ProviderID
JOIN MasterPatientIndex..tblGlobalPatientMaster gpm
ON ae.GMPI = gpm.GMPI
JOIN tblDM_AuditDiag ad
ON ad.DM_AuditEncounterID = ae.DM_AuditEncounterID
JOIN dbo.tblDM_ICDtoHCC hcc
on hcc.ICD9 = ad.AD_ICD9
group by ae.DM_AuditNurseID, an.NL_NurseFirst,an.NL_NurseLast, ae.GMPI
,ap.PL_ProvFirst, ap.PL_ProvLast, ae.DM_ProviderID
,gpm.FirstName, gpm.LastName,ae.GMPI, convert(varchar(12),gpm.Birthdate)
,ad.AD_ICD9, hcc.HCC
Thanks in advance,
Adam
March 18, 2011 at 11:47 am
Why are you using Group By in this? I don't see any aggregate functions (Min, Max, Avg, etc.) in the Select clause.
Move the Top 25 part to the Select, instead of in the inline sub-query, and that should work, but I'm not sure if it'll get you what you need since I'm not sure what end result you want from the query.
It might look something like this:
SELECT TOP 25
ae.DM_AuditNurseID AS 'Nurse_Code',
an.NL_NurseFirst 'Nurse_First_Name',
an.NL_NurseLast AS 'Nurse_Last_Name',
GMPI,
ap.PL_ProvFirst 'Prov_First_Name',
ap.PL_ProvLast 'Prov_Last_Name',
ae.DM_ProviderID,
gpm.FirstName 'Memb_First_Name',
gpm.LastName 'Memb_Last_Name',
ae.GMPI 'MemberID',
CONVERT(VARCHAR(12), gpm.Birthdate) 'Memb_DOB',
ad.AD_ICD9,
hcc.HCC 'HHC_Code'--, Validation(boolean),Submitted_2010(boolean)
FROM dbo.tblDM_AuditEncounter ae
JOIN dbo.tblDM_NurseLookup an
ON ae.DM_AuditNurseID = an.DM_NurseID
JOIN dbo.tblDM_ProvidersLookup ap
ON ap.DM_ProviderID = ae.DM_ProviderID
JOIN MasterPatientIndex..tblGlobalPatientMaster gpm
ON ae.GMPI = gpm.GMPI
JOIN tblDM_AuditDiag ad
ON ad.DM_AuditEncounterID = ae.DM_AuditEncounterID
JOIN dbo.tblDM_ICDtoHCC hcc
ON hcc.ICD9 = ad.AD_ICD9
GROUP BY ae.DM_AuditNurseID,
an.NL_NurseFirst,
an.NL_NurseLast,
ae.GMPI,
ap.PL_ProvFirst,
ap.PL_ProvLast,
ae.DM_ProviderID,
gpm.FirstName,
gpm.LastName,
ae.GMPI,
CONVERT(VARCHAR(12), gpm.Birthdate),
ad.AD_ICD9,
hcc.HCC ;
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 18, 2011 at 12:15 pm
The reason for the group by is to get a 25 patient for nurse sampling for 2011. I have not filtered for 2011.
To be honest I know I am overseeing the obvious. Maybe I need any aggreagate function on gmpi (gmpi is our patient number) in the select .
Maybe if I ask in much more simpler terms. What aggreagte function will give me 25 distinct gmpi. or is that not the way?
Confused. Scratching head. I know I have done this before
select Nurse, top 25(gpmi) from Nurse_Audit
group by Nurse
March 18, 2011 at 12:22 pm
Without having sample data, this is a "best guess"
SELECT
*
FROM
(SELECT
ROW_NUMBER() OVER (PARTITION BY ae.DM_AuditNurseID ORDER BY ae.GMPI) AS rn
,ae.DM_AuditNurseID AS 'Nurse_Code'
,an.NL_NurseFirst 'Nurse_First_Name'
,an.NL_NurseLast AS 'Nurse_Last_Name'
,ap.PL_ProvFirst 'Prov_First_Name'
,ap.PL_ProvLast 'Prov_Last_Name'
,ae.DM_ProviderID
,gpm.FirstName 'Memb_First_Name'
,gpm.LastName 'Memb_Last_Name'
,ae.GMPI 'MemberID'
,CONVERT(VARCHAR(12), gpm.Birthdate) 'Memb_DOB'
,ad.AD_ICD9
,hcc.HCC 'HHC_Code'--, Validation(boolean),Submitted_2010(boolean)
FROM
dbo.tblDM_AuditEncounter ae
INNER JOIN dbo.tblDM_NurseLookup an
ON ae.DM_AuditNurseID = an.DM_NurseID
INNER JOIN dbo.tblDM_ProvidersLookup ap
ON ap.DM_ProviderID = ae.DM_ProviderID
INNER JOIN MasterPatientIndex..tblGlobalPatientMaster gpm
ON ae.GMPI = gpm.GMPI
INNER JOIN tblDM_AuditDiag ad
ON ad.DM_AuditEncounterID = ae.DM_AuditEncounterID
INNER JOIN dbo.tblDM_ICDtoHCC hcc
ON hcc.ICD9 = ad.AD_ICD9) AS iq
WHERE
rn < 26
ORDER BY
Nurse_Code
,rn
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgMarch 18, 2011 at 12:36 pm
Jason,
I want to shoot myself, before I started this I new I would may need
ROW_NUMBER() OVER (PARTITION BY ae.DM_AuditNurseID ORDER BY ae.GMPI) AS Row_Counter .
But when I started getting stuck, I forgot that I knew before I started Ithat may be how I need to slice this cake this way.
Thanks so much. I copied your code out and pasted it and it ran perfectly. I just wish I would have remembered so then I would just have needed to look at the syntax for row_number() etc... Anyway
super thanks
Adam
March 18, 2011 at 12:51 pm
I still need to narrow it so it gives me 25 distinct gmpis (i have dups). I got excited a little bit too soon, I tried distinct after both selects and no dice.
Any Ideas
March 18, 2011 at 12:57 pm
What makes them unique?
Can you post sample data/DDL scripts?
It's much easier to assist if I have sample data.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgMarch 18, 2011 at 12:58 pm
change it to ..... ORDER BY gpm.GMPI)
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgMarch 18, 2011 at 1:04 pm
I suspect this will get you closer to what you need:
SELECT ae.DM_AuditNurseID AS 'Nurse_Code',
an.NL_NurseFirst 'Nurse_First_Name',
an.NL_NurseLast AS 'Nurse_Last_Name',
GMPI,
ap.PL_ProvFirst 'Prov_First_Name',
ap.PL_ProvLast 'Prov_Last_Name',
ae.DM_ProviderID,
gpm.FirstName 'Memb_First_Name',
gpm.LastName 'Memb_Last_Name',
ae.GMPI 'MemberID',
CONVERT(VARCHAR(12), gpm.Birthdate) 'Memb_DOB',
ad.AD_ICD9,
hcc.HCC 'HHC_Code'--, Validation(boolean),Submitted_2010(boolean)
FROM dbo.tblDM_AuditEncounter ae
JOIN dbo.tblDM_NurseLookup an
ON ae.DM_AuditNurseID = an.DM_NurseID
JOIN dbo.tblDM_ProvidersLookup ap
ON ap.DM_ProviderID = ae.DM_ProviderID
JOIN MasterPatientIndex..tblGlobalPatientMaster gpm
ON ae.GMPI = gpm.GMPI
JOIN tblDM_AuditDiag ad
ON ad.DM_AuditEncounterID = ae.DM_AuditEncounterID
JOIN dbo.tblDM_ICDtoHCC hcc
ON hcc.ICD9 = ad.AD_ICD9
WHERE GMPI IN (SELECT TOP 25 -- Gets the top 25 GMPI values
GMPI
FROM MasterPatientIndex..tblGlobalPatientMaster
ORDER BY [Put a Date Column here to make it 2011] DESC) ;
You can modify the sub-query to get you the GMPI values you want to use. I proposed ordering them by a date column in that table, but you might want to use a Where clause in the sub-query to limit to 2011 and then use the Order By to make it more random or something.
Does that help point you in the right direction?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply