Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
DanielLH
Starting Member
4 Posts |
 Posted - 2008-08-28 : 07:24:00
|
| Hi,Can anyone help me with this one...Query returns...StockCode, SoldIn, BoxQtyInStock, PalletQtyInStockAAA, Boxes, 2, 0AAA, Boxes, 0, 4How can I 'join' these 2 records to return row showing both quantities in stock, eg...AAA, Boxes, 2, 4FYI: Quantities are from a joined table, hence the multiple rows per StockCode.Thanks in advance. |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2008-08-28 : 07:26:54
|
| select StockCode, SoldIn, max(BoxQtyInStock) as BoxQtyInStock, max(PalletQtyInStock) as PalletQtyInStock from table group by StockCode, SoldInMadhivananFailing to plan is Planning to fail |
 |
|
|
DanielLH
Starting Member
4 Posts |
Posted - 2008-08-28 : 08:07:36
|
| Hi Thanks for your reply.Still no luck. This is the query...SELECT StockID, CASE WHEN tblStockLocationLines.UnitType = 'Unit' THEN MAX(Qty) ELSE 0 END AS UnitQty, CASE WHEN UnitType = 'Packet' THEN MAX(Qty) ELSE 0 END AS PacketQty, CASE WHEN UnitType = 'Cases' THEN MAX(Qty) ELSE 0 END AS CasesQty, CASE WHEN UnitType = 'Pallet' THEN MAX(Qty) ELSE 0 END AS PalletQtyFROM dbo.tblStockLocationLinesGROUP BY StockID, UnitTypeThe problem is I am getting duplicate StockIDs because of the UnitType in the GROUP BY clause, however when I remove from the clause, the CASE complains! Any ideas? |
|
|
|
|
|
|
|
|
GROUP BY?