Results 1 to 13 of 13
  1. #1
    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.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You 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.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Jul 2010
    Posts
    8
    what I want is the logic and rem column. The requirement is specified.

  4. #4
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    jonasalbert, While you may know your process and data 'inside-out', 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?

  5. #5
    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

  6. #6
    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; 07-10-10 at 01:04.

  7. #7
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    jonasalbert, 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

  8. #8
    Join Date
    Jul 2010
    Posts
    8

    Thumbs up

    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?

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Geeeeee.....

    Quote Originally Posted by Pat Phelan View Post
    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.
    -PatP
    Quote Originally Posted by jonasalbert View Post
    what I want is the logic and rem column. The requirement is specified.
    Quote Originally Posted by jonasalbert View Post
    Yes, there is always a 5 periods per item.
    Quote Originally Posted by jonasalbert View Post
    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?
    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

  10. #10
    Join Date
    Jul 2010
    Posts
    8
    blindman, I don't think I said something that a member will not like. Seems I was mis-interpreted 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.

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Because, 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

  12. #12
    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 heat-up and out of topic.


    Quote Originally Posted by blindman View Post
    Because, and I'll try to make this simple for you....

    You ignored Pat's request for more information.
    "what I want is the logic and rem column. The requirement is specified." -- I don't think I ignored him. I answered him directly.

    Quote Originally Posted by blindman View Post
    Then you made a definitive statement about your requirements which was incorrect.
    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 follow-up 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 mis-quoted this and have you're interpretation afterwards.

    Quote Originally Posted by blindman View Post
    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 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?


    Quote Originally Posted by blindman View Post
    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.
    What makes it rude, inconsiderate and aggravating is this statement itself. You judged the book by its cover.

    Quote Originally Posted by blindman View Post
    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.
    "blindman, I don't think I said something that a member will not like. Seems I was mis-interpreted 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.

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by jonasalbert View Post
    "what I want is the logic and rem column. The requirement is specified." -- I don't think I ignored him. I answered him directly.
    No. You ignored him. He asked for more details so that he did not end up wasting his time, and you declined to supply any.


    Quote Originally Posted by jonasalbert View Post
    Yes, I made a definitive statement but I NEVER NEVER said INCORRECT or WRONG.
    ...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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •