How to change query so that it does not have to group by the Unit ID column

Question

How to change query so that it does not have to group by the Unit ID column

theeggman

Old Hand

Points: 387
More actions
September 30, 2011 at 5:36 pm

#258158

The following query needs to be changed so that the total QTY, total Pallets, total Baskets, total Carts are displayed on the same record. Currently, since I have to include Unit Id in my group by, it will display a record for each unit id. How can I change it to display the totals mentioned above for each store id/route template id record?
[/URL]
Select
SalesTable.asiStoreId,
InventTableModule.UnitId,
SalesTable.asiDlvRouteTemplateId,
SalesTable.ShippingDateConfirmed,
SalesTable.ReceiptDateConfirmed,
Sum(InventTrans.Qty) * -1 as Qty,
Case when InventTableModule.UnitId <> 'CT' and sum(InventTrans.Qty) * -1 < 60
Then 1
else
Case when InventTableModule.UnitId <> 'CT' and sum(InventTrans.Qty) * -1 between 60 and 97 then 2
else
Case when InventTableModule.UnitId <> 'CT' and sum(InventTrans.Qty) * -1 between 108 and 144 then 3
else
Case when InventTableModule.UnitId <> 'CT' and sum(InventTrans.Qty) * -1 between 156 and 192 then 4
else
Case when InventTableModule.UnitId <> 'CT' and sum(InventTrans.Qty) * -1 between 204 and 288 then 5
else
Case when InventTableModule.UnitId <> 'CT' and sum(InventTrans.Qty) * -1 between 252 and 268 then 6
else
Case when InventTableModule.UnitId <> 'CT' and sum(InventTrans.Qty) * -1 = 300 then 7
else
Case when InventTableModule.UnitId <> 'CT' and sum(InventTrans.Qty) * -1 = 360 then 8
else
Case when InventTableModule.UnitId <> 'CT' and sum(InventTrans.Qty) * -1 = 420 then 9
else 0
end end end end end end end end end as Pallets,
Case when InventTableModule.UnitId <> 'CT' Then Sum(InventTrans.Qty) * -1 Else 0 End as Baskets,
Case when InventTableModule.UnitId = 'CT' Then Sum(InventTrans.Qty) * -1 Else 0 End as Carts
From
SalesTable
Join InventTrans ON InventTrans.TransRefId = SalesTable.SalesId AND InventTrans.DataAreaId = SalesTable.DataAreaId
Join InventTableModule ON InventTrans.Itemid = InventTableModule.ItemId AND InventTableModule.ModuleType = 0 and InventTrans.DataAreaId = InventTableModule.DataAreaId
Join CustTable On CustTable.AccountNum = SalesTable.CustAccount and CustTable.DataAreaId = SalesTable.DataAreaId
Where
SalesTable.ShippingDateConfirmed = @Date
And CustTable.asiStoreGroupId Like 'RALPHS'
And SalesTable.DataAreaId = 'nuc'
Group by
SalesTable.asiStoreId,
SalesTable.asiDlvRouteTemplateId,
SalesTable.ShippingDateConfirmed,
SalesTable.ReceiptDateConfirmed,
InventTableModule.UnitId
Order by asiDlvRouteTemplateId

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

I'd like to suggest that you read the first article I reference below in my signature block regarding asking for help. It will show you what you need to post to get the best possible ansers. In addition, when posing sample data, also post your expected results based on the sample data. It will give us something to test against.

LutzM SSC Guru Points: 107049 More actions · Answer 2

You might want to look into CROSS APPLY.

Sounds like one possible solution to the problem.

For details please follow the advice given by Lynn.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 3

theeggman (9/30/2011)
The following query needs to be changed so that the total QTY, total Pallets, total Baskets, total Carts are displayed on the same record. Currently, since I have to include Unit Id in my group by, it will display a record for each unit id. How can I change it to display the totals mentioned above for each store id/route template id record?
[/URL]

Can you post what you'd like the output to look like, please?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

theeggman Old Hand Points: 387 More actions · Answer 4

Here is what I am looking for. The result set is not grouped by the unit id. If you look at store id 339 you will see that the total for carts are on the same line as the totals for pallets and baskets. Same thing for store id 357. The issue is that the number of carts can not be included in the total number of pallets or baskets. Let me know if you need more information.

[/URL]

theeggman Old Hand Points: 387 More actions · Answer 5

I was able to get it resolved by inserting the unit id within the SUM statement as seen below for the pallets.

Case

when Sum(Case When InventTableModule.UnitId <> 'CT' then (InventTrans.Qty) * -1 else 0 end) between 1 and 59 Then 1

when Sum(Case When InventTableModule.UnitId <> 'CT' then (InventTrans.Qty) * -1 else 0 end) between 60 and 97 then 2

when Sum(Case When InventTableModule.UnitId <> 'CT' then (InventTrans.Qty) * -1 else 0 end) between 108 and 144 then 3

when Sum(Case When InventTableModule.UnitId <> 'CT' then (InventTrans.Qty) * -1 else 0 end) between 156 and 192 then 4

when Sum(Case When InventTableModule.UnitId <> 'CT' then (InventTrans.Qty) * -1 else 0 end) between 204 and 288 then 5

when Sum(Case when InventTableModule.UnitId <> 'CT' then (InventTrans.Qty) * -1 else 0 end) between 252 and 268 then 6

when Sum(Case when InventTableModule.UnitId <> 'CT' then (InventTrans.Qty) * -1 else 0 end) = 300 then 7

when Sum(Case when InventTableModule.UnitId <> 'CT' then (InventTrans.Qty) * -1 else 0 end) = 360 then 8

when Sum(Case when InventTableModule.UnitId <> 'CT' then (InventTrans.Qty) * -1 else 0 end) = 420 then 9

else 0

end Pallets,

The result is:

[/URL]