Help! Using a variable to store more than 1 subquery value
-
I'm trying to write a script that looks at sales for various different products and then decrements the stock for each of those products. The following script I wrote, gets sales from all the products and then decrements each of the products with the total figure (I want each each sku decremented only with its particular sales). Any ideas please?
If (select count(sku)
from sku
where sku.labsupplied = 'A012') > 0
begin
set nocount on
DECLARE @orders1 int
set @orders1 = (select count (orders.orderid) as 'orders'
from orders inner join sku on orders.framestylecode = sku.sku
where (orders.orderdate between '2007-05-21' and '2007-05-27')and orders.ordercancel <> 'Y'
and sku.labsupplied = 'A012' and orders.labid = 'A012')
update sku
set qty_in_stock = qty_in_stock - @orders1
from orders inner join sku on orders.framestylecode = sku.sku
where (orders.orderdate between '2007-05-21' and '2007-05-27')and orders.ordercancel <> 'Y'
and sku.labsupplied = 'A012' and orders.labid = 'A012'
end
-
Without DDL it is difficult to tell. Maybe something like:
UPDATE S
SET qty_in_stock = qty_in_stock - D.orders
FROM sku S
JOIN (
SELECT S1.sku, S1.labsupplied ,COUNT(*) AS orders
FROM orders O1
JOIN sku S1
ON O1.framestylecode = S1.sku
AND O1.labid = S1.labsupplied
WHERE O1.orderdate BETWEEN '20070521' AND '20070527'
AND O1.ordercancel <> 'Y'
AND O1.labid = 'A012'
GROUP BY S1.sku, S1.labsupplied
) D
ON S.sku = D.sku
AND S.labsupplied = D.labsupplied
-
Ken, this seems to have done the trick! Thanks for your help!
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply