Geek asks and answers

T-SQL: find the latest cumulative state event to date

I have a table with stock 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 the report should contain the day that has passed since the moment when the article had a negative balance (balance). In the above example, this means yesterday as the answer.

I am trying to translate this to SQL, but I am having problems. I tried using 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 a problem that I cannot use the subquery in the recursive part (to find the last transaction before the current one), and the inner join will end the loop when there is no transaction during a certain transaction (so the dateaddpart will also fail).

What would be the best approach to solve this problem?

+3
2

, - OUTER APPLY :

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

. , LEFT JOIN , . , (- dbo.Calendar), , , , CTE, . , ,

, , , ( ).

;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

2

SQL Fiddle, OUTER APPLY . , CTE , , 17% , 83% CTE.

+2

. :

:

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


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

More articles:


All Articles