FIFO Method - 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 2000 Forums

Transact-SQL (2000)

FIFO Method

Author

Topic

Pieter
Starting Member

5 Posts

Posted - 2007-07-08 : 23:18:22

Dear guys,

Sory for bad english languages.
I have a case in accounting like this below
Result 1:
PartNo Qty InvDate
A 2 16/03/2007
A 2 17/03/2007
A 5 18/03/2007

If user pick PartNo A=3, so my t-sql want to need result like this below :

Result 2:
PartNo Qty InvDate
A 2 16/03/2007
A 1 17/03/2007

Like that, I have a scenario FIFO method. First, pick from InvDate smaller date from other InvDate.

I have a syntax sql to detected the result that ( result 2 ), but i'm still confusing when user pick other PartNo. If user want to pick PartNo=4, so the result should be like this :

Result 3:
PartNo Qty InvDate
A 1 17/03/2007 ==> Because in InvDate A still 1 Qty remaining
A 2 18/03/2007 ==> Because in InvDate A still 5 Qty remaining

So, in like that scenario, how i can implement this t-sql method?

Please advise me.I have need more deep learning from this forums,
This forum more good learning like me(beginner) ;-).

Best Regards,

Pieter
Starting Member

5 Posts

Posted - 2007-07-08 : 23:38:13

Sory guys... something wrong to this Result of Result 3.
If User want to Pick PartNo A=4, so the result should be like this :

Mistake.....
Result 3:
PartNo Qty InvDate
A 1 17/03/2007 ==> Because in InvDate A still 1 Qty remaining
A 2 18/03/2007 ==> Because in InvDate A still 5 Qty remaining

True Result ....
Result 3:
PartNo Qty InvDate
A 1 17/03/2007 ==> Because in InvDate A still 1 Qty remaining ( 2-1 ) still 1
A 3 18/03/2007 ==> Because in InvDate A still 5 Qty remaining ( 5-4 ) still 3

And the scenario continues result. So, In InvDate=18/03/2007, A still have remaining Qty=2, because
3 have been pick from result 3.

Please, advise me and need help you guys.Especially from Peso.

Thanks again,

Best Regards,

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-09 : 00:21:22

quote:
Especially from Peso.

Wow ! only 3 posts and you have heard of the Saint.

Peter has a solution something very similar to your requirement for Maya_Zakry. Try to search for it.

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-09 : 00:28:53

try these threads see if helps.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=79505
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=82143
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=80857

If not, ask Saint Peter nicely

KH
[spoiler]Time is always against us[/spoiler]

Pieter
Starting Member

5 Posts

Posted - 2007-07-09 : 01:22:53

Thanks khtan for your replies.

Of course...in your relation link, i have learn that from Peso what have same topics with me.But, I'm still confusing about case.

The result of 2 Idea from Saint Peter also. It's nice solutions. But with my case in result 3, how I can Implements them.

This is of Saint Peter trick, that I have Implement SQL

The T-SQL :

-- Initialize search parameter
DECLARE @WantedValue int,
@Sum int,
@WantedPartNo varchar(30),
@RangeDate datetime

SELECT @WantedValue = 15,
@Sum = @WantedValue,
@WantedPartNo='A'

-- Stage the data
DECLARE @Stage TABLE (RecID INT IDENTITY(1, 1), PartNo VARCHAR(30), Qty int, InvDate DATETIME)

DECLARE curStage CURSOR FORWARD_ONLY READ_ONLY FOR
SELECT PartNo,
Qty,
InvDate
FROM MyTable
WHERE PartNo=@WantedPartNo
ORDER BY PartNo, InvDate ASC,
Qty DESC

DECLARE @PartNo varchar(30),
@Qty int,
@InvDate DATETIME

OPEN curStage

FETCH NEXT
FROM curStage
INTO @PartNo,
@Qty,
@InvDate

WHILE @@FETCH_STATUS = 0 AND @Sum > 0
BEGIN
SET @Sum = @Sum - @Qty

INSERT @Stage (PartNo, Qty, InvDate)
SELECT @PartNo,
@Qty,
@InvDate

FETCH NEXT
FROM curStage
INTO @PartNo,
@Qty,
@InvDate
END

CLOSE curStage
DEALLOCATE curStage

IF @Sum <= 0
UPDATE @Stage
SET Qty = Qty + @Sum
WHERE RecID = @@IDENTITY

ELSE
SELECT 'No oversum found'

-- Show the result
SELECT *
FROM @Stage
ORDER BY RecID

==================================================

In this case is okay, but when User want to pick other Item for Qty=4

The result should like this :

PartNo Qty InvDate
A 1 17/03/2007 ==> Because in InvDate A still 1 Qty remaining ( 2-1 ) still 1
A 3 18/03/2007 ==> Because in InvDate A still 5 Qty remaining ( 5-4 ) still 3

And, until now I still Confusing, how I saved that in InvDate=17/03/2007 have still 1 Item, because in InvDate=16/03/2007 for PartNo A have Qty=0.

For illustration like below:

PartNo Qty ==> Pick Qty=3 PartNo Qty ==> Balance : PartNo Qty ==> Pick Qty=4
A 2 A 2 A 0
A 2 A 1 A 1
A 5 A 5

PartNo Qty ==> Balance : PartNo Qty
A 0 A 0
A 1 A 0
A 3 A 2

And continues for more pick items.

How I can should this scenario. Please advise me.

Thanks

Pieter
Starting Member

5 Posts

Posted - 2007-07-09 : 03:49:13

Dear All Guys...

Sory for this FIFO Method. When I try some trick, I have been success to make this FIFO method clear.

So, I'm trick is ...

As same as Peso Trick, but I have 1 table to insert different quantity out ( pick ). I'm make name table T_CRD

This tabel have field : Idx ( identity), PartNo, Qty, InvDate

So, When User need pick qty, I will insert to this table.

In Peso t-sql, I just simple trick to read them like below :

-- Initialize search parameter
DECLARE @WantedValue int,
@Sum int,
@WantedPartNo varchar(30),
@RangeDate datetime

SELECT @WantedValue = 5,
@Sum = @WantedValue,
@WantedPartNo='A'

-- Stage the data
DECLARE @Stage TABLE (RecID INT IDENTITY(1, 1), PartNo VARCHAR(30), Qty int, InvDate DATETIME)

DECLARE curStage CURSOR FORWARD_ONLY READ_ONLY FOR

'=== This is My Trick ====
'=== Summary them qty ( In-Out), For Tabel In and Tabel Out Order them === what partno has been InvDate first date.

SELECT PartNo, Qty=SUM(Qty), InvDate
FROM
( SELECT PartNo, Qty, InvDate
FROM
(

SELECT TOP 100 PartNo,
Qty,
InvDate
FROM T_INCD
WHERE PartNo=@WantedPartNo
ORDER BY PartNo, InvDate ASC,
Qty DESC

) A

UNION ALL

SELECT TOP 100 PartNo, Qty=-Qty, InvDate
FROM T_CRD
WHERE PartNo=@WantedPartNo
ORDER BY PartNo, InvDate ASC,
Qty DESC
) A1
GROUP BY PartNo, InvDate
HAVING SUM(Qty)<>0

'=======================

DECLARE @PartNo varchar(30),
@Qty int,
@InvDate DATETIME

OPEN curStage

FETCH NEXT
FROM curStage
INTO @PartNo,
@Qty,
@InvDate

WHILE @@FETCH_STATUS = 0 AND @Sum > 0
BEGIN
SET @Sum = @Sum - @Qty

INSERT @Stage (PartNo, Qty, InvDate)
SELECT @PartNo,
@Qty,
@InvDate

FETCH NEXT
FROM curStage
INTO @PartNo,
@Qty,
@InvDate
END

CLOSE curStage
DEALLOCATE curStage

IF @Sum <= 0
UPDATE @Stage
SET Qty = Qty + @Sum
WHERE RecID = @@IDENTITY

ELSE
SELECT 'No oversum found'

=== ' MY TRICK
==== 'WHEN SUCCESS, PICK THEM RESULT TO T_CRD TABEL

-- Show the result
-- INSERT INTO T_CRD
-- ( RecID, PartNo, Qty, InvDate )
SELECT RecID, PartNo, Qty, InvDate
FROM @Stage
ORDER BY RecID

'===============

Specially thanks again for all your response man. Especially Peso. You r trick very good learning.

Thanks again all

So, this Topics is out and close by me.

Thanks again and regards,

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-23 : 18:06:05

You are welcome!

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources