Grouping Sorting String and Numerical Fields

I've got a report built and I'm trying to figure out how sorting and grouping works. I can group the report by Patient, Albumin and it groups as I would expect.

Patient Date Albumin
Adams, John 01/28/2007 4.1
Adams, John 12/30/2007 3.9
Adams, John 01/15/2007 3.2
Barker, Mark 01/18/2007 4.3
Barker, Mark 01/22/2007 4.1
Barker, Mark 01/05/2007 3.9

However, when I try to group by Albumin, Patient, it just sorts by Albumin.
Patient Date Albumin
Barker, Mark 01/18/2007 4.3
Adams, John 01/28/2007 4.1
Barker, Mark 01/22/2007 4.1
Adams, John 12/30/2007 3.9
Barker, Mark 01/05/2007 3.9
Adams, John 01/15/2007 3.2

What I'm looking for is this:
Patient Date Albumin
Barker, Mark 01/18/2007 4.3
Barker, Mark 01/22/2007 4.1
Barker, Mark 01/05/2007 3.9
Adams, John 01/28/2007 4.1
Adams, John 12/30/2007 3.9
Adams, John 01/15/2007 3.2

Is this something that can be done with grouping and sorting?

Thanks,
Chad

Hi,

guess that you did not want to group you wanted to just sort, right ? For getting the results pasted below you will have to Sort by Patient Desc, Albumin Desc. If that is not your intention, please post the RDL and the way you want to display the information.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Group by statement problem

I am using the T-SQL code below to pull patient information. The code returns 86 rows, however, there are only 9 distinct account numbers. Why is the group by statement not grouping these together to only display the 9 distinct accounts and associated data?

select
srm.episodes.episode_type as Visit_Type,
srm.episodes.account_number as Account_Number,
srm.episodes.medrec_no as MRN,
dbo.PtMstr.PatientFullName,
left(srm.episodes.admission_date,11) as Admit_Date,
left(srm.episodes.episode_date,11) as Disch_Date,
dbo.PtMstr.Cases as Cases,
dbo.PtMstr.TotCharges,
srm.cdmab_base_info.abst_cmp_status as Abtract_Comp_Status,
srm.cdmab_base_info.adm_dx_adt as Admitting_Dx
,srm.event_types.event_type_code
from srm.cdmab_base_info inner join
srm.episodes on srm.episodes.episode_key = srm.cdmab_base_info.episode_key
inner join srm.event_history on srm.event_history.item_key = srm.episodes.episode_key
inner join srm.event_types on srm.event_types.event_type_key = srm.event_history.event_type_key
inner join dbo.PtMstr on dbo.PtMstr.AccountNumber = srm.episodes.account_number
where srm.cdmab_base_info.abst_cmp_status <> 'Y'
and srm.episodes.episode_date is not null
and srm.event_types.event_type_code <> 'ACOD'
AND srm.EPISODES.EPISODE_DATE Between @.StartDate and @.EndDate
AND srm.EPISODES.EPISODE_TYPE IN(@.VisitTypeCode)
Group By srm.episodes.account_number,
dbo.PtMstr.TotCharges,
srm.episodes.episode_type,
srm.episodes.medrec_no,
dbo.PtMstr.PatientFullName,
srm.episodes.admission_date,
srm.episodes.episode_date,
dbo.PtMstr.Cases,
srm.cdmab_base_info.abst_cmp_status,
srm.cdmab_base_info.adm_dx_adt,
srm.event_types.event_type_code

Use the following query..

select

srm.episodes.episode_type as Visit_Type,

srm.episodes.account_number as Account_Number,

srm.episodes.medrec_no as MRN,

dbo.PtMstr.PatientFullName,

left(srm.episodes.admission_date,11) as Admit_Date,

left(srm.episodes.episode_date,11) as Disch_Date,

dbo.PtMstr.Cases as Cases,

dbo.PtMstr.TotCharges,

srm.cdmab_base_info.abst_cmp_status as Abtract_Comp_Status,

srm.cdmab_base_info.adm_dx_adt as Admitting_Dx

,srm.event_types.event_type_code

from

srm.cdmab_base_info

inner join srm.episodes on srm.episodes.episode_key = srm.cdmab_base_info.episode_key

inner join srm.event_history on srm.event_history.item_key = srm.episodes.episode_key

inner join srm.event_types on srm.event_types.event_type_key = srm.event_history.event_type_key

inner join dbo.PtMstr on dbo.PtMstr.AccountNumber = srm.episodes.account_number

where

srm.cdmab_base_info.abst_cmp_status <> 'Y'

and srm.episodes.episode_date is not null

and srm.event_types.event_type_code <> 'ACOD'

AND srm.EPISODES.EPISODE_DATE Between @.StartDate and @.EndDate

AND srm.EPISODES.EPISODE_TYPE IN(@.VisitTypeCode)

Group By

srm.episodes.account_number,

dbo.PtMstr.TotCharges,

srm.episodes.episode_type,

srm.episodes.medrec_no,

dbo.PtMstr.PatientFullName,

left(srm.episodes.admission_date,11) as Admit_Date,

left(srm.episodes.episode_date,11) as Disch_Date,

dbo.PtMstr.Cases,

srm.cdmab_base_info.abst_cmp_status,

srm.cdmab_base_info.adm_dx_adt,

srm.event_types.event_type_code

|||

I had to remove the AS portion of the group by clause to get the code to work , however, it still returns 86 rows versus the expected 9 distinct rows.

|||

How you know there is only 9 distinct record. You only the get the number of rows as per the following query..& i didn't understand your requirement on your query(there is no group by funcations used).

select Distinct

srm.episodes.episode_type as Visit_Type,

srm.episodes.account_number as Account_Number,

srm.episodes.medrec_no as MRN,

dbo.PtMstr.PatientFullName,

left(srm.episodes.admission_date,11) as Admit_Date,

left(srm.episodes.episode_date,11) as Disch_Date,

dbo.PtMstr.Cases as Cases,

dbo.PtMstr.TotCharges,

srm.cdmab_base_info.abst_cmp_status as Abtract_Comp_Status,

srm.cdmab_base_info.adm_dx_adt as Admitting_Dx

,srm.event_types.event_type_code

from

srm.cdmab_base_info

inner join srm.episodes on srm.episodes.episode_key = srm.cdmab_base_info.episode_key

inner join srm.event_history on srm.event_history.item_key = srm.episodes.episode_key

inner join srm.event_types on srm.event_types.event_type_key = srm.event_history.event_type_key

inner join dbo.PtMstr on dbo.PtMstr.AccountNumber = srm.episodes.account_number

where

srm.cdmab_base_info.abst_cmp_status <> 'Y'

and srm.episodes.episode_date is not null

and srm.event_types.event_type_code <> 'ACOD'

AND srm.EPISODES.EPISODE_DATE Between @.StartDate and @.EndDate

AND srm.EPISODES.EPISODE_TYPE IN(@.VisitTypeCode)

|||

I appreciate you help. I ordered the data by account number and saw there were 9 distinct account numbers. I also noticed the srm.event_types.event_type_code field should not have been in this query; once I removed it, the code returned the expected 9 rows of data using either of the examples you provided. Thanks again for your assistance.

|||

You are grouping several additional columns after the account number.

If you just want the nine accounts listed, you'll need to just group on that column.

Then you can apply aggregates to get sums, etc. of the other data you desire.