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.

 All Forums
 Old Forums
 CLOSED - General SQL Server
 [ RESOLVED ] Query Question...

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_Flag
101 A 10 250.00 01/03/2003 N
101 A 5 300.00 01/03/2003 N
101 A 4 300.00 02/03/2003 Y

As 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_Flag
101 A 10 250.00 01/03/2003 N
101 A 1 300.00 01/03/2003 N

Any 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)
FROM
yourtable
GROUP BY
PO_orderno, part_name, unit_rate

-------
Moo. :)
Go to Top of Page

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.


Go to Top of Page
   

- Advertisement -