Difficult Query, with dynamically updated data between rows....

Hi Everybody,
I'm looking for some help putting together a stored procedure to do a
report for my shipping department. What they are looking for is a
report listing a SalesOrder (SO), some of its pertinent information,
and then a list of the inventory we have for that part. Here's the
catch. If the same part is to ship on two seperate SO's, the inventory
must change between them. For example:
SONum Part QTYReq QtyAvail LotNum
1 A 100 120 100k5
2 B 500 800 120j4
3 B 200 300 120j4
4 C 55 50 121b4
You'll notice that for part 'B', we had 800 in stock for the first so,
took off 500, leaving us with 300 to report for the second.
The problem is that I don't really know how to handle this. I don't
even know what to title this post!
So far, I have created temp tables to capture a snapshot of my
inventory (so that I can modify it without hurting the real data) and
to hold the list of SO's data.
My basic plan is to create a cursor on the SO Data, updating the
available inventory for that SO item, then updating the inventory
snapshot, so that on subsequent checks, the new, lower values are ready
to be used.
Is there a better way to do this? Will my plan be likely to succeed?
Thanks for any input you can offer! It is honestly appreciated.
Brian.you could use something like this (untested):
select
SONum, Part, QTYReq,
(QtyAvail - (select sum(t1.QTYReq) from your_table t1
where t1.part=t.part and t1.sonum<t.sonum)) QtyAvail ,
LotNum
from your_table t|||Hrm, I see how that goes...
Lets throw another wrench into the mix.
Add multiple lot numbers for a part so the output looks now something
like:
SONum Part QTYReq QtyAvail LotN
1 A 100 120 100k
2 B 500 360 120j
2 B 500 280 121j
2 B 500 198 122j
3 B 200 0 120j
3 B 200 140 121j
3 B 200 198 122j
4 C 55 50 21b4
How would you work that out? I'll keep trying on my own to see if I
can get it, but if you can suggest, I'd be most grateful.
Thanks,
Brian.|||using row_number(), it's a snap.
without OLAP functions, still doable, something like this:
drop table #lots
go
drop table #requests
go
create table #lots(LotN char(4), part char(1), qty smallint)
insert into #lots values('120j', 'B', 360)
insert into #lots values('121j', 'B', 280)
insert into #lots values('122j', 'B', 122)
create table #requests(SOnum smallint, part char(1), qty smallint)
insert into #requests values(2, 'B', 500)
insert into #requests values(3, 'B', 200)
go
select sonum, part, QTYReq,
case
when prev_reqs<prev_lots then QtyAvail
when prev_reqs between prev_lots and (prev_lots + QtyAvail) then
QtyAvail - prev_reqs + prev_lots
else 0 end QtyAvail,
lotN
from(
select r.sonum, r.part, r.qty QTYReq, l.qty QtyAvail, l.lotN,
isnull((select sum(qty) from #lots l1 where l1.part = l.part and
l1.lotN < l.lotN), 0) prev_lots,
isnull((select sum(qty) from #requests r1 where r1.part = r.part and
r1.SOnum < r.SOnum), 0) prev_reqs
from #lots l, #requests r where r.part = l.part
) t
order by sonum, lotN
note that you need more test data|||Okay! That looks promising...
I'll give that a try tomorrow morning.
Thanks for the response. It was more detailed than I had hoped for,
considering my lack of proper ddl and such.
Thanks again,
Brian|||Alexander (or anybody else),
I've taken your suggestion, and applied it to my situation. It very
nearly does the trick, but something about it just isn't right...I'll
pase the code following, but I'll talk about it up here...
With this particular dataset (ddl included this time :) ), what I have
is 3 seperate sales orders for a part, and 2 lots in inventory. Each
SO is for 400 parts, and the lots are 500 each.
For the first SO in the list it displays correctly. We have 2 lots of
500 to take the 400 out of...
But for the second SO, we should see that we have one lot of 100, and
another of 500, instead of both listing at 100.
The third SO, of course, should list one lot as empty, and the other as
having 200, instead of both listing at 0.
At this point, are we out of options as far as set operations go? I'm
still pondering the Cursor in the back of my mind, but I'd rather avoid
that if its possible.
Thanks again for aiding me. It is appreciated.
==== CODE BLOCK ====
drop table #lots
go
drop table #requests
go
create table #lots
(
customerID varchar(55)
, LotNumber char(44)
, PartNumber char(11)
, Quantity float
, lotPKey int
, departmentCode varchar(22)
, qtyReleased float
, qtyHold float
)
create table #requests
(
customerID varchar(55)
, shipDate datetime
, sortDate datetime
, SONumber varchar(22)
, SOLine varchar(22)
, customerPO varchar(22)
, partNumber varchar(11)
, partXRef varchar(22)
, descText varchar(99)
, qtyOpen float
, sortKey int IDENTITY(1,1)
)
-- -- generate the request data...
-- INSERT INTO #requests (
-- customerID
-- , shipDate
-- , SONumber
-- , SOLine
-- , customerPO
-- , partNumber
-- , partXRef
-- , descText
-- , qtyOpen
-- )
-- SELECT
-- UPPER(SOH.CustomerID)
-- , SOD.ScheduledShipDate
-- , SOH.SONumber
-- , SOD.SOLine
-- , SOH.CustomerPO
-- , SOD.PartNumber
-- , SOD.PartXReference
-- , PM.DescText
-- , SOD.QuantityOrdered - (SOD.QuantityShipped + SOD.QuantityReturned)
as QtyOpen
-- FROM SOHeader SOH
-- INNER JOIN SODetail SOD ON SOH.SONumber = SOD.SONumber
-- INNER JOIN PartMaster PM ON SOD.PartNumber = PM.PartNumber
-- WHERE SOD.ScheduledShipDate >= '1/1/2000' AND SOD.ScheduledShipDate
<= '11/13/2005'
-- AND SOD.PartNumber LIKE '600879%' --600879
-- AND SOH.ClosedFlag <> 1
-- AND SOD.ClosedFlag <> 1
-- ORDER BY
-- UPPER(SOH.CustomerID)
-- , SOD.PartNumber
-- , SOD.ScheduledShipDate
-- , SOH.SONumber
-- , SOD.SOLine
--
INSERT INTO #requests
(customerID , shipDate, SONumber , SOLine, customerPO , partNumber ,
partXRef , descText , qtyOpen)
VALUES ('ACME', '11/01/2005', 'SO0510001', '001', 'xyz', '600111',
'z987', 'ACME Foot Creme', 400)
INSERT INTO #requests
(customerID , shipDate, SONumber , SOLine, customerPO , partNumber ,
partXRef , descText , qtyOpen)
VALUES ('ACME', '11/11/2005', 'SO0510001', '001', 'xyz', '600111',
'z987', 'ACME Foot Creme', 400)
INSERT INTO #requests
(customerID , shipDate, SONumber , SOLine, customerPO , partNumber ,
partXRef , descText , qtyOpen)
VALUES ('ACME', '11/21/2005', 'SO0510001', '001', 'xyz', '600111',
'z987', 'ACME Foot Creme', 400)
-- --generate the lots data
-- INSERT INTO #lots
-- (
-- LotNumber
-- , PartNumber
-- , Quantity
-- , lotPKey
-- , departmentCode
-- , customerID
-- )
-- SELECT
-- IL.SNLotNumber
-- , IL.PartNumber
-- , IL.Quantity
-- , IL.InventoryLots_PKey
-- , IL.DepartmentCode
-- , P.SUOCode
-- FROM InventoryLots IL
-- INNER JOIN PartMaster P ON IL.PartNumber = P.PartNumber
-- WHERE IL.PartNumber LIKE '6%'
INSERT INTO #lots
(LotNumber, PartNumber, Quantity, lotPKey, departmentCode, CustomerID)
VALUES ('123j4' , '600111', 500, 1, 'FGINR', 'ACME')
INSERT INTO #lots
(LotNumber, PartNumber, Quantity, lotPKey, departmentCode, CustomerID)
VALUES ('124j4' , '600111', 500, 1, 'FGINR', 'ACME')
UPDATE #lots
SET qtyReleased = Quantity
FROM #lots
WHERE DepartmentCode = 'FGINR'
AND CustomerID <> 'CP'
UPDATE #lots
SET qtyReleased = Quantity
FROM #lots
WHERE DepartmentCode = 'FGINP'
AND CustomerID = 'CP'
UPDATE #lots
SET qtyHold = Quantity
FROM #lots
WHERE DepartmentCode = 'HOLD FGIN'
AND CustomerID <> 'CP'
UPDATE #lots
SET qtyHold = Quantity
FROM #lots
WHERE DepartmentCode = 'HOLDN FGIN'
AND CustomerID = 'CP'
--set the sort date for all shipments...
UPDATE #requests
SET sortDate = Z.minshipdate
FROM #requests R
INNER JOIN
(
SELECT PartNumber, MIN(shipDate) as minshipdate FROM #requests
GROUP BY PartNumber
) Z ON R.PartNumber = Z.PartNumber
select
customerID
, shipDate
, sortDate
, SONumber
, SOLine
, customerPO
, partNumber
, partXRef
, descText
, qtyOpen
--, qtyReleased
, case
when prev_reqs < prev_lots then qtyReleased
when prev_reqs between prev_lots and (prev_lots + qtyReleased) then
qtyReleased - prev_reqs + prev_lots
else 0
end qtyReleased
, qtyHold
, lotNumber
from(
select
r.customerID
, r.shipDate
, r.sortDate
, r.SONumber
, r.SOLine
, r.customerPO
, r.partNumber
, r.partXRef
, r.descText
, ISNULL(r.qtyOpen , 0) qtyOpen
, ISNULL(l.qtyReleased , 0) qtyReleased
, ISNULL(l.qtyHold , 0) qtyHold
, l.lotNumber
, isnull(
(select sum(qtyReleased)
from #lots l1
where
l1.partNumber = l.partNumber and
l1.lotPKey < l.lotPKey
), 0) prev_lots
, isnull(
(select sum(qtyOpen)
from #requests r1
where r1.partNumber = r.partNumber
and r1.sortKey < r.sortKey
), 0) prev_reqs
from #lots l, #requests r
where r.partNumber = l.partNumber
) t
--order by sonum, lotN|||On 10 Nov 2005 08:08:50 -0800, Brian Ackermann wrote:

>Alexander (or anybody else),
>I've taken your suggestion, and applied it to my situation. It very
>nearly does the trick, but something about it just isn't right...I'll
>pase the code following, but I'll talk about it up here...
>With this particular dataset (ddl included this time :) ), what I have
>is 3 seperate sales orders for a part, and 2 lots in inventory. Each
>SO is for 400 parts, and the lots are 500 each.
>For the first SO in the list it displays correctly. We have 2 lots of
>500 to take the 400 out of...
>But for the second SO, we should see that we have one lot of 100, and
>another of 500, instead of both listing at 100.
>The third SO, of course, should list one lot as empty, and the other as
>having 200, instead of both listing at 0.
>At this point, are we out of options as far as set operations go? I'm
>still pondering the Cursor in the back of my mind, but I'd rather avoid
>that if its possible.
>Thanks again for aiding me. It is appreciated.
Hi Brian,
Thabks for posting DDL and sample data. However, I'm not sure if your
data is correct. You mention three sales orders and two lots in your
post, yet I see only one SONumber and one LotNumber in the output!
Anyway - this kind of problem CAN be tackled with a setbased operation,
but they often perform very bad. Because they require some correlated
subqueries and/or self-joins, the typical query plan often involves
multiple table scans. If you can find a cursor-based solution that only
needs to iterate over all rows once, it'll probably be faster than a
set-based version.
If you still want to try a set-based solution, I'll try to help you. But
not now - it's past midnight here; I'd just make errors. Please try to
explain me how your data holds three sales orders and two lot numbers,
even though I see only one of each. Or correct your data if you made a
mistake. I'll take a jab at a set-based solution later (after seeing an
explanation or a correction of your test data).
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||You might want to look at a pair of articles I have posted on
DBAzine.com on inventory control queries.sql