November 2, 2011 at 6:26 pm
Hi,
This one has me a little stumped - wonder if anyone out there can help.
I have a table variable that contains 2 rows as follows:
customer_code, order_id, product_desc, order_product_quantity, action
131471,23830967, 5kg sausages,90removed
131471,23830969, 5kg sausages,95added
this represents a customer updating their order by changing their quantity of sausages from 90 to 95..
The front end application does not update order records but creates a new order on update so order_id before update was 23830967 and new order_id is 23830969.
What I want to do it return the following by running a query on the table variable
customer_code, order_id, product_desc, order_product_quantity, action
131471,23830969, 5kg sausages,5 added
So I need to check for any products that have the same name, and for those products roll the update into one row, check the quantities between the old order_id and the new_order - if the new quantity is less than the old then action is added and vice versa for removed....
Any takers 😀
Thanks in adavnce for any help..
Cheers,
JayK
November 2, 2011 at 6:51 pm
There is probably a more graceful way, but this will work:
--MAKE TEMP TABLE TO MIMIC YOUR TABLE VARIABLE
CREATE TABLE #snausages (customer_code INT, order_id INT, product_desc VARCHAR(25), order_product_quantity INT, [action] VARCHAR(25))
;
--ADD ROWS
INSERT INTO #snausages
VALUES
(131471,23830967, '5kg sausages',90, 'removed'),
(131471,23830969, '5kg sausages',95, 'added' )
;
--MAKE NEW TEMP TABLE TO ORDER THE ORDERS
SELECTcustomer_code, order_id, product_desc, order_product_quantity, [action]
, ROW_NUMBER() OVER(PARTITION BY product_desc ORDER BY order_id DESC) as OrderOrder
--SORT DESC SO THAT THE MOST RECENT TRANSACTION IS ON TOP
INTO#OrderChange
FROM#snausages
;
--JOIN TEMP TABLE TWICE TO GET CURRENT AND PRIOR ORDERS
SELECTOC1.customer_code, OC1.order_id, OC1.product_desc, OC1.order_product_quantity, OC1.[action], OC1.OrderOrder
,PriorOrder=OC2.order_id , PriorOrderQuantity=oc2.order_product_quantity
--ADD YOUR CALC'D COLUMN
,NewOrderCount=CASE
WHEN OC1.order_product_quantity > OC2.order_product_quantity THEN CONVERT(VARCHAR, OC1.order_product_quantity - OC2.order_product_quantity) + ' added'
WHEN OC1.order_product_quantity <= OC2.order_product_quantity THEN CONVERT(VARCHAR, OC2.order_product_quantity - OC1.order_product_quantity) + ' removed'
END
FROM#OrderChange OC1
INNER JOIN #OrderChange OC2 ON OC1.product_desc = OC2.product_desc
AND OC1.OrderOrder = 1
AND OC2.OrderOrder = OC1.OrderOrder + 1
;
--CLEAN IT UP
drop table #OrderChange
drop table #snausages
;
November 7, 2011 at 5:30 pm
Hey Burninator - thaks for taking the time to reply to me - I was able to incorporate the logic of your query into my existing code to get the desired result 🙂
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply