FIFO Stock Valuation Through CTE-Recursion

Question

question

shreeapplications asked Jun 10, '15

FIFO Stock Valuation Through CTE-Recursion

Dear Sirs/Mams, I have copied it from this site because it's been already closed but I needed it for further solution. thus, kindly help me out..... Problem : it's calculating the closing stock valuation through FIFO of issue as a whole. but i need cost of issues into Price column in the same row it belongs to itself. declare @Stock table (Item char(3) not null,[Date] datetime not null,TxnType varchar(3) not null,Qty int not null,Price decimal(10,2) null) insert into @Stock(Item , [Date] , TxnType, Qty, Price) values ('ABC','20120401','IN', 200, 750.00), ('ABC','20120405','OUT', 100 ,null ), ('ABC','20120410','IN', 50, 700.00), ('ABC','20120416','IN', 75, 800.00), ('ABC','20120425','OUT', 175, null ), ('XYZ','20120402','IN', 150, 350.00), ('XYZ','20120408','OUT', 120 ,null ), ('XYZ','20120412','OUT', 10 ,null ), ('XYZ','20120424','IN', 90, 340.00); ;WITH OrderedIn as ( select *,ROW_NUMBER() OVER (PARTITION BY Item ORDER BY [DATE]) as rn from @Stock where TxnType = 'IN' ), RunningTotals as ( select Item,Qty,Price,Qty as Total,0 as PrevTotal,rn from OrderedIn where rn = 1 union all select rt.Item,oi.Qty,oi.Price,rt.Total + oi.Qty,rt.Total,oi.rn from RunningTotals rt inner join OrderedIn oi on rt.Item = oi.Item and rt.rn = oi.rn - 1 ), TotalOut as ( select Item,SUM(Qty) as Qty from @Stock where TxnType='OUT' group by Item ) select rt.Item,SUM(CASE WHEN PrevTotal > out.Qty THEN rt.Qty ELSE rt.Total - out.Qty END * Price) from RunningTotals rt inner join TotalOut out on rt.Item = out.Item where rt.Total > out.Qty group by rt.Item The result is only Closing Stock as per FIFO Basis as below: Item ClsStock ABC 40000.00 XYZ 37600.00 But I need the followings Result from the same query: Item Date TxnType OpnQty OpnRate OpnVal InnQty InnRate InnVal OutQty OutRate OutVal ClsQty ClsRate ClsVal ABC 20120401 IN 200 750 150000 200 750.00 150000 ABC 20120405 OUT 200 750.00 150000 100 750 75000 100 750.00 75000 ABC 20120410 IN 100 750.00 75000 50 700 35000 150 733.33 110000 ABC 20120416 IN 150 733.33 110000 75 800 60000 225 755.56 170000 ABC 20120425 OUT 225 755.56 170000 175 742.86 130000 50 800.00 40000 Total 0 0.00 0 325 753.85 245000 275 745.45 205000 50 800.00 40000 XYZ 20120402 IN 150 350 52500 150 350.00 52500 XYZ 20120408 OUT 150 350.00 52500 120 350 42000 30 350.00 10500 XYZ 20120412 OUT 30 350.00 10500 10 350 3500 20 350.00 7000 XYZ 20120424 IN 20 350.00 7000 90 340 30600 110 341.82 37600 Total 0 0.00 0 240 346.25 83100 130 350 45500 110 341.82 37600 Grand Total 0 0 0 565 580.71 328100 405 618.52 250500 160 485.00 77600

sql server 2012 cte

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2015-06-14T05:59:40Z

sabinweb answered Jun 14, '15

and for Grand total , add another UNION ALL, to last stmt UNION ALL SELECT 'Grand Total ' ,MAX([Date]),0,'I/O' ,0,0,0 ,SUM(InQty),SUM(InVal)/NULLIF(SUM(InQty),0), SUM(InVal) ,SUM(OutQty), SUM(OutVal)/NULLIF(SUM(OutQty),0) ,SUM(OutVal) ,SUM(InQty)-SUM(OutQty) ,(SUM(InVal)-SUM(OutVal))/NULLIF((SUM(InQty)-SUM(OutQty)),0) ,SUM(InVal)-SUM(OutVal) FROM cteR One comment : Total and Grand Total should be calculated in application layer.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 2 · 2015-06-12T09:16:15Z

sabinweb answered Jun 12, '15

I have something close to it, if you like to take a look. declare @Stock table (Item char(3) not null ,[Date] date not null ,TxnType varchar(3) not null ,Qty int not null ,Price decimal(10,2) null) insert into @Stock(Item , [Date] , TxnType, Qty, Price) values ('ABC','20120401','IN', 200, 750.00), ('ABC','20120405','OUT', 100 ,null ), ('ABC','20120410','IN', 50, 700.00), ('ABC','20120416','IN', 75, 800.00), ('ABC','20120425','OUT', 175, null ) , ('XYZ','20120402','IN', 150, 350.00), ('XYZ','20120408','OUT', 120 ,null ), ('XYZ','20120412','OUT', 10 ,null ), ('XYZ','20120424','IN', 90, 340.00); ;WITH ItemDate AS( SELECT CS.Item ,CS.[Date] ,CS.Price ,ROW_NUMBER()OVER (PARTITION BY CS.Item ORDER BY CS.[Date]) AS RN ,CASE WHEN CS.TxnType = 'IN' THEN CS.Qty ELSE 0 END AS cIn ,CASE WHEN CS.TxnType = 'OUT' THEN CS.Qty ELSE 0 END AS cOut FROM @stock AS CS ) --SELECT * FROM ItemDate order by Item, [Date] ,cteR AS( SELECT Item ,[Date] , RN ,Price ,CAST(0 AS INT) as OpnQty , CAST(0 AS DECIMAL(10,2)) as OpnVal ,cIn as InQty, CAST(cIn*Price AS DECIMAL(10,2)) AS InVal ,0 as OutQty , CAST(0 AS DECIMAL(10,2)) as OutVal ,cIn as ClsQty, CAST(cIn*Price AS DECIMAL(10,2)) AS ClsVal FROM ItemDate WHERE RN = 1 UNION ALL SELECT ID.Item, ID.[Date] , ID.RN ,ID.Price ,R.ClsQty , R.ClsVal ,ID.cIn , CAST(ISNULL(ID.cIn * ID.Price,0) AS DECIMAL(10,2)) ,ID.cOut, CAST(ID.cOut * (ISNULL((R.ClsVal/NULLIF(R.ClsQty,0)),0)) AS DECIMAL(10,2)) ,R.ClsQty + ID.cIn - ID.cOut ,CAST(R.ClsVal + ISNULL(ID.cIn * ID.Price,0) - ID.cOut * (ISNULL((R.ClsVal/NULLIF(R.ClsQty,0)),0)) AS DECIMAL(10,2)) FROM ItemDate as ID INNER JOIN cteR as R ON ID.Item = R.Item AND ID.RN - 1 = R.RN ) --SELECT * FROM cteR SELECT CASE WHEN GROUPING(Item) = 0 AND GROUPING([Date]) = 1 THEN 'Total: ' + Item WHEN GROUPING(Item) = 1 AND GROUPING([Date]) = 1 THEN 'Grand Total: ' ELSE Item END AS Item ,[Date],Price ,SUM(OpnQty) AS OpnQty, SUM(CAST(OpnVal/NULLIF(OpnQty,0) AS DECIMAL(10,2))) AS OpnRate , SUM(OpnVal) AS OpnVal ,SUM(NULLIF(InQty,0)) AS InQty, SUM(CAST(InVal/NULLIF(InQty,0) AS DECIMAL(10,2))) AS InRate ,SUM(NULLIF(InVal,0)) as InVal ,SUM(NULLIF(OutQty,0)) AS OutQty , SUM(CAST(OutVal/NULLIF(OutQty,0) AS DECIMAL(10,2))) AS OutRate ,SUM(NULLIF(OutVal,0)) as OutVal ,SUM(NULLIF(ClsQty,0)) AS ClsQty , SUM(CAST(ClsVal/NULLIF(ClsQty,0) AS DECIMAL(10,2))) AS ClsRate , SUM(NULLIF(ClsVal,0)) as ClsVal FROM cteR GROUP BY GROUPING SETS ((Item,[Date],Price),(Item),()) --ORDER BY Item , [Date]

2

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

shreeapplications commented · Jun 13, 2015 at 09:58 AM

Dear Sir, your answer is very near to the solution except followings: 1) Values should be calculated first and then dividing by qty, rate should be resulted like for 5th row of ABC's Outqty=175: OutValue=100*750+50*700+25*800=130000 OutRate=130000/175=742.86 2) ABC: Total Calculation for example: OpnQty=1st row of ABC OpnQty=0 OpnVal=1st row of ABC OpnVal=0 OpnRate=1st row of ABC OpnRate=0 InnQty=Sum(InnQty)=325 InnVal=Sum(InnVal)=245000 InnRate=245000/325=753.85 OutQty=Sum(OutQty)=275 OutVal=Sum(OutVal)=205000 OutRate=205000/275=745.45 ClsQty=0+325-275=50 ClsVal=0+245000-205000=40000 ClsRate=40000/50=800 3) Grand Total: Calculation like (2) Above adding all items. Regards- Sanjeeb

0 ·

shreeapplications commented · Jun 13, 2015 at 10:02 AM

sir, also request you to solve my one more question in the below link: https://ask.sqlservercentral.com/questions/121210/add-new-row-after-every-loanno-change-in-sql-query.html

0 ·

Answer 3 · 2015-06-13T14:02:21Z

sabinweb answered Jun 13, '15

First , I'm not sure , if I can obtain your output . So, o create this script, that has 2 parts: - first part is creating a table that hold the Out quantity with the correspondent In qty - second, is the select So here it is : declare @Stock table (Item char(3) not null ,[Date] date not null ,TxnType varchar(3) not null ,Qty int not null ,Price decimal(10,2) null) insert into @Stock(Item , [Date] , TxnType, Qty, Price) values ('ABC','20120401','IN', 200, 750.00), ('ABC','20120405','OUT', 100 ,null ), ('ABC','20120410','IN', 50, 700.00), ('ABC','20120416','IN', 75, 800.00), ('ABC','20120425','OUT', 175, null ) /*, ('XYZ','20120402','IN', 150, 350.00), ('XYZ','20120408','OUT', 120 ,null ), ('XYZ','20120412','OUT', 10 ,null ), ('XYZ','20120424','IN', 90, 340.00);*/ declare @itemDate Table(Item char(3) not null ,[Date] date not null ,Price decimal(10,2) null ,TxnType varchar(3) not null ,QtyIn int not null ,QtyOut int not null ,RN int not null ,RN_Item int not null ) Insert into @itemDate(item,[date],price,TxnType,QtyIn,QtyOut,RN,RN_Item) SELECT CS.Item ,CS.[Date] ,CS.Price ,CS.TxnType ,CASE WHEN CS.TxnType = 'IN' THEN CS.Qty ELSE 0 END ,CASE WHEN CS.TxnType = 'OUT' THEN CS.Qty ELSE 0 END ,ROW_NUMBER()OVER (/*PARTITION BY CS.Item */ ORDER BY item ,CS.[Date]) ,ROW_NUMBER()OVER (PARTITION BY CS.Item ORDER BY item , CS.[Date]) FROM @stock AS CS --SELECT * FROM @ItemDate order by Item, [Date] DECLARE @i INT = 1 ,@iMaxRN AS INT = (SELECT MAX(RN) FROM @itemDate) ,@vcItem CHAR(3) ,@iQtyOut INT ,@dPrice DECIMAL(18,2) ,@vcType VARCHAR(3) ,@iStocLevel INT ,@iQ_New INT ,@iRN INT -- holds the new qtyr with ref to IN qty ID DECLARE @tOUTPUT TABLE (rowID INT IDENTITY(1,1) , Qty INT , RN_Out INT , RN_in INT) WHILE @i<= @iMaxRN BEGIN SELECT @vcItem = item ,@iQtyOut = QtyOut ,@vcType = TxnType FROM @itemDate WHERE RN = @i IF @vcType = 'OUT' BEGIN WHILE(1=1) BEGIN SELECT TOP (1) @iStocLevel = ID.QtyIn - ISNULL(OU.Qty,0) ,@iRN = ID.RN FROM @itemDate AS ID LEFT JOIN (SELECT SUM(Qty) AS Qty,RN_in FROM @tOutput GROUP BY RN_in ) AS OU ON ID.RN = OU.RN_in WHERE ID.TxnType ='IN' AND ID.Item = @vcItem AND ID.QtyIn - ISNULL(OU.Qty,0) > 0 ORDER BY ID.[Date] ASC IF @iStocLevel >= @iQtyOut BEGIN INSERT INTO @tOUTPUT(Qty,RN_out,RN_in) VALUES(@iQtyOut,@i,@iRN) BREAK END ELSE BEGIN INSERT INTO @tOUTPUT(Qty,RN_out,RN_in) VALUES(@iStocLevel,@i,@iRN) SET @iQtyOut = @iQtyOut - @iStocLevel END END END -- end IF @vcType ='OUT' SET @i= @i + 1 END --@i<= @iMaxRN ;WITH ItemDate AS ( SELECT ID.Item ,ID.[Date] ,ISNULL(ID.Price,OP.Price) AS Price ,ROW_NUMBER()OVER (PARTITION BY ID.Item ORDER BY ID.[Date]) AS RN ,ID.QtyIn as cIn ,ISNULL(OU.Qty,0) AS cOut ,ID.QtyOut AS OriginalQtyOut FROM @itemDate AS ID LEFT JOIN @tOUTPUT AS OU ON ID.RN = OU.RN_out LEFT JOIN @itemDate AS OP ON OU.RN_in = OP.RN ) --SELECT * FROM itemDate ,cteR AS ( SELECT ID.Item,ID.Date ,ID.RN ,ID.Price ,CAST(0 AS INT) as OpnQty , CAST(0 AS DECIMAL(10,2)) as OpnVal ,cIn as InQty, CAST(cIn*Price AS DECIMAL(10,2)) AS InVal ,0 as OutQty , CAST(0 AS DECIMAL(10,2)) as OutVal ,cIn as ClsQty, CAST(cIn*Price AS DECIMAL(10,2)) AS ClsVal FROM itemDate AS ID WHERE ID.RN = 1 UNION ALL SELECT ID.Item, ID.[Date] , ID.RN ,ID.Price ,R.ClsQty , R.ClsVal ,ID.cIn , CAST(ISNULL(ID.cIn * ID.Price,0) AS DECIMAL(10,2)) ,ID.cOut, CAST(ISNULL(ID.cOut * ID.Price,0) AS DECIMAL(10,2)) ,R.ClsQty + ID.cIn - ID.cOut ,CAST(R.ClsVal + ISNULL(ID.cIn * ID.Price,0) - ISNULL(ID.cOut * ID.Price,0) AS DECIMAL(10,2)) FROM ItemDate as ID INNER JOIN cteR as R ON ID.Item = R.Item AND ID.RN - 1 = R.RN )--SELECT * FROM cteR SELECT CASE WHEN GROUPING(Item) = 0 AND GROUPING([Date]) = 1 THEN 'Total: ' + Item WHEN GROUPING(Item) = 1 AND GROUPING([Date]) = 1 THEN 'Grand Total: ' ELSE Item END AS Item ,[Date],Price ,SUM(OpnQty) AS OpnQty, SUM(CAST(OpnVal/NULLIF(OpnQty,0) AS DECIMAL(10,2))) AS OpnRate , SUM(OpnVal) AS OpnVal ,SUM(NULLIF(InQty,0)) AS InQty, SUM(CAST(InVal/NULLIF(InQty,0) AS DECIMAL(10,2))) AS InRate ,SUM(NULLIF(InVal,0)) as InVal ,SUM(NULLIF(OutQty,0)) AS OutQty , SUM(CAST(OutVal/NULLIF(OutQty,0) AS DECIMAL(10,2))) AS OutRate ,SUM(NULLIF(OutVal,0)) as OutVal ,SUM(NULLIF(ClsQty,0)) AS ClsQty , SUM(CAST(ClsVal/NULLIF(ClsQty,0) AS DECIMAL(10,2))) AS ClsRate , SUM(NULLIF(ClsVal,0)) as ClsVal FROM cteR GROUP BY GROUPING SETS ((Item,[Date],Price),(Item),())

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

shreeapplications commented · Jun 14, 2015 at 03:52 AM

Dear Respected Sir, You have almost solved my problem except a little bit changes to be done as follows: 1) you have evaluated the OutVal Separately in three rows. It should be calculated all in a single row as example given above because it issued by a single transaction. 2) calculations for XYZ item is missing due to which grand total could not found meaningful. Thus, kindly request you to consider the same and help me as soon as possible. With Warm Regards- Sanjeeb

0 ·

Answer 4 · 2015-06-14T05:56:41Z

sabinweb answered Jun 14, '15

declare @Stock table (Item char(3) not null ,[Date] date not null ,TxnType varchar(3) not null ,Qty int not null ,Price decimal(10,2) null) insert into @Stock(Item , [Date] , TxnType, Qty, Price) values ('ABC','20120401','IN', 200, 750.00), ('ABC','20120405','OUT', 100 ,null ), ('ABC','20120410','IN', 50, 700.00), ('ABC','20120416','IN', 75, 800.00), ('ABC','20120425','OUT', 175, null ) , ('XYZ','20120402','IN', 150, 350.00), ('XYZ','20120408','OUT', 120 ,null ), ('XYZ','20120412','OUT', 10 ,null ), ('XYZ','20120424','IN', 90, 340.00); declare @itemDate Table(Item char(3) not null ,[Date] date not null ,Price decimal(10,2) null ,TxnType varchar(3) not null ,QtyIn int not null ,QtyOut int not null ,RN int not null ,RN_Item int not null ) Insert into @itemDate(item,[date],price,TxnType,QtyIn,QtyOut,RN,RN_Item) SELECT CS.Item ,CS.[Date] ,CS.Price ,CS.TxnType ,CASE WHEN CS.TxnType = 'IN' THEN CS.Qty ELSE 0 END ,CASE WHEN CS.TxnType = 'OUT' THEN CS.Qty ELSE 0 END ,ROW_NUMBER()OVER (/*PARTITION BY CS.Item */ ORDER BY item ,CS.[Date]) ,ROW_NUMBER()OVER (PARTITION BY CS.Item ORDER BY item , CS.[Date]) FROM @stock AS CS --SELECT * FROM @ItemDate order by Item, [Date] DECLARE @i INT = 1 ,@iMaxRN AS INT = (SELECT MAX(RN) FROM @itemDate) ,@vcItem CHAR(3) ,@iQtyOut INT ,@dPrice DECIMAL(18,2) ,@vcType VARCHAR(3) ,@iStocLevel INT ,@iQ_New INT ,@iRN INT -- holds the new qtyr with ref to IN qty ID DECLARE @tOUTPUT TABLE (rowID INT IDENTITY(1,1) , Qty INT , RN_Out INT , RN_in INT) WHILE @i<= @iMaxRN BEGIN SELECT @vcItem = item ,@iQtyOut = QtyOut ,@vcType = TxnType FROM @itemDate WHERE RN = @i IF @vcType = 'OUT' BEGIN WHILE(1=1) BEGIN SELECT TOP (1) @iStocLevel = ID.QtyIn - ISNULL(OU.Qty,0) ,@iRN = ID.RN FROM @itemDate AS ID LEFT JOIN (SELECT SUM(Qty) AS Qty,RN_in FROM @tOutput GROUP BY RN_in ) AS OU ON ID.RN = OU.RN_in WHERE ID.TxnType ='IN' AND ID.Item = @vcItem AND ID.QtyIn - ISNULL(OU.Qty,0) > 0 ORDER BY ID.[Date] ASC IF @iStocLevel >= @iQtyOut BEGIN INSERT INTO @tOUTPUT(Qty,RN_out,RN_in) VALUES(@iQtyOut,@i,@iRN) BREAK END ELSE BEGIN INSERT INTO @tOUTPUT(Qty,RN_out,RN_in) VALUES(@iStocLevel,@i,@iRN) SET @iQtyOut = @iQtyOut - @iStocLevel END END END -- end IF @vcType ='OUT' SET @i= @i + 1 END --@i<= @iMaxRN ;WITH ItemDateSum AS ( SELECT ID.Item ,ID.[Date] --,AVG(ISNULL(ID.Price,OP.Price)) AS Price --,ROW_NUMBER()OVER (PARTITION BY ID.Item ORDER BY ID.[Date]) AS RN ,SUM(ID.QtyIn) as cIn ,SUM(ID.QtyIn*ISNULL(ID.Price,OP.Price)) as ValIn ,SUM(ID.QtyIn*ISNULL(ID.Price,OP.Price))/NULLIF(SUM(ID.QtyIn),0) as PriceIn ,SUM(ISNULL(OU.Qty,0)) AS cOut ,SUM(ISNULL(OU.Qty,0)*ISNULL(ID.Price,OP.Price)) AS ValOut ,SUM(ISNULL(OU.Qty,0)*ISNULL(ID.Price,OP.Price)) /NULLIF(SUM(ISNULL(OU.Qty,0)),0) as PriceOut ,ID.QtyOut AS OriginalQtyOut ,ID.TxnType FROM @itemDate AS ID LEFT JOIN @tOUTPUT AS OU ON ID.RN = OU.RN_out LEFT JOIN @itemDate AS OP ON OU.RN_in = OP.RN GROUP BY ID.Item ,ID.[Date],ID.QtyOut,ID.TxnType ) ,itemDate AS( SELECT Item,[Date] ,ROW_NUMBER()OVER (PARTITION BY ID.Item ORDER BY ID.[Date]) AS RN ,ID.TxnType ,cIn,ValIn,PriceIn ,cOut,ValOut,PriceOut FROM itemDateSum AS ID ) --select * from ItemDate ,cteR AS ( SELECT ID.Item,ID.Date ,ID.RN ,ID.PriceIn as Price,ID.TxnType ,CAST(0 AS INT) as OpnQty , CAST(0 AS DECIMAL(10,2)) as OpnVal ,cIn as InQty, CAST(cIn*PriceIn AS DECIMAL(10,2)) AS InVal ,0 as OutQty , CAST(0 AS DECIMAL(10,2)) as OutVal ,cIn as ClsQty, CAST(cIn*PriceIn AS DECIMAL(10,2)) AS ClsVal FROM itemDate AS ID WHERE ID.RN = 1 UNION ALL SELECT ID.Item, ID.[Date] , ID.RN ,ID.PriceIn,ID.TxnType ,R.ClsQty , R.ClsVal ,ID.cIn , CAST(ISNULL(ID.cIn * ID.PriceIn,0) AS DECIMAL(10,2)) ,ID.cOut, CAST(ISNULL(ID.cOut * ID.PriceOut,0) AS DECIMAL(10,2)) ,R.ClsQty + ID.cIn - ID.cOut ,CAST(R.ClsVal + ISNULL(ID.cIn * ID.PriceIn,0) - ISNULL(ID.cOut * ID.PriceOut,0) AS DECIMAL(10,2)) FROM ItemDate as ID INNER JOIN cteR as R ON ID.Item = R.Item AND ID.RN - 1 = R.RN ) --SELECT * FROM cteR SELECT Item AS Item ,[Date],Price,TxnType ,(OpnQty) AS OpnQty, (CAST(OpnVal/NULLIF(OpnQty,0) AS DECIMAL(10,2))) AS OpnRate , (OpnVal) AS OpnVal ,(NULLIF(InQty,0)) AS InQty, (CAST(InVal/NULLIF(InQty,0) AS DECIMAL(10,2))) AS InRate ,(NULLIF(InVal,0)) as InVal ,(NULLIF(OutQty,0)) AS OutQty , (CAST(OutVal/NULLIF(OutQty,0) AS DECIMAL(10,2))) AS OutRate ,(NULLIF(OutVal,0)) as OutVal ,(NULLIF(ClsQty,0)) AS ClsQty , (CAST(ClsVal/NULLIF(ClsQty,0) AS DECIMAL(10,2))) AS ClsRate , (NULLIF(ClsVal,0)) as ClsVal FROM cteR UNION ALL SELECT 'Total ' + item ,MAX([Date]),0,'I/O' ,0,0,0 ,SUM(InQty),SUM(InVal)/NULLIF(SUM(InQty),0), SUM(InVal) ,SUM(OutQty), SUM(OutVal)/NULLIF(SUM(OutQty),0) ,SUM(OutVal) ,SUM(InQty)-SUM(OutQty) ,(SUM(InVal)-SUM(OutVal))/NULLIF((SUM(InQty)-SUM(OutQty)),0) ,SUM(InVal)-SUM(OutVal) FROM cteR GROUP BY item

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 5 · 2015-06-14T09:41:02Z

shreeapplications answered Jun 14, '15

Respected Sir, The answer you provided is superb. there is nothing to modify further in the same except a simple query. 1)you have fixed the opening values as 0,0,0 for total & grand total, what will be the code there if there exists the opening values>0 ? heartily thankx a lot sir. it's incredible. also humbly request you to suggest the solution of my another question in link below for which I am in search for months: https://ask.sqlservercentral.com/questions/121210/add-new-row-after-every-loanno-change-in-sql-query.html With Heartily Regards- Sanjeeb

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 6 · 2015-06-14T16:04:03Z

sabinweb answered Jun 14, '15

> 1)you have fixed the opening values as 0,0,0 for total & grand total, what will be the code there if there exists the opening values>0 ? Regarding this, I put 0,0,0 because I am not sure about it ,about the logic of it.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 7 · 2015-06-15T04:17:05Z

shreeapplications answered Jun 15, '15

Respected Sir, Suppose you have inserted the above data to a temp table. and you are asked to provide the summary of both items from date:20120403 to 20120424: In that case: Calculations will be as follows: 1) ABC Item: Opening= Closing from 20120401(1 row<=0120403 i.e. FromDate)=100@750.00=75000 In=sum(in from 0120403 to 20120424) Out=sum(out from 0120403 to 20120424) Cls=as usual 2) like ZYZ: 3) Grand Total as usual **Note: sir, if possible include a query to filter from date to date after inserting it to a temporary table to make this answer 100% concluded.** Regards- Sanjeeb

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 8 · 2015-06-16T16:45:31Z

sabinweb answered Jun 16, '15

declare @Stock table (Item char(3) not null ,[Date] date not null ,TxnType varchar(3) not null ,Qty int not null ,Price decimal(10,2) null) insert into @Stock(Item , [Date] , TxnType, Qty, Price) values ('ABC','20120401','IN', 200, 750.00), ('ABC','20120405','OUT', 100 ,null ), ('ABC','20120410','IN', 50, 700.00), ('ABC','20120416','IN', 75, 800.00), ('ABC','20120425','OUT', 175, null ) , ('XYZ','20120402','IN', 150, 350.00), ('XYZ','20120408','OUT', 120 ,null ), ('XYZ','20120412','OUT', 10 ,null ), ('XYZ','20120424','IN', 90, 340.00); declare @itemDate Table(Item char(3) not null ,[Date] date not null ,Price decimal(10,2) null ,TxnType varchar(3) not null ,QtyIn int not null ,QtyOut int not null ,RN int not null ,RN_Item int not null ) Insert into @itemDate(item,[date],price,TxnType,QtyIn,QtyOut,RN,RN_Item) SELECT CS.Item ,CS.[Date] ,CS.Price ,CS.TxnType ,CASE WHEN CS.TxnType = 'IN' THEN CS.Qty ELSE 0 END ,CASE WHEN CS.TxnType = 'OUT' THEN CS.Qty ELSE 0 END ,ROW_NUMBER()OVER (/*PARTITION BY CS.Item */ ORDER BY item ,CS.[Date]) ,ROW_NUMBER()OVER (PARTITION BY CS.Item ORDER BY item , CS.[Date]) FROM @stock AS CS --SELECT * FROM @ItemDate order by Item, [Date] DECLARE @i INT = 1 ,@iMaxRN AS INT = (SELECT MAX(RN) FROM @itemDate) ,@vcItem CHAR(3) ,@iQtyOut INT ,@dPrice DECIMAL(18,2) ,@vcType VARCHAR(3) ,@iStocLevel INT ,@iQ_New INT ,@iRN INT -- holds the new qtyr with ref to IN qty ID DECLARE @tOUTPUT TABLE (rowID INT IDENTITY(1,1) , Qty INT , RN_Out INT , RN_in INT) DECLARE @dtFromDate AS DATETIME = '20120403' ,@dtToDate AS DATETIME = '20120426' WHILE @i<= @iMaxRN BEGIN SELECT @vcItem = item ,@iQtyOut = QtyOut ,@vcType = TxnType FROM @itemDate WHERE RN = @i IF @vcType = 'OUT' BEGIN WHILE(1=1) BEGIN SELECT TOP (1) @iStocLevel = ID.QtyIn - ISNULL(OU.Qty,0) ,@iRN = ID.RN FROM @itemDate AS ID LEFT JOIN (SELECT SUM(Qty) AS Qty,RN_in FROM @tOutput GROUP BY RN_in ) AS OU ON ID.RN = OU.RN_in WHERE ID.TxnType ='IN' AND ID.Item = @vcItem AND ID.QtyIn - ISNULL(OU.Qty,0) > 0 ORDER BY ID.[Date] ASC IF @iStocLevel >= @iQtyOut BEGIN INSERT INTO @tOUTPUT(Qty,RN_out,RN_in) VALUES(@iQtyOut,@i,@iRN) BREAK END ELSE BEGIN INSERT INTO @tOUTPUT(Qty,RN_out,RN_in) VALUES(@iStocLevel,@i,@iRN) SET @iQtyOut = @iQtyOut - @iStocLevel END END END -- end IF @vcType ='OUT' SET @i= @i + 1 END --@i<= @iMaxRN ;WITH ItemDateSum AS ( SELECT ID.Item , CASE WHEN ID.[Date] < @dtFromDate THEN @dtFromDate WHEN ID.[Date]>@dtToDate THEN DATEADD(dd,1,@dtToDate) ELSE ID.[Date] END AS [Date] --,AVG(ISNULL(ID.Price,OP.Price)) AS Price --,ROW_NUMBER()OVER (PARTITION BY ID.Item ORDER BY ID.[Date]) AS RN ,SUM(CASE WHEN ID.[Date] >= @dtFromDate AND ID.[Date] <=@dtToDate THEN ID.QtyIn ELSE 0 END ) as cIn ,SUM(CASE WHEN ID.[Date] >= @dtFromDate AND ID.[Date] <=@dtToDate THEN ID.QtyIn ELSE 0 END *ISNULL(ID.Price,OP.Price)) as ValIn ,SUM(CASE WHEN ID.[Date] >= @dtFromDate AND ID.[Date] <=@dtToDate THEN ID.QtyIn ELSE 0 END *ISNULL(ID.Price,OP.Price))/NULLIF(SUM(ID.QtyIn),0) as PriceIn ,SUM(CASE WHEN ID.[Date] >= @dtFromDate AND ID.[Date] <=@dtToDate THEN ISNULL(OU.Qty,0) ELSE 0 END ) AS cOut ,SUM(CASE WHEN ID.[Date] >= @dtFromDate AND ID.[Date] <=@dtToDate THEN ISNULL(OU.Qty,0) ELSE 0 END *ISNULL(ID.Price,OP.Price)) AS ValOut ,SUM(CASE WHEN ID.[Date] >= @dtFromDate AND ID.[Date] <=@dtToDate THEN ISNULL(OU.Qty,0) ELSE 0 END *ISNULL(ID.Price,OP.Price)) /NULLIF(SUM(CASE WHEN ID.[Date] >= @dtFromDate AND ID.[Date] <=@dtToDate THEN ISNULL(OU.Qty,0) ELSE 0 END ),0) as PriceOut ,CASE WHEN ID.[Date]<@dtFromDate THEN 'OPN' ELSE ID.TxnType END AS TxnType ,SUM(CASE WHEN ID.[Date] < @dtFromDate THEN ID.QtyIn - ISNULL(OU.Qty,0) ELSE 0 END ) as cOpn ,SUM(CASE WHEN ID.[Date] < @dtFromDate THEN ID.QtyIn - ISNULL(OU.Qty,0) ELSE 0 END *ISNULL(ID.Price,OP.Price)) as ValOpn ,SUM(CASE WHEN ID.[Date] < @dtFromDate THEN ID.QtyIn - ISNULL(OU.Qty,0) ELSE 0 END *ISNULL(ID.Price,OP.Price))/NULLIF(SUM(ID.QtyIn - ISNULL(OU.Qty,0)),0) as PriceOpn FROM @itemDate AS ID LEFT JOIN @tOUTPUT AS OU ON ID.RN = OU.RN_out LEFT JOIN @itemDate AS OP ON OU.RN_in = OP.RN WHERE ID.[Date] <= @dtToDate GROUP BY ID.Item ,CASE WHEN ID.[Date] < @dtFromDate THEN @dtFromDate WHEN ID.[Date]> @dtToDate THEN DATEADD(dd,1,@dtToDate) ELSE ID.[Date] END ,CASE WHEN ID.[Date]<@dtFromDate THEN 'OPN' ELSE ID.TxnType END ) --select * from itemDateSum ,itemDate AS( SELECT Item,[Date] ,ROW_NUMBER()OVER (PARTITION BY ID.Item ORDER BY ID.[Date]) AS RN ,ID.TxnType ,cOpn,ValOpn,PriceOpn ,cIn,ValIn,PriceIn ,cOut,ValOut,PriceOut FROM itemDateSum AS ID ) --select * from ItemDate ,cteR AS ( SELECT ID.Item,ID.Date ,ID.RN ,ID.PriceIn as Price,ID.TxnType ,cOpn as OpnQty , CAST(cOpn* PriceOpn AS DECIMAL(10,2)) as OpnVal ,cIn as InQty, CAST(cIn*PriceIn AS DECIMAL(10,2)) AS InVal ,0 as OutQty , CAST(0 AS DECIMAL(10,2)) as OutVal ,CAST(cOpn AS INT)+ cIn as ClsQty , CAST(cIn*PriceIn + cOpn* PriceOpn AS DECIMAL(10,2)) AS ClsVal FROM itemDate AS ID WHERE ID.RN = 1 UNION ALL SELECT ID.Item, ID.[Date] , ID.RN ,ID.PriceIn,ID.TxnType ,R.ClsQty , R.ClsVal ,ID.cIn , CAST(ISNULL(ID.cIn * ID.PriceIn,0) AS DECIMAL(10,2)) ,ID.cOut, CAST(ISNULL(ID.cOut * ID.PriceOut,0) AS DECIMAL(10,2)) ,R.ClsQty + ID.cIn - ID.cOut ,CAST(R.ClsVal + ISNULL(ID.cIn * ID.PriceIn,0) - ISNULL(ID.cOut * ID.PriceOut,0) AS DECIMAL(10,2)) FROM ItemDate as ID INNER JOIN cteR as R ON ID.Item = R.Item AND ID.RN - 1 = R.RN ) --SELECT * FROM cteR ORDER BY Item, [Date] SELECT Item AS Item ,[Date],Price,TxnType ,(OpnQty) AS OpnQty, (CAST(OpnVal/NULLIF(OpnQty,0) AS DECIMAL(10,2))) AS OpnRate , (OpnVal) AS OpnVal ,(NULLIF(InQty,0)) AS InQty, (CAST(InVal/NULLIF(InQty,0) AS DECIMAL(10,2))) AS InRate ,(NULLIF(InVal,0)) as InVal ,(NULLIF(OutQty,0)) AS OutQty , (CAST(OutVal/NULLIF(OutQty,0) AS DECIMAL(10,2))) AS OutRate ,(NULLIF(OutVal,0)) as OutVal ,(NULLIF(ClsQty,0)) AS ClsQty , (CAST(ClsVal/NULLIF(ClsQty,0) AS DECIMAL(10,2))) AS ClsRate , (NULLIF(ClsVal,0)) as ClsVal FROM cteR UNION ALL SELECT item + ' Total:' ,MAX([Date]),0,'I/O' ,SUM(CASE WHEN TxnType ='OPN' THEN OpnQty ELSE 0 END) ,SUM(CASE WHEN TxnType ='OPN' THEN OpnVal ELSE 0 END)/NULLIF(SUM(CASE WHEN TxnType ='OPN' THEN OpnQty ELSE 0 END),0) ,SUM(CASE WHEN TxnType ='OPN' THEN OpnVal ELSE 0 END) ,SUM(InQty),SUM(InVal)/NULLIF(SUM(InQty),0), SUM(InVal) ,SUM(OutQty), SUM(OutVal)/NULLIF(SUM(OutQty),0) ,SUM(OutVal) ,SUM(InQty)-SUM(OutQty) + SUM(CASE WHEN TxnType ='OPN' THEN OpnQty ELSE 0 END) ,(SUM(InVal)-SUM(OutVal)+ SUM(CASE WHEN TxnType ='OPN' THEN OpnVal ELSE 0 END)) /NULLIF((SUM(InQty)-SUM(OutQty) + SUM(CASE WHEN TxnType ='OPN' THEN OpnQty ELSE 0 END)),0) ,SUM(InVal)-SUM(OutVal) + SUM(CASE WHEN TxnType ='OPN' THEN OpnVal ELSE 0 END) FROM cteR GROUP BY item UNION ALL SELECT 'Z Total Grand Total ' ,MAX([Date]),0,'I/O' ,0,0,0 ,SUM(InQty),SUM(InVal)/NULLIF(SUM(InQty),0), SUM(InVal) ,SUM(OutQty), SUM(OutVal)/NULLIF(SUM(OutQty),0) ,SUM(OutVal) ,SUM(InQty)-SUM(OutQty) ,(SUM(InVal)-SUM(OutVal))/NULLIF((SUM(InQty)-SUM(OutQty)),0) ,SUM(InVal)-SUM(OutVal) FROM cteR ORDER BY Item,[Date]

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 9 · 2015-06-17T04:49:30Z

shreeapplications answered Jun 17, '15

Respected Sir, Kindly request you to run the following query i modified a little bit and faced following problems: 1)sir, Opn is not any transaction type that can be fixed on only first row, instead it's every row's opening values that you have calculated. 2)Item-Total & Grand-Total both didn't appeared & even correctly after selecting between two days. kindly request you to suggest the solution after editing following query only not the last one you edited. ------------------------------------------------------------------- IF EXISTS(SELECT * FROM Cat) DROP TABLE Cat declare @Stock table (Item char(3) not null ,[Date] date not null ,TxnType varchar(3) not null ,Qty int not null ,Price decimal(10,2) null) insert into @Stock(Item , [Date] , TxnType, Qty, Price) values ('ABC','20120401','IN', 200, 750.00), ('ABC','20120405','OUT', 100 ,null ), ('ABC','20120410','IN', 50, 700.00), ('ABC','20120416','IN', 75, 800.00), ('ABC','20120425','OUT', 175, null ) ,('XYZ','20120402','IN', 150, 350.00), ('XYZ','20120408','OUT', 120 ,null ), ('XYZ','20120412','OUT', 10 ,null ), ('XYZ','20120424','IN', 90, 340.00); declare @itemDate Table(Item char(3) not null ,[Date] date not null ,Price decimal(10,2) null ,TxnType varchar(3) not null ,QtyIn int not null ,QtyOut int not null ,RN int not null ,RN_Item int not null ) Insert into @itemDate(item,[date],price,TxnType,QtyIn,QtyOut,RN,RN_Item) SELECT CS.Item ,CS.[Date] ,CS.Price ,CS.TxnType ,CASE WHEN CS.TxnType = 'IN' THEN CS.Qty ELSE 0 END ,CASE WHEN CS.TxnType = 'OUT' THEN CS.Qty ELSE 0 END ,ROW_NUMBER()OVER (/*PARTITION BY CS.Item */ ORDER BY item ,CS.[Date]) ,ROW_NUMBER()OVER (PARTITION BY CS.Item ORDER BY item , CS.[Date]) FROM @stock AS CS --SELECT * FROM @ItemDate order by Item, [Date] DECLARE @i INT = 1 ,@iMaxRN AS INT = (SELECT MAX(RN) FROM @itemDate) ,@vcItem CHAR(3) ,@iQtyOut INT ,@dPrice DECIMAL(18,2) ,@vcType VARCHAR(3) ,@iStocLevel INT ,@iQ_New INT ,@iRN INT -- holds the new qtyr with ref to IN qty ID DECLARE @tOUTPUT TABLE (rowID INT IDENTITY(1,1) , Qty INT , RN_Out INT , RN_in INT) WHILE @i<= @iMaxRN BEGIN SELECT @vcItem = item ,@iQtyOut = QtyOut ,@vcType = TxnType FROM @itemDate WHERE RN = @i IF @vcType = 'OUT' BEGIN WHILE(1=1) BEGIN SELECT TOP (1) @iStocLevel = ID.QtyIn - ISNULL(OU.Qty,0) ,@iRN = ID.RN FROM @itemDate AS ID LEFT JOIN (SELECT SUM(Qty) AS Qty,RN_in FROM @tOutput GROUP BY RN_in ) AS OU ON ID.RN = OU.RN_in WHERE ID.TxnType ='IN' AND ID.Item = @vcItem AND ID.QtyIn - ISNULL(OU.Qty,0) > 0 ORDER BY ID.[Date] ASC IF @iStocLevel >= @iQtyOut BEGIN INSERT INTO @tOUTPUT(Qty,RN_out,RN_in) VALUES(@iQtyOut,@i,@iRN) BREAK END ELSE BEGIN INSERT INTO @tOUTPUT(Qty,RN_out,RN_in) VALUES(@iStocLevel,@i,@iRN) SET @iQtyOut = @iQtyOut - @iStocLevel END END END -- end IF @vcType ='OUT' SET @i= @i + 1 END --@i<= @iMaxRN ;WITH ItemDateSum AS ( SELECT ID.Item ,ID.[Date] --,AVG(ISNULL(ID.Price,OP.Price)) AS Price --,ROW_NUMBER()OVER (PARTITION BY ID.Item ORDER BY ID.[Date]) AS RN ,SUM(ID.QtyIn) as cIn ,SUM(ID.QtyIn*ISNULL(ID.Price,OP.Price)) as ValIn ,SUM(ID.QtyIn*ISNULL(ID.Price,OP.Price))/NULLIF(SUM(ID.QtyIn),0) as PriceIn ,SUM(ISNULL(OU.Qty,0)) AS cOut ,SUM(ISNULL(OU.Qty,0)*ISNULL(ID.Price,OP.Price)) AS ValOut ,SUM(ISNULL(OU.Qty,0)*ISNULL(ID.Price,OP.Price)) /NULLIF(SUM(ISNULL(OU.Qty,0)),0) as PriceOut ,ID.QtyOut AS OriginalQtyOut ,ID.TxnType FROM @itemDate AS ID LEFT JOIN @tOUTPUT AS OU ON ID.RN = OU.RN_out LEFT JOIN @itemDate AS OP ON OU.RN_in = OP.RN GROUP BY ID.Item ,ID.[Date],ID.QtyOut,ID.TxnType ) ,itemDate AS( SELECT Item,[Date] ,ROW_NUMBER()OVER (PARTITION BY ID.Item ORDER BY ID.[Date]) AS RN ,ID.TxnType ,cIn,ValIn,PriceIn ,cOut,ValOut,PriceOut FROM itemDateSum AS ID ) --select * from ItemDate ,cteR AS ( SELECT ID.Item,ID.Date ,ID.RN ,ID.PriceIn as Price,ID.TxnType ,CAST(0 AS INT) as OpnQty , CAST(0 AS DECIMAL(10,2)) as OpnVal ,cIn as InQty, CAST(cIn*PriceIn AS DECIMAL(10,2)) AS InVal ,0 as OutQty , CAST(0 AS DECIMAL(10,2)) as OutVal ,cIn as ClsQty, CAST(cIn*PriceIn AS DECIMAL(10,2)) AS ClsVal FROM itemDate AS ID WHERE ID.RN = 1 UNION ALL SELECT ID.Item, ID.[Date] , ID.RN ,ID.PriceIn,ID.TxnType ,R.ClsQty , R.ClsVal ,ID.cIn , CAST(ISNULL(ID.cIn * ID.PriceIn,0) AS DECIMAL(10,2)) ,ID.cOut, CAST(ISNULL(ID.cOut * ID.PriceOut,0) AS DECIMAL(10,2)) ,R.ClsQty + ID.cIn - ID.cOut ,CAST(R.ClsVal + ISNULL(ID.cIn * ID.PriceIn,0) - ISNULL(ID.cOut * ID.PriceOut,0) AS DECIMAL(10,2)) FROM ItemDate as ID INNER JOIN cteR as R ON ID.Item = R.Item AND ID.RN - 1 = R.RN ) SELECT Top 100 Percent * Into Cat FROM ( (SELECT Item AS Item ,[Date],Price,TxnType ,(OpnQty) AS OpnQty, (CAST(OpnVal/NULLIF(OpnQty,0) AS DECIMAL(10,2))) AS OpnRate , (OpnVal) AS OpnVal ,(NULLIF(InQty,0)) AS InQty, (CAST(InVal/NULLIF(InQty,0) AS DECIMAL(10,2))) AS InRate ,(NULLIF(InVal,0)) as InVal ,(NULLIF(OutQty,0)) AS OutQty , (CAST(OutVal/NULLIF(OutQty,0) AS DECIMAL(10,2))) AS OutRate ,(NULLIF(OutVal,0)) as OutVal ,(NULLIF(ClsQty,0)) AS ClsQty , (CAST(ClsVal/NULLIF(ClsQty,0) AS DECIMAL(10,2))) AS ClsRate , (NULLIF(ClsVal,0)) as ClsVal FROM cteR UNION ALL SELECT item +' Total' ,MAX([Date]),0,'I/O' ,0,0,0 ,SUM(InQty),SUM(InVal)/NULLIF(SUM(InQty),0), SUM(InVal) ,SUM(OutQty), SUM(OutVal)/NULLIF(SUM(OutQty),0) ,SUM(OutVal) ,SUM(InQty)-SUM(OutQty) ,(SUM(InVal)-SUM(OutVal))/NULLIF((SUM(InQty)-SUM(OutQty)),0) ,SUM(InVal)-SUM(OutVal) FROM cteR GROUP BY item UNION ALL SELECT 'ZGrand Total ' ,MAX([Date]),0,'I/O' ,0,0,0 ,SUM(InQty),SUM(InVal)/NULLIF(SUM(InQty),0), SUM(InVal) ,SUM(OutQty), SUM(OutVal)/NULLIF(SUM(OutQty),0) ,SUM(OutVal) ,SUM(InQty)-SUM(OutQty) ,(SUM(InVal)-SUM(OutVal))/NULLIF((SUM(InQty)-SUM(OutQty)),0) ,SUM(InVal)-SUM(OutVal) FROM cteR ) ) N Select * from Cat ORDER BY Item, [Date] Select * from Cat Where Date>='2012-04-03' And Date<='2012-04-24' ORDER BY Item, [Date] --------------------------------------------------------------

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 10 · 2015-06-18T07:32:17Z

sabinweb answered Jun 18, '15

I use 'Opn' type transaction , because I need something to distinct between records in period and the one record < @FromDate .This could be done also by a column like 1/0 flag > even correctly ? what column /s ? Is best to add some sample , add an interval , and the output. Like you enter at the begin

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question