Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Query to show Items in FIFO method. Expand / Collapse
Author
Message
Posted Thursday, September 12, 2013 11:41 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, March 13, 2014 3:18 AM
Points: 27, 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.
Post #1494444
Posted Friday, September 13, 2013 3:42 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: 2 days ago @ 4:43 AM
Points: 756, Visits: 631
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
Post #1494752
Posted Monday, September 16, 2013 1:26 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, March 13, 2014 3:18 AM
Points: 27, 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.



Post #1494948
Posted Monday, September 16, 2013 4:12 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: 2 days ago @ 4:43 AM
Points: 756, Visits: 631
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
Post #1495319
Posted Wednesday, September 25, 2013 10:53 PM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, December 18, 2013 5:03 AM
Points: 1,454, Visits: 135
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

Post #1498666
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse