Stock Balance Query - Cursors

Transact-SQL
1

Hi all,

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.

  1. 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.

  2. 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.

  3. 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.

Many thanks,
Kinrossfarm

2

Please provide tables definitions as create statements, sample data as insert statements, expected output from your provided sample data.

Also you lost me with the statement "SELECT avg(OnHand) - sum(c.StockQty)". What exactly is this surpossed to do?

To get the stock age, you need to provide table definitions and sample data of the "other tables" you mentioned.

The following query might get you some of the way, but we need more information.

with cte1(dt)
  as (select @min_dt
      union all
      select dateadd(dd,1,dt)
        from cte1
       where dt<@max_dt
     )
    ,cte2(itemcode,itemname,loccode)
  as (select itemcode
            ,itemname
            ,loccode
        from [sbo_kf_live].[dbo].[oitl]
       where docdate>=@min_dt
         and docdate<=@max_dt
         and itemcode='10092'
       group by itemcode
               ,itemname
               ,loccode
     )
select a.dt
      ,b.itemcode
      ,b.itemname
      ,b.loccode
      ,sum(case
              when itmlog.docdate=a.dt
              then itmlog.stockqty
              else 0
           end
          ) as qty
      ,sum(isnull(itmlog.stockqty,0)) as bal
  from cte1 as a
       cross join cte2 as b
       left outer join [sbo_kf_live].[dbo].[oitl] as itmlog
                    on itmlog.docdate=<a.dt
                   and itmlog.itemcode=b.itemcode
                   and itmlog.itemname=b.itemname
                   abd itmlog.loccode=b.loccode
       left outer join [sbo_kf_live].[dbo].[oitw] as itmqty
                    on itmqty.itemcode=itmlog.itemcode
                   and itmqty.whscode=itmlog.loccode
 group by a.dt
         ,b.itemcode
         ,b.itemname
         ,b.loccode
 order by b.loccode
         ,a.dt
;
3

Perhaps I'm missing something in all of this. Is the "Balance" there a Starting Balance or an Ending Balance. It Looks like a starting balance and I believe it should be an ending balance more like something like a checkbook would be. Can you confirm which way you want it?

It would also be real nice to have the DDL for the tables/indexes and some reasonable "readily consumable" test data in the form of INSERT statements to demonstrate with.

4

Hi bitsmed and JeffModen,

Thanks for the quick responses. Apologies for not providing the other table make-up and expected output.

For bitsmed - I was trying to add up the past transactions (the sum part) and deduct it from the Stock On Hand quantity (and I needed to aggregate it for the formula to work - I chose average so it would stand out).

The age of the stock is needing to be calculated by deducting the end date of the query from the Batch Date (ManfDate) and dividing it by 7 for weeks.

For JeffModen - no you didn't, the "Balance" is the opening balance as this seems to be give me every day where as if I do the ending balance, the last day of the report goes to a NULL value.

I'll try to break it down succinctly.

Table 1 - Holds the current stock balances per warehouse

CREATE TABLE #OITW
(
ItemCode nvarchar(20),
WhsCode nvarchar(8), 
OnHand numeric(19,6)
);

INSERT INTO #OITW (ItemCode, WHsCode, OnHand)
VALUES ('10092','AD1','12847');

SELECT * FROM #OITW

Table 2 - holds the stock age information and stock transactions (I managed to find a way to combine then with this output:

CREATE TABLE #Stks
(
ItemCode nvarchar(20),
ItemName nvarchar(100),
DocDate datetime, 
StockQty numeric(19,6),
LocCode nvarchar(8),
MnfDate datetime,
);

INSERT INTO #Stks (ItemCode, ItemName, DocDate, StockQty, LocCode, MnfDate)
VALUES ('10092','Pullets','20150701','-1','AD1','20141117')
	   , ('10092','Pullets','20150705','-4','AD1','20141117')
	   , ('10092','Pullets','20150708','-3','AD1','20141117')
	   , ('10092','Pullets','20150709','-3','AD1','20141117')
	   , ('10092','Pullets','20150711','-1','AD1','20141117')
	   , ('10092','Pullets','20150714','-2','AD1','20141117');

SELECT * FROM #Stks

The expected results would be something like below:

Item Code      Item Name        DocDate                 LocCode          StockQty       Balance     Avg Age
10092          Pullets          2015-07-01 00:00:00     AD1              -1             12961       32.43
10092          Pullets          2015-07-02 00:00:00     AD1              0              12961       32.57
10092          Pullets          2015-07-03 00:00:00     AD1              0              12961       32.71
10092          Pullets          2015-07-04 00:00:00     AD1              0              12961       32.86
10092          Pullets          2015-07-05 00:00:00     AD1              -4             12957       33.00
10092          Pullets          2015-07-06 00:00:00     AD1              0              12957       33.14
....
5

Something like this perhaps:

with cte1(dt)
  as (select @min_dt
      union all
      select dateadd(dd,1,dt)
        from cte1
       where dt<@max_dt
     )
    ,cte2(itemcode,itemname,loccode)
  as (select itemcode
            ,itemname
            ,loccode
        from #stks
       where docdate>=@min_dt
         and docdate<=@max_dt
         and itemcode='10092'
       group by itemcode
               ,itemname
               ,loccode
     )
select b.itemcode
      ,b.itemname
      ,a.dt
      ,b.loccode
      ,sum(case
              when d.docdate=a.dt
              then d.stockqty
              else 0
           end
          ) as qty
      ,avg(c.onhand)-sum(case
                            when d.docdate>a.dt
                            then d.stockqty
                            else 0
                         end
                        )
       as bal
      ,avg((datediff(dd,d.mnfdate,d.docdate)+1)/7.0) as age
  from cte1 as a
       cross join cte2 as b
       left outer join #oitw as c
                    on c.itemcode=b.itemcode
                   and c.whscode=b.loccode
       left outer join #stks as d
                    on d.docdate>=a.dt
                   and d.itemcode=b.itemcode
                   and d.itemname=b.itemname
                   and d.loccode=b.loccode
 group by b.itemcode
         ,b.itemname
         ,a.dt
         ,b.loccode
 order by b.loccode
         ,a.dt
;
1 Like
6

Hi bitsmed,

Thanks for that - that works and very fast too!

I ended up changing the avg((datediff(dd,d.mnfdate,d.docdate)+1)/7.0) As Age to min((datediff(dd,d.mnfdate,d.docdate)+1)/7.0) as Age and that gave me the right age, not sure exactly why the avg is giving a different age but it works.

Just a quick one, I noticed that for the days where the stock has no transactions, it seems to pick up the age of the stock at the next transaction - ie. for the 2nd, 3rd and 4th July, the age of the stock is the same age as the stock at 5th July.

I think this may be due to the datediff function looking at the difference between the docdate and the mnfdate, and as the docdate doesn't exist on those days, it would be looking at the next docdate. Am i assuming this correctly?

7

Change d.docdate to a.dt

1 Like
8

Hi bitsmed,

That solved the issue. Thanks for the help!