September 12, 2013 at 11:41 pm
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 2100000332013-09-11 85
2 IT002 10 1000 PURCHASE 2100000332013-09-11 85
3 IT001 5 200 SALES 1100000192013-09-11 86
4 IT001 5 200 PURCHASE 2100000342013-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.
September 13, 2013 at 3:42 pm
Have a look at this thread from the MSDN forums, where I answer a similar question. Hopefully it should get you started.
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.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
September 16, 2013 at 1:26 am
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.
September 16, 2013 at 4:12 pm
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
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
September 25, 2013 at 10:53 pm
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply