March 25, 2004 at 4:23 pm
Hello all:
Given the following situation
Customer_No Date Order_No Part_no Total_Amount_Paid
234 1/1/02 14 777 500.00
234 1/1/02 14 855 500.00
233 8/1/03 15 55 300.00
I have order 14 for customer 234, total amount paid is 500.00, but I have $500.00 for each part in order 14, making the total 1000.
Question is, how can I remove one of the 500s and replace it with a 0, I only care about the total amount paid for the order not each item amount.
Appreciate your help.
March 25, 2004 at 4:47 pm
If that is the case don't select the part number. If you only select customer, date, order number, total you will only get 2 records.
mom
March 25, 2004 at 5:03 pm
March 25, 2004 at 5:05 pm
March 25, 2004 at 5:58 pm
FIrst you really need to normalize this table by moving Part_No into another table with Order_No and any other field to use as a key to tie back.
However that said this should do the trick for all of the records that nedd this treatment.
Make sure you test first and backup before actual run.
BEGIN TRAN
--Step one set first item to total cost of entire order.
UPDATE X1
SET
Total_Amount_Paid = TOTAL
FROM
dbo.tblX X1
INNER JOIN
(
SELECT
Customer_No
,[Date]
,Order_No
,Min(Part_no) AS Part_No
,SUM(Total_Amount_Paid) AS TOTAL
FROM dbo.tblX X2
GROUP BY
Customer_No
,[Date]
,Order_No
) AS MinPart
ON
X1.Customer_No = MinPart AND
X1.[DATE] = MinPart.[Date] AND
X1.Order_No = MinPart.Order_No AND
X1.Part_No = MinPart.Part_No
-- Step 2 set all others to 0.
UPDATE X1
SET
Total_Amount_Paid = 0
FROM
dbo.tblX X1
LEFT JOIN
(
SELECT
Customer_No
,[Date]
,Order_No
,Min(Part_no) AS Part_No
FROM dbo.tblX X2
GROUP BY
Customer_No
,[Date]
,Order_No
) AS MinPart
ON
X1.Customer_No = MinPart AND
X1.[DATE] = MinPart.[Date] AND
X1.Order_No = MinPart.Order_No AND
X1.Part_No = MinPart.Part_No
WHERE
MinPart.Part_No IS NULL
COMMIT TRAN
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply