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 |
trusted4u
Posting Yak Master
109 Posts |
Posted - 2003-07-18 : 04:36:43
|
Hi,The following are the records of PO_Reciepts table :- PO_OrderNo Part_Name Qty_Recvd Unit_Rate Date_Recvd Return_Flag101 A 10 250.00 01/03/2003 N101 A 5 300.00 01/03/2003 N101 A 4 300.00 02/03/2003 YAs you can c there are 3 records of the same part with same PO_OrderNo. On 02/03/2003 the part with Unit_rate = 300 was returned, so I want to write a query to subtract and get a result set something like this :-PO_OrderNo Part_Name Qty_Recvd Unit_Rate Date_Recvd Return_Flag101 A 10 250.00 01/03/2003 N101 A 1 300.00 01/03/2003 NAny help will be really appreciated.Thanks.- Marjo.Edited by - TRUSTED4u on 07/18/2003 06:04:17 |
|
mr_mist
Grunnio
1870 Posts |
Posted - 2003-07-18 : 04:48:08
|
Try something like this...SELECT PO_orderno, part_name, sum (case when return_flag = 'N' then qty_recvd else -1 * qty_recvd end) as qty_recvd, unit_rate, min (date_recvd)FROMyourtableGROUP BY PO_orderno, part_name, unit_rate-------Moo. :) |
|
|
trusted4u
Posting Yak Master
109 Posts |
Posted - 2003-07-18 : 06:02:58
|
Gr8 Mist.THE QUERY IS TOOOO EXCELLENT AND IT's GIVING ME 100% SAME RESULTS AS I NEEDED.THANKS A MILLION.Best Regards,- Marjo. |
|
|
|
|
|