SQL using while loop to calculate values for multiple elements

Question

I am trying to calculate an average daily stock of each item in a warehouse using Ms SQL. Every incoming and outgoing quantity is recorded in the same column, let's name it [quantity]. Due to this structure the only way for me to tell the stock level for today is to simply sum all of the quantity from the beginning of time. i.e.

SELECT [item], SUM(quantity)
FROM item_table

If I want to see the stock level of yesterday I would need to following code:

SELECT [item], SUM(quantity)
FROM item_table
WHERE [date] < DATEADD(day,-1,GETDATE())

And so on for every single day. Of course I'm not going to go through the trouble of manually changing the DATEADD number 360 times in order to get the stock level for the entire year. Hence I am using a While loop as follows:

DECLARE @intFlag INT
DECLARE @s DECIMAL = 0
DECLARE @i varchar(20) --replaced with table (see table part below)
SET @intFlag = 0
WHILE (@intFlag <= 360)
BEGIN
SET @intFlag = @intFlag + 1
SET @i = (SELECT TOP (1) [item] FROM item_table ) --replaced with table (see table part below)
SET @s = (
        SELECT
            SUM(quantity) + @s AS inventory
        FROM item_table
        WHERE 
        item IN (@i) --replaced with table (see table part below)
        AND [Date] < dateadd(day, - @intFlag, GETDATE())
        )
IF @intFlag = 360
    BREAK;
END

SELECT 
@i AS [item],
@s/360 AS [AVG_stock]

This is working properly so far, but I'm stuck when trying to do the same operation for multiple items. When I try replacing the @i parameter with a table like this:

DECLARE @items table (number varchar (20))
INSERT INTO @items 
SELECT DISTINCT [Item]
FROM items_table

And try to call it in

SET @s = (
        SELECT
            SUM(quantity) + @s AS inventory
        FROM item_table
        WHERE 
        item IN (@items) 
        AND [Date] < dateadd(day, - @intFlag, GETDATE())
        )

SQL cannot find the @item table and says that it needs to be declared first. At this point I don't know why is this happening and how can I fix it. Also I have my doubts about the performance of that query since there are around 30,000 items that I would perform the stock calculation for.

Maybe there is an easy fix to this problem, or maybe someone can come up with a completely different way of calculating that damned thing. Anyway I could use any help I can get.

Kind Regards.

Try to replace this part: ´WHERE item IN (@items)´ with ´WHERE item IN (SELECT number FROM @items) ´ — Julia, Feb 03 '17 at 13:54
WHERE item IN (SELECT number FROM @items). Can't you just Group by StockItem, Date and then Avg the stockAmount column, add a where clause to limit the date? — Snowlockk, Feb 03 '17 at 13:55
But I can't really get it. You are calculating a SUM() of some item for every day - day by day. Is this not equvalent to calculating SUM() of this item for a whole periode of 360 days? Why are you making it so complicated? You may GROUP BY items that you inserted in your @Items table in the end. — Julia, Feb 03 '17 at 14:02
Hi, thanks for the quick answer ! The (SELECT number FROM @items) is quite an obvious solution, can't believe I didn't come up with it before :D ! Unfortunately the performance is terrible as I expected. — Xberger_Pete, Feb 03 '17 at 14:06
As explained in the post , I can't simply limit the dates because of the way the table is structured. I can't sum the stock 'day by day'. I can only sum the entire value up to a certain day to get a correct amount for that day. If I were to limit it from both sides with date between '' and '' it's gonna cut out previous incoming and outgoing stock quantities returning a wrong amount. — Xberger_Pete, Feb 03 '17 at 14:16

score 1 · Answer 1 · answered Feb 03 '17 at 14:03

1

What you want is a cumulative sum. Even in SQL Server 2008 your while loop is not the best approach. You might as well do the following:

SELECT it.item, it2.quantity
FROM item_table it OUTER APPLY
     (SELECT SUM(it2.quantity) as quantity
      FROM it2
      WHERE it2.item = it.item AND
            it2.date < it.date
     ) it2;

In this case, the fastest way (apart from upgrading SQL Server) is to do a while loop keeping an incremental value for each day. However, that might be challenging because you need to handle a separate variable for each item.

answered Feb 03 '17 at 14:03

Gordon Linoff

1,242,037
58
646
786

should it be it2.date <= it.date? – Vamsi Prabhala Feb 03 '17 at 14:05
Hi, this calculation performs great! However I don't know how could I limit it to only calculate for the past 360 days. – Xberger_Pete Feb 03 '17 at 15:50
@Maffa_sql . . . You can put the date condition in a `where` clause in the outer query. – Gordon Linoff Feb 04 '17 at 03:19

Charles Bretana · Accepted Answer · 2017-02-03T16:22:00.650

For ALL items, try this:

    With Ints(aInt) As
      (Select -365 Union All
       Select aInt + 1 From Ints
       Where aInt < 0)   
    Select item,
       DateAdd(day, i.aInt, getdate()) tdate 
       Sum(quantity) runningTotal
    from item_table it join Ints i
       on it.[Date] < DateAdd(day, i.aInt, getdate()) 
    group by item, DateAdd(day, i.aInt, getdate()) 
    Option (MaxRecursion 1000)

to restrict it to some small set of items, just add a where clause

    With Ints(aInt) As
      (Select -365 Union All
       Select aInt + 1 From Ints
       Where aInt < 0)   
    Select item,
       DateAdd(day, i.aInt, getdate()) tdate 
       Sum(quantity) runningTotal
    from item_table it join Ints i
       on it.[Date] < DateAdd(day, i.aInt, getdate()) 
    where item in ([set of comma-delimited item values here])
    group by item, DateAdd(day, i.aInt, getdate()) 
    Option (MaxRecursion 1000)

I couldn't get results from this code as im getting the following error when I try to run it : The maximum recursion 100 has been exhausted before statement completion (error 530). Also sql required this code to be grouped by item and the i.aInt column. — Xberger_Pete, Feb 03 '17 at 15:59
oh yeah I forgot... you have to reset the max recursion level (it defaults to 100). I modified to add that (and the group by clause) — Charles Bretana, Feb 03 '17 at 16:21

score 0 · Answer 3 · edited May 23 '17 at 11:53

Create a "Numbers" (or "Tally" table). It is just a single-column table than contains sequential integers. You can read about how to create one here. They are useful for replacing while loops.

Once you have that, you can do something like this:

select i.item, 
i.date, 
sum(i.quantity) over (partition by i.item order by i.date) quantity
from item_table i
join Numbers n on i.date = DATEADD(dd, -1*n.n, convert(date, getdate()))
where n.n <= 365

That should give you a running total of your quantity for each day.

DEMO here

SQL using while loop to calculate values for multiple elements

3 Answers3