We're using SQL Server 2008 and we're trying to extract data from our financial system. We've been asked to generate a list of stock balances and the age of the stock for each day for a date range.
Desired to have stock balances - our financial package holds stock transactions in a table, but does not hold any balances. The only balance held for an item is in another table and this is the stock for today, so we have to add back all the transactions to get to the true figure for the day. I've been able to do this using a cursor, but I am confused on how to add the balance for each and every day. For example, if there was no stock movement for 7th July, then the 7th July gets ignored. The cursor does seem to take a lot of time, so not sure if it can be improved - it also gives the opening balance for the day, not the closing balance.
Age of the stock - our stock comes in as one lump sum and decreases (never increases until a new group comes in) and the date of manufacture is stored in another table. I have been asked to grab the average age for the stock for each day and to then be summarised monthly.
Table structures - Each table can be connected via an ItemCode, Warehouse and/or date.
My cursor that lists the stock per warehouse and the quantities:
DECLARE @min_dt date = (SELECT MIN(CDate) FROM [Kinross_Other].[dbo].[KFT-Calendar]) ,@max_dt date = '20151030' ;WITH cte AS (SELECT @min_dt AS dt UNION ALL SELECT DATEADD(DAY,1,dt) FROM cte WHERE dt < @max_dt) ,cte2 AS (SELECT a.dt , ItmLog.ItemCode , ItmLog.ItemName , ItmLog.LocCode , (ItmLog.StockQty) As StockQty , ItmQty.OnHand FROM cte a LEFT JOIN [SBO_KF_Live].[dbo].[OITL] ItmLog RIGHT JOIN [Kinross_Other].[dbo].[KFT-Calendar] Cal ON Cal.CDate = ItmLog.DocDate INNER JOIN [SBO_KF_Live].[dbo].[OITW] ItmQty ON ItmLog.ItemCode = ItmQty.ItemCode and ItmLog.LocCode = ItmQty.WhsCode ON a.dt BETWEEN ItmLog.DocDate AND ISNULL(ItmLog.DocDate,'2099-12-31') WHERE ItmLog.ItemCode = '10092') SELECT dt , ItemCode , ItemName , LocCode , (SELECT sum(b.StockQty) FROM cte2 b WHERE a.LocCode = b.LocCode AND b.dt = a.dt) As Qty , (SELECT avg(OnHand) - sum(c.StockQty) FROM cte2 c WHERE a.LocCode = c.LocCode AND c.dt >= a.dt) As OBal FROM cte2 a GROUP BY a.dt, ItemCode, ItemName, LocCode ORDER BY a.LocCode, dt OPTION (MAXRECURSION 0);
A sample of the results:
Date ItemCode ItemName Whse Qty Balance
2015-07-01 10092 Pullets AD1 -1.000000 12962.000000
2015-07-05 10092 Pullets AD1 -4.000000 12961.000000
2015-07-08 10092 Pullets AD1 -3.000000 12957.000000
Any help would be greatly appreciated. I am open to the ideas of it not all being done in one hit and that a table can be made and then updated or something else.