What I'm trying to do is get a running stock level for every day for the past year for each item

Question

I have the below SQL query that works but it's very slow. it takes about 1 min to run the query. This would be made into a stored procedure. which is not the problem. but that store procedure would be called for every item of which there are about 600 items. The estimated run time probably would end up taking about 10 hours. Does anyone have any suggestions of a better way of doing it?

What I'm trying to do is get a running stock level for every day for the past year for each item.

If you need any more information. Please let me know.

DECLARE @StartDate AS DATETIME
DECLARE @EndDate AS DATETIME
DECLARE @CurrentDate AS DATETIME
DECLARE @ItemName As Varchar(450)
DECLARE @QOH DECIMAL(19,4)

SET @QOH = 0
SET @ItemName = 'TUR001-02'
SET @StartDate = '2020-04-01'
SET @EndDate = GETDATE()
SET @CurrentDate = @StartDate

CREATE TABLE #TempTable
(
    Date datetime,
    ItemName char(450),
    QOH DECIMAL(19,4) 
);

WHILE (@CurrentDate < @EndDate)
BEGIN
    DECLARE @daySales DECIMAL(19,4)
    
    SELECT @daySales = SUM(Quantity) 
    FROM qbInvoiceLineDetail 
    WHERE TxnDate = @CurrentDate AND FullName = @ItemName;

    SET @QOH = @QOH - @daySales 

    INSERT INTO #TempTable (Date, ItemName, QOH) 
        SELECT @CurrentDate, @ItemName, @QOH;

    SET @CurrentDate = DATEADD(DAY, 1, @CurrentDate); 
END

SELECT * FROM #TempTable

DROP TABLE #TempTable

Get rid of the loop! Just groupe by day and treat a set as a set. — Stu, Apr 10 '21 at 17:59
Use `SELECT TxnDate,SUM(Quantity) from qbInvoiceLineDetail where FullName = @ItemName and TxnDate between @StarDate and @EndDate group by TxnDate;` and loop over the results.. — Luuk, Apr 10 '21 at 18:01
Could you please provide sample data. Seems you are trying ti perform "running total"-calculation — Sergey, Apr 10 '21 at 18:02
the group by I believe would not calculate the day end QOH day by day — DevBot246, Apr 10 '21 at 18:05
Are you interested in days where you have rows or do you want 1 row per day regardless with quantity=0? — Stu, Apr 10 '21 at 18:06
@June7 this article seems to have help to this point from what I have tested so far. I will have to do some more testing. But thanks. — DevBot246, Apr 10 '21 at 18:41

Stu · Answer 1 · 2021-04-10T18:45:46.173

You can use a tally table to generate your dates between start and end, then insert all your data in one hit into your table.

Caveat - this is untested as I have nothing to check it against, assumes the dates are dates only, if they include time then will need to use convert - hopefully will be what you are looking for:

/*first, create a tally table - this should be a permanent feature */

select top 1000 N=Identity(int, 0, 1)
into dbo.Digits
from master.dbo.syscolumns a cross join master.dbo.syscolumns


declare @StartDate datetime='20200401', @EndDate datetime=GetDate()

select DateAdd(day,N,@startDate) currentDate, FullName ItemName, Sum(Quantity) over(order by d.N) QOH
from Digits d
left join qbInvoiceLineDetail  q on q.TxnDate=DateAdd(day,N,@startDate)
where DateAdd(day,N,@startDate)<=@EndDate
group by TxnDate, ItemName

What I'm trying to do is get a running stock level for every day for the past year for each item

1 Answers1