April 6, 2011 at 3:54 am
Dear All,
I tried to search on forums related to my query, but I was unsuccessful in getting any similar information. Please pardon me if I am asking something which already been question from someone else.
I have following data in table/ view for all incoming stock for an item.The Current stock for the item is 41 units:
-----------------------------------------------------------------------
Item Code| Trx Qty|Trx Date| DaysDiff (31-03-2011)
001B00XAA| 10 |02/01/2011| 88
001B00XAA| 25 | 14/01/2011| 76
001B00XAA| 12 |21/01/2011| 69
001B00XAA| 15 |31/01/2011| 59
001B00XAA| 09 |13/02/2011| 46
001B00XAA| 02 |18/02/2011| 41
001B00XAA| 20 |27/02/2011| 32
001B00XAA| 08 |10/03/2011| 21
001B00XAA| 10 | 23/03/2011| 08
001B00XAA| 07 |29/03/2011| 02
-----------------------------------------------------------------------
Now based on above data I need to do the aging of stock. And my desired result should be like this:
Item Code| A(0-30)| B(31-60)| C(61-90)| D(Over 91)
001B00XAA| 25| 16| 0| 0
Means what my requirement is I want to go back from Latest stock till I reached to my Current Stock and then remaining stock should reflect as Zero . To achieve this do I need to use Loop and/or a Temp table in SQL?
Thanks for anticipation.
-Amjad.Ali 🙂
April 6, 2011 at 10:16 am
WITH a ([Item Code],A,B,C,D) AS (
SELECT [Item Code],
SUM(CASE WHEN DaysDiff BETWEEN 0 AND 30 THEN [Trx Qty] ELSE 0 END) AS [A],
SUM(CASE WHEN DaysDiff BETWEEN 31 AND 60 THEN [Trx Qty] ELSE 0 END) AS ,
SUM(CASE WHEN DaysDiff BETWEEN 61 AND 90 THEN [Trx Qty] ELSE 0 END) AS [C],
SUM(CASE WHEN DaysDiff >= 91 THEN [Trx Qty] ELSE 0 END) AS [D]
FROM [Table]
GROUP BY [Item Code]
)
SELECT [Item Code],
CASE WHEN A > 41 THEN 41 ELSE A END AS [A],
CASE WHEN A+B > 41 THEN (41-A) ELSE B END AS ,
CASE WHEN A+B > 41 THEN 0 WHEN A+B+C > 41 THEN (41-A-B) ELSE C END AS [C],
CASE WHEN A+B+C > 41 THEN 0 WHEN A+B+C+D > 41 THEN (41-A-B-C) ELSE D END AS [D]
FROM a
Far away is close at hand in the images of elsewhere.
Anon.
April 7, 2011 at 5:21 am
Dear David!
Thanks for great support.
This really help for me. Just one confusion for following line:
CASE WHEN A+B > 41 THEN (41-A) ELSE B END AS B
for some items it is giving me Negative values also. I tried to figure it out but useless :unsure:
Any clue?
Thanks Once again!
-Amjad Ali
April 7, 2011 at 5:42 am
When you state 'for some items' are you using 41 (stock) for all items or did you substitute each item's stock for the literal 41.
Far away is close at hand in the images of elsewhere.
Anon.
April 10, 2011 at 4:48 am
Dear David,
Yes I substitute 41 with each item current stock. Below is my original query:
WITH Age ([ItemCode],A,B,C,D) AS (
SELECT [ItemCode],
SUM(CASE WHEN DateDiff(day,DateRecd,GetDate()) BETWEEN 0 AND 30 THEN QtyRecvd ELSE 0 END) AS [A],
SUM(CASE WHEN DateDiff(day,DateRecd,GetDate()) BETWEEN 31 AND 60 THEN QtyRecvd ELSE 0 END) AS ,
SUM(CASE WHEN DateDiff(day,DateRecd,GetDate()) BETWEEN 61 AND 90 THEN QtyRecvd ELSE 0 END) AS [C],
SUM(CASE WHEN DateDiff(day,DateRecd,GetDate()) >= 91 THEN QtyRecvd ELSE 0 END) AS [D]
FROM Item_Purchase_Table
WHERE Purch_Type IN (1,5,6)
GROUP BY [ItemCode] )
SELECT Age.[ItemCode],
CASE WHEN A > Stock.QtyonHand THEN Stock.QtyonHand ELSE A END AS [A],
CASE WHEN A+B > Stock.QtyonHand THEN (Stock.QtyonHand-A) ELSE B END AS ,
CASE WHEN A+B > Stock.QtyonHand THEN 0 WHEN A+B+C > Stock.QtyonHand THEN (Stock.QtyonHand-A-B) ELSE C END AS [C],
CASE WHEN A+B+C > Stock.QtyonHand THEN 0 WHEN A+B+C+D > Stock.QtyonHand THEN (Stock.QtyonHand-A-B-C) ELSE D END AS [D]
FROM Age inner join Item_Stock_Table Stock on Age.[ItemCode] = Stock.[ItemCode]
WHERE Stock.RCRDTYPE = 1 AND Stock.QtyonHand > 0
ORder By 1
April 10, 2011 at 10:09 pm
I've done this type of stock age/drawn queries before - using non loop/cursor (prior to cte). David's code is almost there and shown why CTE is cool - all it needs is a minor logic fix (well, some test code doesn't hurt):
declare @rec table(code varchar(64), qty int, dt varchar(32), delta int)
INSERT @rec(code, qty, dt, delta)
values ('001B00XAA',10,'02/01/2011',88)
,('001B00XAA',25,'14/01/2011',76)
,('001B00XAA',12,'21/01/2011',69)
,('001B00XAA',15,'31/01/2011',59)
,('001B00XAA',09,'13/02/2011',46)
,('001B00XAA',02,'18/02/2011',41)
,('001B00XAA',20,'27/02/2011',32)
,('001B00XAA',08,'10/03/2011',21)
,('001B00XAA',10,'23/03/2011',08)
,('001B00XAA',07,'29/03/2011',02)
declare @Stock table(code varchar(64), onhand int)
insert @Stock(code, onhand)
values ('001B00XAA',17); -- change this qty to test
WITH Age (code,A,B,C,D) AS (
SELECT code,
SUM(CASE WHEN delta BETWEEN 0 AND 30 THEN qty ELSE 0 END) AS A,
SUM(CASE WHEN delta BETWEEN 31 AND 60 THEN qty ELSE 0 END) AS B,
SUM(CASE WHEN delta BETWEEN 61 AND 90 THEN qty ELSE 0 END) AS C,
SUM(CASE WHEN delta >= 91 THEN qty ELSE 0 END) AS D
FROM @rec
GROUP BY code )
SELECT a.code,
CASE WHEN A > o.onhand THEN o.onhand ELSE A END AS A,
CASE WHEN (o.onhand-A)>0 THEN CASE WHEN B>(o.onhand-A) THEN (o.onhand-A) ELSE B END ELSE 0 END AS B,
CASE WHEN (o.onhand-(A+B))>0 THEN CASE WHEN C>(o.onhand-(A+B)) THEN (o.onhand-(A+B)) ELSE C END ELSE 0 END AS C,
CASE WHEN (o.onhand-(A+B+C))>0 THEN CASE WHEN D>(o.onhand-(A+B+C)) THEN (o.onhand-(A+B+C)) ELSE D END ELSE 0 END AS D
FROM Age a
INNER JOIN @Stock o ON a.code = o.code
April 11, 2011 at 1:50 am
Thanks Flexdog for helping 🙂
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply