Select statement-making two columns out of one

[Bufsabres]

I am trying to create two new rows in a report with information gathered from one. Production Quantity-"ProdQty" and Scrap Quantity-"ScrpQty" from one column "production". In the Where clause I specify criteria from another column "WH" as follows 'PQ' OR 'PW' OR 'SP' OR 'S1'...'S7'. How can I make only the 'PQ' AND 'PW' quantities go to the "ProdQty" columm and the 'SP' AND 'S1' TO 'S7' go to "ScrpQty" column in the select statement? I already have a 'CASE' Function in the SELECT clause to change the quantities to a negitive based on a "Credit" column.

Thanks

 

[nigelrivett]

ProdQty = case when WH in ('PQ','PW') then production else 0 end ,
ScrpQty = case when WH in ('SP','S1','S7') then production else 0 end ,


you can put another case round these for the Credit and sum them if you wish.


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[Bufsabres]

Great!

Thanks.