T-SQL: Find the last occurence of a aggregate state before today

Question

I have a table with inventory transactions. A simplified example:

--Inventory Transactions            
Date         Sold    Purchased  Balance(not in table)
Today        1                  -5
Yesterday    6                  -4
5 days ago           5          +2
10 days ago  103                -3 
20 days ago          100        +100

Requirements indicate that a report should contain the day since an article had negative balance (stockout). In the example above it would mean yesterday as the answer.

I'm trying to translate this into SQL but I'm having some trouble. I have tried using a CTE:

with Stockouts as (
select getdate() as [Date],
       (calculation) as Balance
from [Inventory Transactions]
--some constraints to get the correct article are omitted
union all
select dateadd(dd, -1, Stockouts.[Date]) as [Date], 
       Stockouts.Balance - (calculation) as Balance
from [Inventory Transactions]
inner join Stockouts    
)

But there is the problem that I cannot use a subquery in the recursive part (to find the last transaction before the current one) and an inner join will stop looping when there is no transaction on a certain date (so the dateadd part will fail as well).

What would be the best approach to solve this issue?

(caculation) is calculating the balance for all records in the anchor part of the CTE, and the change on that day (sold - purchased) for the recursive part — Bas, May 04 '12 at 09:39

score 2 · Accepted Answer · edited May 23 '17 at 12:11

I think the best approach is to use OUTER APPLY like so:

DECLARE @InventoryTransactions TABLE ([Date] DATE, Sold INT, Purchased INT)
INSERT @InventoryTransactions VALUES 
    ('20120504', 1, 0),
    ('20120503', 6, 0),
    ('20120501', 0, 5),
    ('20120425', 103, 0),
    ('20120415', 0, 100)

SELECT  trans.Date, 
        trans.Sold, 
        trans.Purchased, 
        ISNULL(Balance, 0) [BalanceIn],
        ISNULL(Balance, 0) + (Purchased - Sold) [BalanceOut]
FROM    @InventoryTransactions trans
        OUTER APPLY
        (   SELECT  SUM(Purchased - Sold) [Balance]
            FROM    @InventoryTransactions bal
            WHERE   Bal.Date < trans.Date
        ) bal

Your approach is not well suited to recursion. If you require all dates then it would be best to create a date table and LEFT JOIN the results from the above to the table containing all dates. It is probably best to have a permanent table of dates (something like dbo.Calendar) as they are usable in a number of situations, but you can always create a temp table using either Loops, a CTE, or system views to manipulate it. The question on how to generate a list of incrementing dates has been answered before

EDIT

Just re-read your requirements and I think this is a better approach to get what you actually want (uses the same sample data).

;WITH Transactions AS
(   SELECT  trans.Date, 
            trans.Sold, 
            trans.Purchased, 
            ISNULL(Balance, 0) [BalanceIn],
            ISNULL(Balance, 0) + (Purchased - Sold) [BalanceOut]
    FROM    @InventoryTransactions trans
            OUTER APPLY
            (   SELECT  SUM(Purchased - Sold) [Balance]
                FROM    @InventoryTransactions bal
                WHERE   Bal.Date < trans.Date
            ) bal
) 
SELECT  DATEDIFF(DAY, MAX(Date), CURRENT_TIMESTAMP) [Days Since Negative Balance]
FROM    Transactions
WHERE   BalanceIn > 0

EDIT 2

I Have create an SQL Fiddle to demonstrate the difference in query plans between OUTER APPLY and Recursion. You can see that the CTE is masses more work, and when running the same data on my local machine it tells me that when running the two in the same batch the outer apply method has a relative batch cost of 17% less than a quarter of the 83% taken up by the Recursive CTE method.

I have millions of inventory transactions, and have to do this for half a million different article/country combinations. The performance problem here is getting the complete balance for every transaction. I already indexed what I can (99.92% improvement). By estimation it will still need 6 days to run. — Bas, May 04 '12 at 11:03
Then you almost certainly need to warehouse your data! In this case looping through millions of transactions will probably perform better, but neither are going to perform particularly well. Can you not create a table that has one row per day that summarises the transactions, then add a new row to this table at midnight every night for the previous days transactions? — GarethD, May 04 '12 at 11:24
It seems there were some other processes running on the development server consuming all of the CPU time, it actually only runs for one minute. So good news :) — Bas, May 08 '12 at 07:38

score 1 · Answer 2 · answered May 04 '12 at 09:53

If you want to do it in a recursive cte. This could be a suggestion:

Test data:

DECLARE @T TABLE(Date DATETIME,Sold INT, Purchased INT)

INSERT INTO @T
VALUES
    (GETDATE(),1,NULL),
    (GETDATE()-1,6,NULL),
    (GETDATE()-5,NULL,5),
    (GETDATE()-10,103,NULL),
    (GETDATE()-20,NULL,100)

Query

;WITH CTE
AS
(
    SELECT ROW_NUMBER() OVER(ORDER BY Date ASC) AS RowNbr, t.* FROM @T AS T
)
, CTE2
AS
(
    SELECT
        CTE.RowNbr,
        CTE.Date,
        CTE.Sold,
        CTE.Purchased,
        (ISNULL(CTE.Purchased,0)-ISNULL(CTE.Sold,0)) AS Balance
    FROM
        CTE
    WHERE
         CTE.RowNbr=1
    UNION ALL
    SELECT
        CTE.RowNbr,
        CTE.Date,
        CTE.Sold,
        CTE.Purchased,
        CTE2.Balance+ISNULL(CTE.Purchased,0)-ISNULL(CTE.Sold,0) AS Balance
    FROM
        CTE
        JOIN CTE2
            ON CTE.RowNbr=CTE2.RowNbr+1
)
SELECT * FROM CTE2 ORDER BY CTE2.RowNbr DESC

Output

5   2012-05-04 11:49:45.497 1       NULL    -5
4   2012-05-03 11:49:45.497 6       NULL    -4
3   2012-04-29 11:49:45.497 NULL    5        2
2   2012-04-24 11:49:45.497 103     NULL    -3
1   2012-04-14 11:49:45.497 NULL    100     100

Thank you, a very nice way of doing it, I will check both answers and use the fastest one :) — Bas, May 04 '12 at 10:12

T-SQL: Find the last occurence of a aggregate state before today

2 Answers2