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.

GROUP BY rule holding me up - HELP!

I need to pull a new field (bpdeptracking.status) out of this existing query, but to include the field in the select I must also include it in the Group By. This changes the dynamic (and the output) of the query. So I need to figure out a way to keep the existing query intact and still obtain the bpdeptracking.status within the context of the parameters of this query. Can anyone help?

select min(calendarentries.entrydate) as firstdepo,prime.matterid
from dep join bpdeptracking on dep.depid=bpdeptracking.depid
join calendarentries on bpdeptracking.calendarid = calendarentries.calendarid
join depmaps on depmaps.depid = dep.depid
join ( select * from BP_ActivebyPara ) as prime on prime.matterid = depmaps.uniqueid
where calendarentries.entrydate <> '11/11/1911'
and (dep.deptype = 'P' or dep.deptype = 'P/IP')
and bpdeptracking.status in ('Concluded','Continued')
group by prime.matteriduse min(bpdeptracking.status) in the SELECT list|||I'll give that a try. I appreciate the suggestion.|||Unfortunately, "use min(bpdeptracking.status) in the SELECT list" won't work because it returns the minimum value from bpdeptracking.status during the GROUP BY operation. As an example, the GROUP BY field is prime.matterid. If there are 10 rows that are identical based on this field, it will group them together and return the row that meets the min(calendarentries.entrydate) criteria. If I use min(bpdeptracking.status), it will return the minimum value for that column from the 10 rows in the group. I don't want the minimum value, I want the value in the row that is selected by the GROUP BY field using the min(calendarentries.entrydate) criteria.

So I will probably have to use 2 queries unless someone on this forum has an idea on how to accomplish this more efficiently.|||The use of GROUP BY produces an aggregate, it doesn't select any individual row. The two ideas are mutually exclusive, you can only have one or the other, never both in a single query.

By doing some creative "digital dancing", you can make it appear that you are getting a single row. If you can give us some sample data, and the results you'd like to see from that data, then I'd bet that we can help you to conjour it up!

-PatP|||if bpdeptracking.status is identical for every row in each prime.matterid group, then you should be able to take the min or the max, and it will be the same regardless

if it isn't the same for every row in each prime.matterid group, then either you (a) include it in the GROUP BY, which you say you can't do because it "changes the dynamic", or (b) pick any arbitrary value for bpdeptracking.status within each group (which is exactly what my choice of min does), or (c) realize that you cannot do what you are asking to do|||bpdeptracking.status is NOT identical for every row in each prime.matterid group, which is why I can't use MIN or MAX.

For example: say the DB has a group of 10 identical matterid values, each with a different entrydate. When I group those together by matterid and take the MIN(entrydate), I want the actual value of bpdeptracking.status that relates to the MIN(entrydate) for the prime.matterid group. If I use MIN(bpdeptracking.status), it returns the minimum alpha value for bpdeptracking.status from the 10 rows rather than the actual value in the selected row.

Is there a way to select bpdeptracking.status using the query below perhaps as a subquery or as 2 seperate queries to produce the desired result?|||aha! now we are getting someplace! :)

there may still be a problem, because if you want the row with the min entrydate, it may not be related to a status in ('Concluded','Continued')

select prime.matterid
, bpdeptracking.status
, c1.entrydate as firstdepo
from dep
join bpdeptracking
on dep.depid
= bpdeptracking.depid
join calendarentries as c1
on bpdeptracking.calendarid
= c1.calendarid
join depmaps
on dep.depid
= depmaps.depid
join BP_ActivebyPara as prime
on depmaps.uniqueid
= prime.matterid
where c1.entrydate <> '11/11/1911'
and c1.entrydate
= ( select min(entrydate)
from calendarentries
where calendarid
= bpdeptracking.calendarid
and entrydate <> '11/11/1911' )
and dep.deptype in ('P', 'P/IP')
and bpdeptracking.status in ('Concluded','Continued')
group
by prime.matterid
, bpdeptracking.status

GridView - SqlDataSource

I have created a GridView that uses a SqlDataSource. When I run the page it does not pull back any data. However when I test the query in the SqlDataSource dialog box it pulls back data.

Here is my GridView and SqlDataSource:

<

asp:GridViewID="Results"runat="server"AllowPaging="True"AllowSorting="True"CellPadding="2"EmptyDataText="No records found."AutoGenerateColumns="False"Width="100%"CssClass="tableResults"PageSize="20"DataSourceID="SqlResults"><Columns><asp:BoundFieldDataField="DaCode"HeaderText="Sub-Station"SortExpression="DaCode"><ItemStyleHorizontalAlign="Center"CssClass="tdResults"/><HeaderStyleHorizontalAlign="Center"CssClass="tdHeaderResults"/></asp:BoundField><asp:BoundFieldDataField="DpInfo"HeaderText="Delivery Point"SortExpression="DpInfo"><HeaderStyleHorizontalAlign="Left"CssClass="tdHeaderResults"/><ItemStyleCssClass="tdResults"/></asp:BoundField><asp:HyperLinkFieldDataNavigateUrlFields="CuCode,OrderID"DataNavigateUrlFormatString="TCCustDetail.asp?CuCode={0}&OrderID={1}"DataTextField="OrderID"HeaderText="Order No"SortExpression="OrderID"><ItemStyleCssClass="tdResults"HorizontalAlign="Center"/><HeaderStyleCssClass="tdHeaderResults"HorizontalAlign="Center"/></asp:HyperLinkField><asp:BoundFieldHeaderText="Order Date"SortExpression="OrderDate"DataField="OrderDate"><ItemStyleHorizontalAlign="Center"CssClass="tdResults"/><HeaderStyleHorizontalAlign="Center"CssClass="tdHeaderResults"/></asp:BoundField><asp:BoundFieldDataField="ReqDeliveryDate"HeaderText="Req Delivery Date"SortExpression="ReqDeliveryDate"><ItemStyleHorizontalAlign="Center"CssClass="tdResults"/><HeaderStyleHorizontalAlign="Center"CssClass="tdHeaderResults"/></asp:BoundField><asp:BoundFieldDataField="StatusDate"HeaderText="Status Date"SortExpression="StatusDate"><ItemStyleHorizontalAlign="Center"CssClass="tdResults"/><HeaderStyleHorizontalAlign="Center"CssClass="tdHeaderResults"/></asp:BoundField><asp:BoundFieldDataField="ManifestNo"HeaderText="Manifest No"SortExpression="ManifestNo"><ItemStyleHorizontalAlign="Center"CssClass="tdResults"/><HeaderStyleHorizontalAlign="Center"CssClass="tdHeaderResults"/></asp:BoundField><asp:BoundFieldDataField="CustomerPO"HeaderText="P.O. No"SortExpression="CustomerPO"><ItemStyleHorizontalAlign="Center"CssClass="tdResults"/><HeaderStyleHorizontalAlign="Center"CssClass="tdHeaderResults"/></asp:BoundField><asp:BoundFieldDataField="Class"HeaderText="Class"SortExpression="Class"><ItemStyleHorizontalAlign="Left"CssClass="tdResults"/><HeaderStyleHorizontalAlign="Left"CssClass="tdHeaderResults"/></asp:BoundField><asp:BoundFieldDataField="OrderStatus"HeaderText="Order Status"SortExpression="StatusSort"><ItemStyleHorizontalAlign="Left"CssClass="tdResults"/><HeaderStyleHorizontalAlign="Left"CssClass="tdHeaderResults"/></asp:BoundField></Columns><HeaderStyleForeColor="White"HorizontalAlign="Left"/><AlternatingRowStyleCssClass="tdResultsAltRowColor"/></asp:GridView><asp:SqlDataSourceID="SqlResults"runat="server"ConnectionString="<%$ ConnectionStrings:TransportationConnectionString %>"SelectCommand="GetOrderSummaryResults"SelectCommandType="StoredProcedure"><SelectParameters><asp:ParameterDefaultValue="10681"Name="CuCode"Type="String"/><asp:ParameterDefaultValue=""Name="DaCode"Type="String"/><asp:ParameterDefaultValue=""Name="DpCode"Type="String"/><asp:ParameterDefaultValue=""Name="OrderID"Type="String"/><asp:ParameterDefaultValue=""Name="ManifestNo"Type="String"/><asp:ParameterDefaultValue=""Name="PONo"Type="String"/></SelectParameters></asp:SqlDataSource>

I can get it to fill with data by manually filling the GridView without using a SqlDataSource but then I cannot get the sorting to work when I do it that way. Actually not sure if the sorting will work this way either as I cannot get it to fill with data. Any ideas would be much appreciated.

It doesn't appear as though your parameters are collecting any data in SqlDataSource. For instance, if you were storing your parameters in the querystring, you would have in your <asp:QueryParameter /> tags something such as QueryString="", or similar...