Can this be done without a Cursor

I know its better to do things without a cursor, but I think I may have a situation where I have no choice. I'm still pretty green with my SQL so I thought I'd put this question out there first. Here's the scenario:

With regards to stores ordering product from our warehouse.

Stores can reserve products that are on Purchase Orders that have not yet been received at the warehouse, so that when the product comes in it will be shipped out automatically. The file containing theses reservations is called "PoOrd". Anytime a regular order is processed for a store, the quantity shipped out is deducted from any reserved quantities in the "PoOrd" file. This way the store does not accidentally get overstocked.

PoOrd Example:

ProductID StoreNum PONum Quantity

93 1 121 5

93 2 121 8

100 1 121 10

100 1 221 10

100 1 321 10

101 4 221 7

Say I'm processing an order for Store # 1 that is shipping 15 units of Product # 100, and I need to take 15 units away from the PoOrd file starting from the PO with the largest number. So in other words POOrd should look like this after the Update.

ProductID StoreNum PONum Quantity

93 1 121 5

93 2 121 8

100 1 121 10

100 1 221 5

100 1 321 0

101 4 221 7

Am I right in thinking I have to use a Cursor. Anyone want to show me an example of how to do this.

If you are using SQL Server 2005 you should be able to accomplish this using a CTE and ROW_NUMBER function.

What follows is my first-pass attempt at this problem. I am not sure that I care for this much and I am also not sure if it is going to be any faster than a cursor-based method. It does appear to work, but in this case I am kind-of hoping that somebody knocks a hole in this approach:

declare @.stock table
( ProductId integer,
StoreNum integer,
PONum integer,
quantity integer
)

insert into @.stock
select 93, 1, 121, 5 union all
select 93, 2, 121, 8 union all
select 100, 1, 121, 10 union all
select 100, 1, 221, 10 union all
select 100, 1, 321, 10 union all
select 101, 4, 221, 7
--select * from @.stock

-- -- Table B4 Update: --

-- ProductId StoreNum PONum quantity
-- -- -- -- --
-- 93 1 121 5
-- 93 2 121 8
-- 100 1 121 10
-- 100 1 221 10
-- 100 1 321 10
-- 101 4 221 7

declare @.storeNum integer set @.storeNum = 1
declare @.productId integer set @.productId = 100
declare @.drawQty integer set @.drawQty = 15

;with stock as
( select row_number() over(order by PONum desc) as seq,
PONum,
storeNum,
ProductId,
quantity
from @.stock
where storeNum = @.storeNum
and productId = @.productId
), stockDraw as
( select seq,
PONum,
storeNum,
ProductId,
case when quantity >= @.drawQty
then quantity - @.drawQty
else 0
end as remainingQuantity,
case when quantity >= @.drawQty
then 0
else @.drawQty - quantity
end as remainingDraw
from stock
where seq = 1
union all
select b.seq,
b.PONum,
a.storeNum,
a.productId,
case when quantity > remainingDraw
then quantity - remainingDraw
else 0
end as remainingQuantity,
case when quantity > remainingDraw
then 0
else remainingDraw - quantity
end as remainingDraw
from stockDraw a
inner join stock b
on b.seq = a.seq + 1
and remainingDraw > 0
)
--select * from stockDraw
update @.stock
set quantity = remainingQuantity
from @.stock a
inner join stockDraw b
on a.storeNum = b.storeNum
and a.productId = b.productId
and a.PONum = b.poNum

select * from @.stock

-- ProductId StoreNum PONum quantity
-- -- -- -- --
-- 93 1 121 5
-- 93 2 121 8
-- 100 1 121 10
-- 100 1 221 5
-- 100 1 321 0
-- 101 4 221 7

|||

It looks like a better way of doing this is with an aggregate from a correlated subquery. This looks better:

declare @.storeNum integer set @.storeNum = 1
declare @.productId integer set @.productId = 100
declare @.drawQty integer set @.drawQty = 15

update @.stock
set quantity
= case when drawDown < 0 then 0
else a.quantity - drawdown
end
from @.stock a
inner join
( select PONum,
quantity,
( select sum(q.Quantity)
from @.stock q
where q.storeNum = @.storeNum
and q.productId = @.productId
and q.PONum >= a.PONum
) - @.drawQty as drawDown
from @.stock a
where storeNum = @.storeNum
and productId = @.productId
) b
on a.storeNum = @.storeNum
and a.productId = @.productId
and a.PONum = b.PONum
and a.quantity >= drawDown

select * from @.stock

-- ProductId StoreNum PONum quantity
-- -- -- -- --
-- 93 1 121 5
-- 93 2 121 8
-- 100 1 121 10
-- 100 1 221 5
-- 100 1 321 0
-- 101 4 221 7

|||Impressive! Thankyou. It will take a lot of practice before I'll be comming up with queries like that on my own.|||

In that case you have come to a good spot. The main reason that I worked this site into my daily routine was for the same reason -- I wanted to improve. Try to start by working through and replicating a few of the posts. I found that in the beginning I frequently made mistakes; I still do. But a lot of learning is in making the attempt.

In time you will understand where the answer is likely to be and you will be able to get there. I want to encourage you to give it a try and to also give yourself time to evolve. My email is available; if you ever have any questions give me a ring. I'm also here for you.

Kent

|||So say I put this Update query into a stored procedure and I need to perform it for each item on a shipment. How can I execute a process for each item without going row by row?|||

Chris:

I added a @.SHIPMENT table to the mockup; I really don't know if I have this set up correctly or not so I need your feedback. Please compare this query to the previous query so that you can see the additions and changes. It changed quite a bit:

declare @.stock table
( ProductId integer,
StoreNum integer,
PONum integer,
quantity integer
)

insert into @.stock
select 93, 1, 121, 5 union all
select 93, 2, 121, 8 union all
select 100, 1, 121, 10 union all
select 100, 1, 221, 10 union all
select 100, 1, 321, 10 union all
select 101, 4, 221, 7 union all
select 101, 1, 421, 20 union all
select 101, 1, 521, 30 union all
select 102, 1, 621, 20
--select * from @.stock

-- -- Table B4 Update: --

/*
ProductId StoreNum PONum quantity
-- -- -- --
93 1 121 5
93 2 121 8
100 1 121 10
100 1 221 10
100 1 321 10
101 4 221 7
101 1 421 20
101 1 421 30
102 1 521 20
*/

declare @.shipment table
( shipId integer,
storeNum integer,
productId integer,
shipQty integer
)
insert into @.shipment
select 101, 1, 93, 5 union all
select 101, 1, 100, 15 union all
select 101, 1, 101, 40 union all
select 102, 2, 93, 2
--select * from @.shipment
/*
shipId storeNum productId shipQty
-- -- -- --
101 1 93 5
101 1 100 15
101 1 101 40
102 2 93 2
*/

declare @.shipId integer set @.shipId = 101

;with shipList as
( select shipId,
storeNum,
productId,
shipQty,
shipQty as drawQty
from @.shipment
where shipId = @.shipId
), drawList as
( select shipId,
a.storeNum,
a.productId,
a.shipQty,
row_number() over
( partition by a.storeNum, a.productID
order by PONum desc
) as seq,
PONum,
b.quantity
from shipList a
inner join @.stock b
on a.storeNum = b.storeNum
and a.productId = b.productId
and b.quantity > 0
and a.shipId = @.shipId
), stockDraw as
( select shipId,
storeNum,
productId,
shipQty,
seq,
PONum,
case when quantity >= shipQty
then quantity - shipQty
else 0
end as remainingQty,
case when quantity >= shipQty
then 0
else shipQty - quantity
end as remainingDraw
from drawList
where seq = 1
union all
select a.shipId,
a.storeNum,
a.productId,
a.shipQty,
a.seq + 1 as seq,
b.PONum,
case when b.quantity >= remainingDraw
then b.quantity - remainingDraw
else 0
end as remainingQty,
case when b.quantity >= remainingDraw
then 0
else remainingDraw - b.quantity
end as remainingDraw
from stockDraw a
inner join drawList b
on b.seq = a.seq + 1
and a.productId = b.productId
and a.storeNum = b.storeNum
and remainingDraw > 0
)
update @.stock
set quantity = remainingQty
from stockDraw a
inner join @.stock b
on a.productId = b.productId
and a.storeNum = b.storeNum
and a.PONum = b.PONum

select * from @.stock

-- After Update -
/*
ProductId StoreNum PONum quantity
-- -- -- --
93 1 121 0
93 2 121 8
100 1 121 10
100 1 221 5
100 1 321 0
101 4 221 7
101 1 421 10
101 1 521 0
102 1 621 20
*/