Results 1 to 13 of 13
Thread: FIFO query with applied logic

070910, 16:48 #1Registered User
 Join Date
 Jul 2010
 Posts
 8
Unanswered: FIFO query with applied logic
I have a discussion with an accountant guy about his logic about the inventory on FIFO basis.
Actually, I already solve this with the use of temporary table and derived tables.
But it is not acceptable for me to have 214 lines to solve the problem. Can you show me you're approach?
select '4718460001111' itemid, 5 period, 155 qty_in, 54 qty_out, 58 logic, 0 rem union all
select '4718460001111' itemid, 4 period, 345 qty_in, 61 qty_out, 287 logic, 287 rem union all
select '4718460001111' itemid, 3 period, 215 qty_in, 18 qty_out, 215 logic, 215 rem union all
select '4718460001111' itemid, 2 period, 185 qty_in, 30 qty_out, 185 logic, 185 rem union all
select '4718460001111' itemid, 1 period, 531 qty_in, 50 qty_out, 531 logic, 531 rem union all
select '5011321835337' itemid, 5 period, 150 qty_in, 44 qty_out, 96 logic, 0 rem union all
select '5011321835337' itemid, 4 period, 354 qty_in, 66 qty_out, 258 logic, 258 rem union all
select '5011321835337' itemid, 3 period, 488 qty_in, 32 qty_out, 488 logic, 488 rem union all
select '5011321835337' itemid, 2 period, 254 qty_in, 46 qty_out, 254 logic, 254 rem union all
select '5011321835337' itemid, 1 period, 418 qty_in, 58 qty_out, 418 logic, 418 rem
the rows are arranged by itemid and period descending.
for the logic column:
1. the last period (5th) is calculated by qty_in less with the sum of qty_out per itemid.
2. for periods 4,3,2 and 1 are calculated by getting the previous logic column value.
4 will get from 5, 3 will get from 4 and so on. It will check if the previous
logic column value is negative. If so, then the previous value will be added to current
row's qty_in. Now if the previous logic column is positive, just display the row's qty_in.
for the rem column:
the result is base on the logic column. If it is negative, then the value is zero else
retain the logic column value.
Any help is highly appreciated. Thanks in advance.

070910, 17:08 #2Resident Curmudgeon
 Join Date
 Feb 2004
 Location
 In front of the computer
 Posts
 15,579
Provided Answers: 54You appear to have all of the columns. I sense a question in there somewhere, but I can't find it. I'd help if I could, but I don't understand what you want.
PatPIn theory, theory and practice are identical. In practice, theory and practice are unrelated.

070910, 17:26 #3Registered User
 Join Date
 Jul 2010
 Posts
 8
what I want is the logic and rem column. The requirement is specified.

070910, 19:10 #4Registered User
 Join Date
 May 2009
 Posts
 509
Provided Answers: 1jonasalbert, While you may know your process and data 'insideout', your 'rules' are a little confusing (and you may have a typo for a value which does not help).
First, will you always have 5 periods per 'group'?
Even though have have your periods entered as 5, 4, 3, 2, 1, for the purposes of processing, 5 is the First row, 4 is the Second row, 3 is the Third row, 2 is the Fourth row and 1 is the Fifth row and they are always processed in that order. Is this true?
Logic rule #1 makes sense and the data matches. To calculate the Logic value for Period #5, take Qty_In and subtract the sum of Qty_Out for Periods 1 through 5. (155  (54 + 61 + 18 + 30 + 50) = 58 Logic) and (150  (44 + 66 + 32 + 46 + 58) = 96 Logic).
Logic rule #2. To calculate the Logic for Period #4, 3, 2, and 1, take Qty_In and and IF the Previous Period Logic value is negative, add it to the current Period's Qty_In ELSE set the Logic = the Qty_In. In your example data Period #4 Qty_In and Period#5 Logic values would be added (since the Logic value is negative in both cases). (345 + 58 = 287 Logic) and (354 + 96 = 287 Logic). But you have 258 as the Logic value, so the first group works but the second group doesn't (unless it is a typo or my understanding of the rule is wrong).
For Periods #3, 2, and 1, the Previous Period's Logic value are all Positive so the Logic is set to the same value as the Qty_In. (215 and 488 for Period #3, 185 and 254 for Period #2, and 531 and 418 for Period #1).
For Rem, the rule is simple, If Logic value is negative, the the value is 0 Else, the value is the same as the Logic value.
Is your question / situation that you have the values for Itemid, Period, Qty_In, and Qty_Out and you want to calculate the Logic and Rem values for all rows based on the rules for Logic and Rem?

070910, 23:42 #5Registered User
 Join Date
 Jul 2010
 Posts
 8
Thanks for the reply...
Yes, there is always a 5 periods per item.
The order of process you have mentioned is true. 5 as the 1st, 4 as 2nd and so on.
Yes, youre logic #1 and #2 interpretation is correct and you are right that the result is based on the rules for logic and rem.
I have this data...
select '4718460001111' itemid, 5 period, 155 qty_in, 54 qty_out union all
select '4718460001111' itemid, 4 period, 345 qty_in, 61 qty_out union all
select '4718460001111' itemid, 3 period, 215 qty_in, 18 qty_out union all
select '4718460001111' itemid, 2 period, 185 qty_in, 30 qty_out union all
select '4718460001111' itemid, 1 period, 531 qty_in, 50 qty_out union all
select '5011321835337' itemid, 5 period, 150 qty_in, 44 qty_out union all
select '5011321835337' itemid, 4 period, 354 qty_in, 66 qty_out union all
select '5011321835337' itemid, 3 period, 488 qty_in, 32 qty_out union all
select '5011321835337' itemid, 2 period, 254 qty_in, 46 qty_out union all
select '5011321835337' itemid, 1 period, 418 qty_in, 58 qty_out
and this is the result I want...
select '4718460001111' itemid, 5 period, 155 qty_in, 54 qty_out, 58 logic, 0 rem union all
select '4718460001111' itemid, 4 period, 345 qty_in, 61 qty_out, 287 logic, 287 rem union all
select '4718460001111' itemid, 3 period, 215 qty_in, 18 qty_out, 215 logic, 215 rem union all
select '4718460001111' itemid, 2 period, 185 qty_in, 30 qty_out, 185 logic, 185 rem union all
select '4718460001111' itemid, 1 period, 531 qty_in, 50 qty_out, 531 logic, 531 rem union all
select '5011321835337' itemid, 5 period, 150 qty_in, 44 qty_out, 96 logic, 0 rem union all
select '5011321835337' itemid, 4 period, 354 qty_in, 66 qty_out, 258 logic, 258 rem union all
select '5011321835337' itemid, 3 period, 488 qty_in, 32 qty_out, 488 logic, 488 rem union all
select '5011321835337' itemid, 2 period, 254 qty_in, 46 qty_out, 254 logic, 254 rem union all
select '5011321835337' itemid, 1 period, 418 qty_in, 58 qty_out, 418 logic, 418 rem

070910, 23:50 #6Registered User
 Join Date
 Jul 2010
 Posts
 8
for the 2nd group with itemid 5011321835337...
(354qty_in for the 4th period + 96logic from the previous period(5th) is equal to 258 Logic) and not 287 as what you have said. The 1st and 2nd works as it is.Last edited by jonasalbert; 071010 at 00:04.

071010, 10:12 #7Registered User
 Join Date
 May 2009
 Posts
 509
Provided Answers: 1jonasalbert, Sorry about my mistake on the calculation. My eyes must have been crossing figuring out the logic.
Here is a solution. It is the first one that come to mind. I am sure there are others. It doesn't use any temp tables but it does use a Common Table Expression to create the pivoted table and calculate the Logic values. I developed this with DB2 but all the commands should work in SQL Server 2005 and above.
First, I pivoted the data to have all of one item on one row. This makes it easier to calculate the Logic values.
Code:WITH TESTTAB (ITEMID, PERIOD, QTY_IN, QTY_OUT) AS (SELECT '4718460001111', 5, 155, 54 UNION ALL SELECT '4718460001111', 4, 345, 61 UNION ALL SELECT '4718460001111', 3, 215, 18 UNION ALL SELECT '4718460001111', 2, 185, 30 UNION ALL SELECT '4718460001111', 1, 531, 50 UNION ALL SELECT '5011321835337', 5, 150, 44 UNION ALL SELECT '5011321835337', 4, 354, 66 UNION ALL SELECT '5011321835337', 3, 488, 32 UNION ALL SELECT '5011321835337', 2, 254, 46 UNION ALL SELECT '5011321835337', 1, 418, 58 ) , WORKTAB ( ITEMID , QTY_IN_5 , QTY_IN_4 , QTY_IN_3 , QTY_IN_2 , QTY_IN_1, QTY_OUT_5, QTY_OUT_4, QTY_OUT_3, QTY_OUT_2, QTY_OUT_1, SUM_QTY_OUT, LOGIC_5) AS (SELECT ITEMID , SUM(CASE PERIOD WHEN 5 THEN QTY_IN ELSE 0 END) , SUM(CASE PERIOD WHEN 4 THEN QTY_IN ELSE 0 END) , SUM(CASE PERIOD WHEN 3 THEN QTY_IN ELSE 0 END) , SUM(CASE PERIOD WHEN 2 THEN QTY_IN ELSE 0 END) , SUM(CASE PERIOD WHEN 1 THEN QTY_IN ELSE 0 END) , SUM(CASE PERIOD WHEN 5 THEN QTY_OUT ELSE 0 END) , SUM(CASE PERIOD WHEN 4 THEN QTY_OUT ELSE 0 END) , SUM(CASE PERIOD WHEN 3 THEN QTY_OUT ELSE 0 END) , SUM(CASE PERIOD WHEN 2 THEN QTY_OUT ELSE 0 END) , SUM(CASE PERIOD WHEN 1 THEN QTY_OUT ELSE 0 END) , SUM(QTY_OUT) , MAX(CASE PERIOD WHEN 5 THEN QTY_IN ELSE 0 END)  SUM(QTY_OUT) FROM TESTTAB GROUP BY ITEMID ) , CALCTAB (ITEMID, LOGIC_5, LOGIC_4, LOGIC_3, LOGIC_2, LOGIC_1) AS (SELECT ITEMID , LOGIC_5 , QTY_IN_4 + (CASE WHEN LOGIC_5 < 0 THEN LOGIC_5 ELSE 0 END ) AS LOGIC_4 , QTY_IN_3 + (CASE WHEN QTY_IN_4 + (CASE WHEN LOGIC_5 < 0 THEN LOGIC_5 ELSE 0 END) < 0 THEN QTY_IN_4 + (CASE WHEN LOGIC_5 < 0 THEN LOGIC_5 ELSE 0 END) ELSE 0 END ) AS LOGIC_3 , QTY_IN_2 + (CASE WHEN QTY_IN_3 + (CASE WHEN QTY_IN_4 + (CASE WHEN LOGIC_5 < 0 THEN LOGIC_5 ELSE 0 END) < 0 THEN QTY_IN_4 + (CASE WHEN LOGIC_5 < 0 THEN LOGIC_5 ELSE 0 END) ELSE 0 END ) < 0 THEN QTY_IN_3 + (CASE WHEN QTY_IN_4 + (CASE WHEN LOGIC_5 < 0 THEN LOGIC_5 ELSE 0 END) < 0 THEN QTY_IN_4 + (CASE WHEN LOGIC_5 < 0 THEN LOGIC_5 ELSE 0 END) ELSE 0 END) ELSE 0 END ) AS LOGIC_2 , QTY_IN_1 + (CASE WHEN QTY_IN_2 + (CASE WHEN QTY_IN_3 + (CASE WHEN QTY_IN_4 + (CASE WHEN LOGIC_5 < 0 THEN LOGIC_5 ELSE 0 END) < 0 THEN QTY_IN_4 + (CASE WHEN LOGIC_5 < 0 THEN LOGIC_5 ELSE 0 END) ELSE 0 END) < 0 THEN QTY_IN_3 + (CASE WHEN QTY_IN_4 + (CASE WHEN LOGIC_5 < 0 THEN LOGIC_5 ELSE 0 END) < 0 THEN QTY_IN_4 + (CASE WHEN LOGIC_5 < 0 THEN LOGIC_5 ELSE 0 END) ELSE 0 END) ELSE 0 END) < 0 THEN QTY_IN_2 + (CASE WHEN QTY_IN_3 + (CASE WHEN QTY_IN_4 + (CASE WHEN LOGIC_5 < 0 THEN LOGIC_5 ELSE 0 END) < 0 THEN QTY_IN_4 + (CASE WHEN LOGIC_5 < 0 THEN LOGIC_5 ELSE 0 END) ELSE 0 END) < 0 THEN QTY_IN_3 + (CASE WHEN QTY_IN_4 + (CASE WHEN LOGIC_5 < 0 THEN LOGIC_5 ELSE 0 END) < 0 THEN QTY_IN_4 + (CASE WHEN LOGIC_5 < 0 THEN LOGIC_5 ELSE 0 END) ELSE 0 END) ELSE 0 END) ELSE 0 END ) AS LOGIC_1 FROM WORKTAB ) SELECT TESTTAB.ITEMID, TESTTAB.PERIOD, TESTTAB.QTY_IN, TESTTAB.QTY_OUT , CASE TESTTAB.PERIOD WHEN 5 THEN CALCTAB.LOGIC_5 WHEN 4 THEN CALCTAB.LOGIC_4 WHEN 3 THEN CALCTAB.LOGIC_3 WHEN 2 THEN CALCTAB.LOGIC_2 WHEN 1 THEN CALCTAB.LOGIC_1 END AS LOGIC , CASE WHEN TESTTAB.PERIOD = 5 AND CALCTAB.LOGIC_5 > 0 THEN LOGIC_5 WHEN TESTTAB.PERIOD = 4 AND CALCTAB.LOGIC_4 > 0 THEN LOGIC_4 WHEN TESTTAB.PERIOD = 3 AND CALCTAB.LOGIC_3 > 0 THEN LOGIC_3 WHEN TESTTAB.PERIOD = 2 AND CALCTAB.LOGIC_2 > 0 THEN LOGIC_2 WHEN TESTTAB.PERIOD = 1 AND CALCTAB.LOGIC_1 > 0 THEN LOGIC_1 ELSE 0 END AS REM FROM TESTTAB INNER JOIN CALCTAB ON TESTTAB.ITEMID = CALCTAB.ITEMID ORDER BY TESTTAB.ITEMID, TESTTAB.PERIOD DESC ;
Code:ITEMID PERIOD QTY_IN QTY_OUT LOGIC REM       4718460001111 5 155 54 58 0 4718460001111 4 345 61 287 287 4718460001111 3 215 18 215 215 4718460001111 2 185 30 185 185 4718460001111 1 531 50 531 531 5011321835337 5 150 44 96 0 5011321835337 4 354 66 258 258 5011321835337 3 488 32 488 488 5011321835337 2 254 46 254 254 5011321835337 1 418 58 418 418

071010, 10:43 #8Registered User
 Join Date
 Jul 2010
 Posts
 8
Geeeee... the pivot of data will trim down the lines. thanks for that. However, I will have a problem later if the requirement for number of periods will change. Coz, periods will increase sooner.
Thank you for the temporary solution. Is it possible to modify ur approach to cater the period issue?

071010, 14:41 #9World Class Flame Warrior
 Join Date
 Jun 2003
 Location
 Ohio
 Posts
 12,595
Provided Answers: 1Geeeeee.....
Jonas, do you understand how this sort of stuff is going to make people very reluctant to spend their spare time helping you out with your problem for free?
Stealth wasted a LOT of effort on the basis of your rather curt dismisal of Pat's inquiry. I think you owe them both an apology, and much more consideration, if you expect further assistance.If it's not practically useful, then it's practically useless.
blindman
www.chess.com: "sqlblindman"
www.LobsterShot.blogspot.com

071010, 16:56 #10Registered User
 Join Date
 Jul 2010
 Posts
 8
blindman, I don't think I said something that a member will not like. Seems I was misinterpreted with my reaction on my last post. I'm sorry for that. What I'm trying to express is that, I was enlighten with Stealth_DBA pivot of data approach. I don't know why you react just like that suddenly.

071010, 19:54 #11World Class Flame Warrior
 Join Date
 Jun 2003
 Location
 Ohio
 Posts
 12,595
Provided Answers: 1Because, and I'll try to make this simple for you....
You ignored Pat's request for more information.
Then you made a definitive statement about your requirements which was incorrect.
Then, after someone spent considerable time working on your problem, you inform him that your previous requirements were incorrect, and ask him to work on the problem all over again.
I'm not sure what internet you have been surfing for the past 10 years, but on a free advice forum that is considered pretty damn rude, inconsiderate, and aggravating.
I, and probably Pat, labeled you "2S2H" quite a while ago. If you are lucky, Stealth DBA may have some patience left. And I repeat: you owe him a HUGE apology.If it's not practically useful, then it's practically useless.
blindman
www.chess.com: "sqlblindman"
www.LobsterShot.blogspot.com

071010, 22:39 #12Registered User
 Join Date
 Jul 2010
 Posts
 8
Dude... You have problem on you're interpretation between our dialogue. What makes now rude, inconsiderate, and aggravating is you're reaction. It would be quite educated if you inform me thru PM first before you react just like this suddenly. Nevertheless, I will answer you and this is my last post just to prevent the discussion to heatup and out of topic.
"what I want is the logic and rem column. The requirement is specified."  I don't think I ignored him. I answered him directly.
Yes, I made a definitive statement but I NEVER NEVER said INCORRECT or WRONG.
"However, I will have a problem later if the requirement for number of periods will change. Coz, periods will increase sooner. "  This is just a followup statement.
"the pivot of data will trim down the lines. thanks for that."  In fact, I thank Stealth_DBA for the approach and gives me an idea for another solution. You misquoted this and have you're interpretation afterwards.
I never said WRONG or INCORRECT or same words of the same meaning. You just have you're own interpretation. In fact, I said thank you for the solution provided. Did I? or you mis read it?
What makes it rude, inconsiderate and aggravating is this statement itself. You judged the book by its cover.
"blindman, I don't think I said something that a member will not like. Seems I was misinterpreted with my reaction on my last post. I'm sorry for that."  I already made an appology. By the way next time, please don't use jejemon phrase. If you are not able to express it literally, PM me instead.

071110, 11:21 #13World Class Flame Warrior
 Join Date
 Jun 2003
 Location
 Ohio
 Posts
 12,595
Provided Answers: 1No. You ignored him. He asked for more details so that he did not end up wasting his time, and you declined to supply any.
...and it turned out your definitive statement WAS wrong.
There is no denying the fact that you refused to give additional information when requested and you knowingly gave false requirements. Your statement that "Yes, there is always a 5 periods per item." was, in fact, incorrect.
So the first person who tried to help you by requesting further details got blown off.
The second person who tried to help you wasted considerable time because you refused to answer the first person's question.
You have wasted the time of two professionals so far, and yet you feel no need to apologize for that. Pretty damn rude.If it's not practically useful, then it's practically useless.
blindman
www.chess.com: "sqlblindman"
www.LobsterShot.blogspot.com