SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Query to show Items in FIFO method.


Query to show Items in FIFO method.

Author
Message
kishorefeb28
kishorefeb28
Valued Member
Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)

Group: General Forum Members
Points: 53 Visits: 96
Dear Experts,

my client needs a report which shows inventory data.
he executes this report before every sale and checks the Item availability.
we should show the report in FIFO method.

Sl.No. Itemcode Qty Price DocType DocNum Date Trans.No.
(Varchar(10)) (Numeric) Numeric(19,6) Varchar(10) Varchar(10) (Date) (integer)
1 IT001 10 100 PURCHASE 210000033 2013-09-11 85
2 IT002 10 1000 PURCHASE 210000033 2013-09-11 85
3 IT001 5 200 SALES 110000019 2013-09-11 86
4 IT001 5 200 PURCHASE 210000034 2013-09-11 87

here in the above table i have data.

if user executes the report, it should show report as below from the data above.

-------------------------------------------------
Sl.No Itemcode Available Qty. Price
-------------------------------------------------
1 IT001 5 100 ------5 Qty is sold as shown in above table at line 3.
2 IT002 10 1000
3 IT001 5 200 ------5 Qty is Purchased at line 3. it is shown in different line ------------------------------------------------- because different price (FIFO method).

I thought of using self Join the first table....but ended up with wrong data.
Help appreciated in this regard.
Thank you
Kishore.
Erland Sommarskog
Erland Sommarskog
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2113 Visits: 872
Have a look at this thread from the MSDN forums, where I answer a similar question. Hopefully it should get you started.

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/36b98d06-fe73-4849-ba68-c8a2110afba0/how-can-i-divide-transaction-on-fifo-basis

If that does help you, please post your table definition and sample data in SQL format, that is, as CREATE TABLE and INSERT statements. And please specify which version of SQL Server you are using.

Erland Sommarskog, SQL Server MVP, www.sommarskog.se
kishorefeb28
kishorefeb28
Valued Member
Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)

Group: General Forum Members
Points: 53 Visits: 96
Dear Erland,

Thanks a lot for your Reply and the link provided, but it seems it will not satisfy my client requirement.

please find the table definition and sample details as you requested:


Create Table WHS_Details (ItemCode VarChar(20),
Quantity numeric(19, 6),
Price numeric(19, 6),
DocType VarChar(10),
DocNum VarChar(12),
DocDate Date ,
TransID Int)

Insert into WHS_Details values ('IT001',10,100,'PURCHASE',210000033,'2013-09-11',85)
Insert into WHS_Details values ('IT002',10,1000,'PURCHASE',210000033,'2013-09-11',85)
Insert into WHS_Details values ('IT001',5,200,'SALES',110000019,'2013-09-11',86)
Insert into WHS_Details values ('IT001',5,200,'PURCHASE',210000034,'2013-09-11',87)

My Sql Version is MSSQLSERVER 2008 R2

While sale, user executes the report to know the available quantity of each item and purchase cost.

suppose if he bought 10 quantity of item1 for 100 rupees
and sold 2 of them (for any price it doesn't matter)
again bought 5 quantity of item1 for 200 rupees.

if user executes report now, it should show available qty based on purchase price as below :

item1 8qty(10-2 sold) 100(purchase price) 2013-09-11 (Date of purchase)
item1 10qty 200(Purchase price) 2013-09-11 (Date of purchase)

please get back to me if any queries.

Thanks in advance
Kishore.
Erland Sommarskog
Erland Sommarskog
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2113 Visits: 872
Too bad that you are not on SQL 2012 or later, that makes things simpler.

The embarrassing part is that I work a lot with FIFO calculation at work, but not for inventory but for profit/loss of stock trading - which is a lot more difficult than inventories.

I'm running out of time, and can only offer a half-finished solution. You need the quantity after each transaction. Best this should be added to the table from the start, but I've added a cursor to compute that. (You need a cursor for an efficient solution on SQL 2008.) Then there is another cursor to match the final positions, but this is unfinsihed.

I've made some assumptions about keys. Please verify these.

Create Table WHS_Details (ItemCode VarChar(20),
Quantity numeric(19, 6),
Price numeric(19, 6),
DocType VarChar(10),
DocNum VarChar(12),
DocDate Date ,
TransID Int)

Insert into WHS_Details values ('IT001',10,100,'PURCHASE',210000033,'2013-09-11',85)
Insert into WHS_Details values ('IT002',10,1000,'PURCHASE',210000033,'2013-09-11',85)
Insert into WHS_Details values ('IT001',5,200,'SALES',110000019,'2013-09-11',86)
Insert into WHS_Details values ('IT001',5,200,'PURCHASE',210000034,'2013-09-11',87)

Insert into WHS_Details values ('IT003',10,100,'PURCHASE',210000033,'2013-09-11',185)
Insert into WHS_Details values ('IT003',10,1000,'PURCHASE',210000033,'2013-09-11',186)
Insert into WHS_Details values ('IT003',21,200,'SALES',110000019,'2013-09-11',187)
Insert into WHS_Details values ('IT003',5,200,'PURCHASE',210000034,'2013-09-11',188)


CREATE TABLE #runningqty (ItemCode varchar(20) NOT NULL,
Qty numeric(19,6) NOT NULL,
TransID int NOT NULL,
BatchNo int NOT NULL,
TotQty numeric(19,6) NULL,
MatchedQty numeric(19,6) NOT NULL DEFAULT 0,
UsedQty numeric(19,6) NOT NULL DEFAULT 0,
PRIMARY KEY NONCLUSTERED (ItemCode, TransID),
UNIQUE CLUSTERED (BatchNo, ItemCode, TransID)
)

INSERT #runningqty(ItemCode, TransID, Qty, BatchNo)
SELECT ItemCode, TransID,
Quantity * CASE DocType WHEN 'PURCHASE' THEN 1 WHEN 'SALES' THEN -1 END,
row_number() OVER (PARTITION BY ItemCode ORDER BY TransID)
FROM WHS_Details

UPDATE #runningqty
SET TotQty = Qty
WHERE BatchNo = 1

DECLARE cur CURSOR STATIC LOCAL FOR
SELECT DISTINCT BatchNo FROM #runningqty WHERE BatchNo > 1

OPEN cur

DECLARE @BatchNo int

WHILE 1 = 1
BEGIN
FETCH cur INTO @BatchNo
IF @@fetch_status <> 0
BREAK

UPDATE this
SET TotQty = this.Qty + prev.TotQty
FROM #runningqty this
JOIN #runningqty prev ON this.ItemCode = prev.ItemCode
WHERE this.BatchNo = @BatchNo
AND prev.BatchNo = @BatchNo - 1
END

DEALLOCATE cur

SELECT * FROM #runningqty ORDER BY ItemCode, TransID

;WITH reversenumbering AS (
SELECT BatchNo,
row_number() OVER (PARTITION BY ItemCode ORDER BY TransID DESC) AS NewBatchNo
FROM #runningqty
)
UPDATE reversenumbering
SET BatchNo = NewBatchNo

DECLARE cur2 CURSOR STATIC LOCAL FOR
SELECT DISTINCT BatchNo FROM #runningqty

OPEN cur2

WHILE 1 = 1
BEGIN
FETCH cur2 INTO @BatchNo
IF @@fetch_status <> 0
BREAK

-- this is the unfinished part
UPDATE this
SET MatchedQty = CASE WHEN this.Qty < 0 THEN 0
????
END
FROM #runningqty this
LEFT JOIN #runningqty next ON this.ItemCode = next.ItemCode
AND next.BatchNo = @BatchNo - 1
JOIN #runningqty last ON this.ItemCode = last.ItemCode
AND last.BatchNo = 1
WHERE this.BatchNo = @BatchNo
END

DEALLOCATE cur2

go
DROP TABLE WHS_Details
DROP TABLE #runningqty



Erland Sommarskog, SQL Server MVP, www.sommarskog.se
parulprabu
parulprabu
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1482 Visits: 136
This query might help you


Create Table WHS_Details (ItemCode VarChar(20),
Quantity numeric(19, 6),
Price numeric(19, 6),
DocType VarChar(10),
DocNum VarChar(12),
DocDate Date ,
TransID Int)

Insert into WHS_Details values ('IT001',10,100,'PURCHASE',210000033,'2013-09-11',85)
Insert into WHS_Details values ('IT002',10,1000,'PURCHASE',210000033,'2013-09-11',85)
Insert into WHS_Details values ('IT001',6,200,'SALES',110000019,'2013-09-12',86)
Insert into WHS_Details values ('IT001',5,200,'PURCHASE',210000034,'2013-09-13',87)
Insert into WHS_Details values ('IT001',5,200,'SALES',110000019,'2013-09-14',88)

select ItemCode, Price, DocDate, SUM(Quantity) as Quantity into #availQty from WHS_Details
where DocType ='PURCHASE'
group by ItemCode, Price, DocDate
order by DocDate

alter table #availQty ADD id int identity(1,1)
declare @ItemCode VarChar(20),@Quantity numeric(19, 6), @id int, @availQty numeric(19, 6)

declare soldqty cursor for select ItemCode,Quantity from WHS_Details where DocType='SALES'
open soldqty
fetch soldQty into @ItemCode, @Quantity
while @@FETCH_STATUS = 0
begin
select top 1 @id=id, @availQty = Quantity from #availQty where ItemCode = @ItemCode
order by DocDate
if (@availQty < @Quantity)
begin
delete from #availQty where id=@id
set @Quantity = @Quantity - @availQty
end
select top 1 @id=id, @availQty = Quantity from #availQty where ItemCode = @ItemCode
order by DocDate
update #availQty set Quantity = Quantity - @Quantity where id=@id
fetch soldQty into @ItemCode, @Quantity
end
close soldqty
deallocate soldqty

select * from #availQty

drop table #availQty
drop table WHS_Details


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search