# Thread: FIFO query with applied logic

1. Registered 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.

2. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
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

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

4. Registered User
Join Date
May 2009
Posts
509
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. Registered User
Join Date
Jul 2010
Posts
8

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

7. Registered User
Join Date
May 2009
Posts
509
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. Registered 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?

9. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595
Geeeeee.....

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

10. Registered User
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. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595
Because, and I'll try to make this simple for you....

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.

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

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

"what I want is the logic and rem column. The requirement is specified." -- I don't think I ignored him. I answered him directly.

Originally Posted by blindman
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.

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

Originally Posted by blindman
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.

Originally Posted by blindman
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. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595
Originally Posted by jonasalbert
"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.

Originally Posted by jonasalbert
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.

#### Posting Permissions

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