June 11, 2012 at 2:24 pm
Looking for some direction on a query that basically combine data in the QOH table with another table that stores all qty receipt history. Looking to avoid cursoring due to the amount of data in the actual tables.
I have an inventory quantity table which stores quantity on hand:
CREATE TABLE InvQOH (Item VARCHAR(20),QOH INTEGER)
INSERT INTO InvQOH (Item,QOH)
SELECT 'ITEM1',12000
UNION
SELECT 'ITEM2',24000
SELECT Item,QOH FROM InvQOH
I have a receipt history table, which is a history of all receipts for all items.
CREATE TABLE InvRecHist (Item VARCHAR(20),DateRecd DATETIME, QtyRecd INTEGER)
INSERT INTO InvRecHist (Item,DateRecd,QtyRecd)
SELECT 'ITEM1','20120101',2500
UNION
SELECT 'ITEM1','20120201',2500
UNION
SELECT 'ITEM1','20120301',2500
UNION
SELECT 'ITEM1','20120401',2500
UNION
SELECT 'ITEM1','20120501',2500
UNION
SELECT 'ITEM1','20120601',2500
UNION
SELECT 'ITEM2','20120315',10000
UNION
SELECT 'ITEM2','20120415',10000
UNION
SELECT 'ITEM2','20120515',10000
SELECT Item,DateRecd,QtyRecd FROM InvRecHist
FIFO with no exception is assumed. What's needed is a query that takes the quantity on hand and calculates what's left out of the receipts for the item.
For example, taking the above 2 tables and data contained within, looking for a query that would return:
ITEM1 2012-02-01 2000
ITEM1 2012-03-01 2500
ITEM1 2012-04-01 2500
ITEM1 2012-05-01 2500
ITEM1 2012-06-01 2500
ITEM2 2012-03-15 4000
ITEM2 2012-04-15 10000
ITEM2 2012-05-15 10000
Basically, the entire lot of ITEM1 received on 1/1/12 was depleted and 500 units of the lot of ITEM1 received 2/1/12 leaving 2000.
I'm thinking a recursive CTE or something of that nature would do the trick, but having trouble thinking of where to start with that.
Same idea for ITEM2, of the 10000 units received 3/15, 6000 were depleted leaving a net available of 4000.
Any help appreciated, thanks.
June 11, 2012 at 2:41 pm
lbrigham (6/11/2012)
Basically, the entire lot of ITEM1 received on 1/1/12 was depleted and 500 units of the lot of ITEM1 received 2/1/12 leaving 2000.I'm thinking a recursive CTE or something of that nature would do the trick, but having trouble thinking of where to start with that.
Same idea for ITEM2, of the 10000 units received 3/15, 6000 were depleted leaving a net available of 4000.
I'm not sure if something got dropped from your original post or if I'm just not adding apples to apples here. Where in here is there something that indicates that 500 units were depleted from the stocks for Item1?
I understand your general question, I'm just not quite sure how you got from point A to point B...
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
June 11, 2012 at 2:50 pm
The assumption is that those units were sold and removed from QOH or depleted for purposes beyond our visibility for this task. Basically, I'm only looking at QOH in relation to units received, and that's it.
Essentially, a looping structure of some sort is what's needed taking QOH and subtracting received qty from MAX(DOCDATE) until you arrive at a point where QOH has been accounted for in all receipts. I've seen some very creative recursion using CTE's that I think would make this a solvable problem with a recursive CTE. The anchor would look something like this:
SELECT a.Item,a.DateRecd
,CASE WHEN a.QtyRecd<b.QOH THEN a.QtyRecd ELSE b.QOH END
FROM InvRecHist a INNER JOIN InvQOH b ON a.Item=b.Item
WHERE a.DateRecd=(SELECT MAX(DateRecd) FROM InvRecHist WHERE Item=a.Item)
AND b.QOH>0
There was a QotD not too long ago (I thought anyway) that did some similar type of looping via a CTE to loop through the ASCII chart of all 255 characters, but haven't been able to locate that.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply