FIFO Inventory Report - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

FIFO Inventory Report

Author

Topic

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2010-11-23 : 04:24:27

Hi All,
I have a report to show based on the FIFO method for inventory.
Below is the sample data


ID	Type	Value
1	IN	2450
2	IN	4625
3	IN	1485
4	OUT	2000
5	OUT	3000
6	IN	1250

and desired output is


ID	Type	Value	PrevBalance	UsedOutID	UsedQty	Balance
1	in	2450	0	4	2000	450
1	in	450	450	5	450	0
2	in	4625	0	5	2550	2075
3	in	1485	2075	0	0	3560
6	in	1250	3560	0	0	4810

feel free to ask any doubts...
Thanks in advance

"There is only one difference between a dream and an aim.
A dream requires soundless sleep to see,
whereas an aim requires sleepless efforts to achieve..!!"

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-11-23 : 04:50:08

The Type in last record (#6), is that supposed to be "IN" instead of "INT"? If not then what does it mean?

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2010-11-23 : 04:57:43

sorry typo mistake, corrected now.

quote:
Originally posted by Lumbago

The Type in last record (#6), is that supposed to be "IN" instead of "INT"? If not then what does it mean?

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

"There is only one difference between a dream and an aim.
A dream requires soundless sleep to see,
whereas an aim requires sleepless efforts to achieve..!!"

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-23 : 04:59:20

Will the tpe in the output ever be anything other than IN?

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2010-11-23 : 05:00:32

it will contain only "IN" and "OUT" nothing else.

quote:
Originally posted by nigelrivett

Will the tpe in the output ever be anything other than IN?

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

"There is only one difference between a dream and an aim.
A dream requires soundless sleep to see,
whereas an aim requires sleepless efforts to achieve..!!"

TimSman
Posting Yak Master

127 Posts

Posted - 2010-11-23 : 08:49:03

If I could make a suggestion to the table structure...

I would include the IN record ID for each OUT record. That way you know where it came from. Yes, I understand FIFO rules should make that unnecessary, but it makes it a lot easier when you have to span multiple INs on one OUT, and when you start dealing with multiple products, locations, etc.

Plus, makes reporting a far sight easier, as well as returns.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-11-23 : 09:09:19

See winning example here
http://ask.sqlservercentral.com/questions/826/the-fifo-stock-inventory-sql-problem

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-11-23 : 09:27:48

Is this a correct interpretation?

ID	Type	Value
1	IN	2450
2	IN	4625
3	IN	1485
4	OUT	2000
5	OUT	3000
6	IN	1250



	    IN	            OUT

1	*--------*  4	*--------*
	|        |	|        |
	|        |	|        |
	|        |	|        |
	|        |	*--------*
	|        |  5	*--------*	
	*--------*	|        |
2	*--------*	|        |
	|        |	|        |
	|        |	|        |
	|        |	|        |
	|        |	|        |
	|        |	*--------*
	|        |	
	|        |
	|        |
	|        |
	*--------*
3	*--------*
	|        |
	|        |
	|        |
	*--------*
6	*--------*
	|        |
	|        |
	*--------*

InID	Value	OutID	UsedQty	PrevBalance	Balance
1	2450	4	2000  	   0		  450
1	2450	5	450	 450		    0
2	4625	5	2550	   0		 2075
3	1485			2075		 3560
6	1250			3560		 4810

N 56°04'39.26"
E 12°55'05.63"

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-11-23 : 09:40:23

I spent a good part of my day trying to figure this one out but gave in after not being able to get my head around it. Most of it looks correct according to the specifications but as far as I can see the Value is off...

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2010-11-24 : 05:34:10

Hi Peso,

U r interpretation of the problem is Correct. The link given by u only gives the final balance not the detail report as in my requirement.

quote:

Originally posted by Peso

Is this a correct interpretation?

ID	Type	Value
1	IN	2450
2	IN	4625
3	IN	1485
4	OUT	2000
5	OUT	3000
6	IN	1250



	    IN	            OUT

1	*--------*  4	*--------*
	|        |	|        |
	|        |	|        |
	|        |	|        |
	|        |	*--------*
	|        |  5	*--------*	
	*--------*	|        |
2	*--------*	|        |
	|        |	|        |
	|        |	|        |
	|        |	|        |
	|        |	|        |
	|        |	*--------*
	|        |	
	|        |
	|        |
	|        |
	*--------*
3	*--------*
	|        |
	|        |
	|        |
	*--------*
6	*--------*
	|        |
	|        |
	*--------*

InID	Value	OutID	UsedQty	PrevBalance	Balance
1	2450	4	2000  	   0		  450
1	2450	5	450	 450		    0
2	4625	5	2550	   0		 2075
3	1485			2075		 3560
6	1250			3560		 4810

N 56°04'39.26"
E 12°55'05.63"

"There is only one difference between a dream and an aim.
A dream requires soundless sleep to see,
whereas an aim requires sleepless efforts to achieve..!!"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources