Running total by day for transactions SQL

Question

I am trying to get a closing balance by day for all transactions by Warehouse, status and product code. I have done this, but am unable to get the value by each of these variables if there is no transaction for that product for the day. I need to put this query in powerbi and display units of stock on hand as at a certain day. I have this query:

select a.Site_0,a.Item,a.Status_0,a.Date_1,a.DailyMVT,
SUM(a.DailyMVT) over(partition by a.Site_0,a.Item,a.Status_0 order by a.Date_0) as RunningTotal
from
(SELECT a.STOFCY_0 as Site_0,
    a.ITMREF_0 as Item, 
    a.STA_0 as Status_0,
    a.CREDATTIM_0 as Date_0,
    a.IPTDAT_0 as Date_1,
    SUM(a.QTYPCU_0) as DailyMVT
    FROM STOJOU a
    WHERE a.IPTDAT_0 > DATEADD(YEAR,-1,getdate()) and a.ITMREF_0 in ('10010261','10030333') 
    GROUP BY a.STOFCY_0,a.ITMREF_0,a.STA_0,a.CREDATTIM_0,a.IPTDAT_0) a
    order by a.Site_0,a.Item,a.Status_0,a.Date_1

Site_0  Item    Status_0    Date_1  DailyMVT    RunningTotal
GRZAF   10010261    A   2022-07-30 00:00:00.000 61.0000000000000    61.0000000000000
GRZAF   10010261    A   2022-08-02 00:00:00.000 -4.0000000000000    57.0000000000000
GRZAF   10010261    A   2022-08-02 00:00:00.000 -2.0000000000000    55.0000000000000
GRZAF   10010261    A   2022-08-03 00:00:00.000 -2.0000000000000    53.0000000000000
GRZAF   10010261    A   2022-08-03 00:00:00.000 -1.0000000000000    52.0000000000000
GRZAF   10010261    A   2022-08-04 00:00:00.000 -1.0000000000000    51.0000000000000
GRZAF   10010261    A   2022-08-04 00:00:00.000 -3.0000000000000    48.0000000000000
GRZAF   10010261    A   2022-08-04 00:00:00.000 -1.0000000000000    47.0000000000000
GRZAF   10010261    A   2022-08-05 00:00:00.000 -1.0000000000000    46.0000000000000
GRZAF   10030333    A   2022-07-30 00:00:00.000 806.0000000000000   806.0000000000000
GRZAF   10030333    A   2022-08-01 00:00:00.000 70.0000000000000    876.0000000000000
GRZAF   10030333    A   2022-08-03 00:00:00.000 -7.0000000000000    869.0000000000000
GRZAF   10030333    A   2022-08-03 00:00:00.000 -14.0000000000000   855.0000000000000
GRZAF   10030333    A   2022-08-04 00:00:00.000 -4.0000000000000    851.0000000000000
GRZAF   10030333    Q1  2022-08-01 00:00:00.000 34.0000000000000    34.0000000000000
GRZAF   10030333    Q1  2022-08-03 00:00:00.000 70.0000000000000    104.0000000000000
GRZAF   10030333    Q1  2022-08-04 00:00:00.000 140.0000000000000   244.0000000000000
GRZAF   10030333    Q1  2022-08-04 00:00:00.000 280.0000000000000   524.0000000000000
GRZAF   10030333    Q1  2022-08-04 00:00:00.000 69.0000000000000    593.0000000000000
GRZAF   10030333    Q1  2022-08-05 00:00:00.000 45.0000000000000    638.0000000000000
GRZAF   10030333    Q2  2022-07-30 00:00:00.000 22.0000000000000    22.0000000000000
GRZAU   10010261        2022-07-30 00:00:00.000 0.0000000000000 0.0000000000000
GRZAU   10010261    A   2022-07-30 00:00:00.000 142.0000000000000   142.0000000000000
GRZAU   10010261    A   2022-08-02 00:00:00.000 -2.0000000000000    140.0000000000000
GRZAU   10010261    A   2022-08-03 00:00:00.000 -1.0000000000000    139.0000000000000
GRZAU   10010261    A   2022-08-03 00:00:00.000 -2.0000000000000    137.0000000000000
GRZAU   10010261    A   2022-08-03 00:00:00.000 -3.0000000000000    134.0000000000000
GRZAU   10010261    A   2022-08-04 00:00:00.000 -2.0000000000000    132.0000000000000
GRZAU   10010261    A   2022-08-04 00:00:00.000 -1.0000000000000    131.0000000000000
GRZAU   10010261    A   2022-08-04 00:00:00.000 -3.0000000000000    128.0000000000000
GRZAU   10010261    A   2022-08-05 00:00:00.000 -2.0000000000000    126.0000000000000
GRZAU   10010261    A   2022-08-06 00:00:00.000 -1.0000000000000    125.0000000000000
GRZAU   10030333        2022-07-30 00:00:00.000 0.0000000000000 0.0000000000000
GRZAU   10030333    A   2022-07-30 00:00:00.000 828.0000000000000   828.0000000000000
GRZAU   10030333    A   2022-08-03 00:00:00.000 -40.0000000000000   788.0000000000000
GRZAU   10030333    A   2022-08-04 00:00:00.000 -16.0000000000000   772.0000000000000
GRZAU   10030333    A   2022-08-04 00:00:00.000 -64.0000000000000   708.0000000000000
GRZAU   10030333    A   2022-08-05 00:00:00.000 -64.0000000000000   644.0000000000000
GRZAU   10030333    A   2022-08-05 00:00:00.000 -64.0000000000000   580.0000000000000
GRZAU   10030333    A   2022-08-06 00:00:00.000 -64.0000000000000   516.0000000000000
GRZIT   10010261    A   2022-07-30 00:00:00.000 182.0000000000000   182.0000000000000
GRZNZ   10030333    A   2022-07-30 00:00:00.000 104.0000000000000   104.0000000000000
GRZNZ   10030333    A   2022-08-01 00:00:00.000 -70.0000000000000   34.0000000000000
GRZNZ   10030333    A   2022-08-01 00:00:00.000 -34.0000000000000   0.0000000000000
GRZNZ   10030333    Q1  2022-08-03 00:00:00.000 70.0000000000000    70.0000000000000
GRZNZ   10030333    Q1  2022-08-03 00:00:00.000 -70.0000000000000   0.0000000000000
GRZNZ   10030333    Q1  2022-08-04 00:00:00.000 140.0000000000000   140.0000000000000
GRZNZ   10030333    Q1  2022-08-04 00:00:00.000 -140.0000000000000  0.0000000000000
GRZNZ   10030333    Q1  2022-08-04 00:00:00.000 280.0000000000000   280.0000000000000
GRZNZ   10030333    Q1  2022-08-04 00:00:00.000 -280.0000000000000  0.0000000000000
GRZNZ   10030333    Q1  2022-08-04 00:00:00.000 69.0000000000000    69.0000000000000
GRZNZ   10030333    Q1  2022-08-04 00:00:00.000 -69.0000000000000   0.0000000000000
GRZNZ   10030333    Q1  2022-08-05 00:00:00.000 45.0000000000000    45.0000000000000
GRZNZ   10030333    Q1  2022-08-05 00:00:00.000 -45.0000000000000   0.0000000000000

So this works fine apart from the fact that it does not display a line for a day if there is no transactions.. for example, if I wanted to see stock closing balance for GRZAF 10010261 A on 1st of August, I would get 0, but I want this to show 61.

Would anyone have any ideas please? Here is the link to SQLFiddle: http://sqlfiddle.com/#!18/818682/4/0

Dai · Accepted Answer

Assuming I understand your data representation correctly... I came up with this:

I renamed your columns for clarity and also used ideal types on columns (e.g. using date not datetime where appropriate, which also impacts how GROUP BY works if you're not careful).

Part 1: Schema and sample input data

CREATE TABLE dbo.Stonks (
  [RowId]   int               NOT NULL PRIMARY KEY,
  StoreId   varchar(5)        NOT NULL, -- Warehouse Location
  ItemRef   varchar(20)       NOT NULL, -- SKU
  [Status]  varchar(3)        NOT NULL, -- Status
  Created   datetime2(7)      NOT NULL, -- When a User INSERTed this Row.
  TxnDate   date              NOT NULL, -- The date of the actual stock change.
  DeltaQty  numeric(28,13)    NOT NULL  -- Change in stock quantity (negative: sale, positive: restock).
);

GO

CREATE INDEX IX_TxnDate ON dbo.Stonks ( TxnDate ) INCLUDE ( StoreId, ItemRef, [Status], DeltaQty );
GO
CREATE INDEX IX_StoreId_ItemRef_Status ON dbo.Stonks ( StoreId, ItemRef, Status )/* INCLUDE (  )*/;
GO

GO

INSERT INTO dbo.Stonks (
    [RowId], StoreId, ItemRef, [Status], Created, TxnDate, DeltaQty
)
VALUES

-- RowId, StoreId , ItemRef   , Status, Created     , TxnDate     , DeltaQty
(      1, 'GRZIT' , '10010261', 'A'   , '2022-07-30', '2022-07-30',     182 ),
(      2, 'GRZAU' , '10010261', 'A'   , '2022-07-30', '2022-07-30',      13 ),
(      3, 'GRZAU' , '10010261', 'A'   , '2022-07-30', '2022-07-30',     129 ),
(      4, 'GRZAU' , '10010261', ' '   , '2022-07-30', '2022-07-30',       0 ),
(      5, 'GRZAF' , '10010261', 'A'   , '2022-07-30', '2022-07-30',      61 ),
(      6, 'GRZAF' , '10010261', 'A'   , '2022-08-01', '2022-08-02',      -4 ),
(      7, 'GRZAF' , '10010261', 'A'   , '2022-08-02', '2022-08-02',      -2 ),
(      8, 'GRZAU' , '10010261', 'A'   , '2022-08-02', '2022-08-02',      -2 ),
(      9, 'GRZAU' , '10010261', 'A'   , '2022-08-02', '2022-08-03',      -1 ),
(     10, 'GRZAF' , '10010261', 'A'   , '2022-08-03', '2022-08-03',      -2 ),
(     11, 'GRZAU' , '10010261', 'A'   , '2022-08-03', '2022-08-03',      -2 ),
(     12, 'GRZAF' , '10010261', 'A'   , '2022-08-03', '2022-08-03',      -1 ),
(     13, 'GRZAU' , '10010261', 'A'   , '2022-08-03', '2022-08-03',      -3 ),
(     14, 'GRZAU' , '10010261', 'A'   , '2022-08-04', '2022-08-04',      -2 ),
(     15, 'GRZAU' , '10010261', 'A'   , '2022-08-04', '2022-08-04',      -1 ),
(     16, 'GRZAF' , '10010261', 'A'   , '2022-08-04', '2022-08-04',      -1 ),
(     17, 'GRZAU' , '10010261', 'A'   , '2022-08-04', '2022-08-04',      -3 ),
(     18, 'GRZAF' , '10010261', 'A'   , '2022-08-04', '2022-08-04',      -3 ),
(     19, 'GRZAF' , '10010261', 'A'   , '2022-08-04', '2022-08-04',      -1 ),
(     20, 'GRZAU' , '10010261', 'A'   , '2022-08-05', '2022-08-05',      -2 ),
(     21, 'GRZAF' , '10010261', 'A'   , '2022-08-05', '2022-08-05',      -1 ),
(     22, 'GRZAU' , '10010261', 'A'   , '2022-08-06', '2022-08-06',      -1 ),
(     23, 'GRZAU' , '10030333', 'A'   , '2022-07-30', '2022-07-30',     405 ),
(     24, 'GRZAU' , '10030333', 'A'   , '2022-07-30', '2022-07-30',     423 ),
(     25, 'GRZAU' , '10030333', ' '   , '2022-07-30', '2022-07-30',       0 ),
(     26, 'GRZAF' , '10030333', 'Q2'  , '2022-07-30', '2022-07-30',      22 ),
(     27, 'GRZAF' , '10030333', 'A'   , '2022-07-30', '2022-07-30',     111 ),
(     28, 'GRZAF' , '10030333', 'A'   , '2022-07-30', '2022-07-30',     301 ),
(     29, 'GRZAF' , '10030333', 'A'   , '2022-07-30', '2022-07-30',     159 ),
(     30, 'GRZAF' , '10030333', 'A'   , '2022-07-30', '2022-07-30',     235 ),
(     31, 'GRZNZ' , '10030333', 'A'   , '2022-07-30', '2022-07-30',     104 ),
(     32, 'GRZNZ' , '10030333', 'A'   , '2022-07-31', '2022-08-01',     -70 ),
(     33, 'GRZAF' , '10030333', 'A'   , '2022-07-31', '2022-08-01',      70 ),
(     34, 'GRZNZ' , '10030333', 'A'   , '2022-08-01', '2022-08-01',     -34 ),
(     35, 'GRZAF' , '10030333', 'Q1'  , '2022-08-01', '2022-08-01',      34 ),
(     36, 'GRZAF' , '10030333', 'A'   , '2022-08-03', '2022-08-03',      -7 ),
(     37, 'GRZAF' , '10030333', 'A'   , '2022-08-03', '2022-08-03',     -14 ),
(     38, 'GRZNZ' , '10030333', 'Q1'  , '2022-08-03', '2022-08-03',      70 ),
(     39, 'GRZNZ' , '10030333', 'Q1'  , '2022-08-03', '2022-08-03',     -70 ),
(     40, 'GRZAF' , '10030333', 'Q1'  , '2022-08-03', '2022-08-03',      70 ),
(     41, 'GRZAU' , '10030333', 'A'   , '2022-08-03', '2022-08-03',     -40 ),
(     42, 'GRZNZ' , '10030333', 'Q1'  , '2022-08-03', '2022-08-04',     140 ),
(     43, 'GRZNZ' , '10030333', 'Q1'  , '2022-08-03', '2022-08-04',    -140 ),
(     44, 'GRZAF' , '10030333', 'Q1'  , '2022-08-03', '2022-08-04',     140 ),
(     45, 'GRZNZ' , '10030333', 'Q1'  , '2022-08-04', '2022-08-04',     280 ),
(     46, 'GRZNZ' , '10030333', 'Q1'  , '2022-08-04', '2022-08-04',    -280 ),
(     47, 'GRZAF' , '10030333', 'Q1'  , '2022-08-04', '2022-08-04',     280 ),
(     48, 'GRZAU' , '10030333', 'A'   , '2022-08-04', '2022-08-04',     -16 ),
(     49, 'GRZAU' , '10030333', 'A'   , '2022-08-04', '2022-08-04',     -64 ),
(     50, 'GRZNZ' , '10030333', 'Q1'  , '2022-08-04', '2022-08-04',      69 ),
(     51, 'GRZNZ' , '10030333', 'Q1'  , '2022-08-04', '2022-08-04',     -69 ),
(     52, 'GRZAF' , '10030333', 'Q1'  , '2022-08-04', '2022-08-04',      69 ),
(     53, 'GRZAF' , '10030333', 'A'   , '2022-08-04', '2022-08-04',      -4 ),
(     54, 'GRZAU' , '10030333', 'A'   , '2022-08-04', '2022-08-05',     -64 ),
(     55, 'GRZAU' , '10030333', 'A'   , '2022-08-04', '2022-08-05',     -64 ),
(     56, 'GRZNZ' , '10030333', 'Q1'  , '2022-08-05', '2022-08-05',      45 ),
(     57, 'GRZNZ' , '10030333', 'Q1'  , '2022-08-05', '2022-08-05',     -45 ),
(     58, 'GRZAF' , '10030333', 'Q1'  , '2022-08-05', '2022-08-05',      45 ),
(     59, 'GRZAU' , '10030333', 'A'   , '2022-08-06', '2022-08-06',     -64 );

Part 2: Query _{(...well, query statement batch)}

There's two separate problems:
1. The first problem is getting the movement-numbers for TxnDate dates which do exist - which you can do by simply using SUM( DeltaQty ) over each distinct ( StoreId, ItemRef, Status, TxnDate ). Another step is required to use SUM again as a windowed function to generate the running-sum
  - _{(Though if you (the reader) know it's possible to do SUM with GROUP BY and as a partitioned window function in a single CTE step the please let me know and I invite you to edit this answer to improve it!)}
2. The second problem is synthesizing rows with those zero 0 values as filler for all possible calendar-dates that aren't returned in the query above.
  - So we need to convert @from and @to to a range of date values we can INNER JOIN with. So how do we get that?
    - Well, this is a solved problem: just create a physical date table to hold a good range of dates (you could even store every possible date value (0001-01-01 to 9999-12-31 in about 12MB (3.6m possible date values, all sized 3 bytes)) ) and JOIN on that.
    - Unfortunately the OP in this case (in the comments section) said they can't do any DDL (so they can't CREATE or ALTER any TABLE, VIEW, FUNCTION, etc), so we'll have to use a table-variable that we populate with dates from a range right before running the main query.
      - Actually, this really isn't that unfortunate: arguably it's a good thing that we still don't actually need DDL access in order to run non-trivial queries.
  - Anyway, the rest of this step is just filling a table-variable @dates with date values between the @from and @to parameters - and then LEFT OUTER JOIN that @dates table with the data from step 1 (though repeated for each distinct (StoreId+ItemRef+Status) tuple, of course) and use ISNULL to use the non-zero value if available, otherwise use 0 for that date.
The query below uses the named CTEs sumMovementPerDay, runningMovementPerDay, storeItemStatus, and storeItemStatusOnAllDays - followed by the final SELECT query that delivers the single result-set of this statement match.
- The sumMovementPerDay and runningMovementPerDay CTEs correspond to the steps described as "Problem 1" above.
- The @dates table-variable is a range of date values in the range @from <= value < @to, fancy that.
- The storeItemStatus and storeItemStatusOnAllDays CTEs are used to synthesize zero-valued rows for each expected "missing" output row (assuming that this query's resultset's effective "primary key" for each daily-movement-of-goods-balance (NetStonksMovement) is the tuple ( TxnDate, StoreId, ItemRef, [Status] )

DECLARE @from date = '2022-07-29'; /* This is an inclusive lower-bound. */
DECLARE @to   date = '2022-08-05'; /* This is an exclusive upper-bound. */

IF @from >= @to THROW 51000, '`@from` must be less-than `@to`.', 1; 

----------------

DECLARE @dates TABLE (
    [Date] date NOT NULL PRIMARY KEY
);

/* SQL Server's lack of a built-in date-range or date-table generator after 33+ years is one of life's great mysteries. */
DECLARE @t date = @from;

WHILE( @t < @to )
BEGIN
  INSERT INTO @dates ( [Date] ) VALUES ( @t );
  SET @t = DATEADD( d, 1, @t );
END;

--

WITH sumMovementPerDay AS (
    
    SELECT
        s.StoreId,
        s.ItemRef,
        s.[Status],
        s.TxnDate,

        SUM( CONVERT( int, s.DeltaQty ) ) AS SumDeltaQty
    FROM
        dbo.Stonks AS s
    
    WHERE
        s.TxnDate >= @from
        AND
        s.TxnDate <  @to
--      AND
--      a.ItemRef IN ( '10010261', '10030333' ) 
    GROUP BY
        s.StoreId,
        s.ItemRef,
        s.[Status],
        s.TxnDate
),
runningMovementPerDay AS (

    SELECT
        p.StoreId,
        p.ItemRef,
        p.[Status],
        p.TxnDate,

        SUM( p.SumDeltaQty ) OVER (
            PARTITION BY p.StoreId, p.ItemRef, p.[Status]
            ORDER BY p.TxnDate
        ) AS NetStonksMovement

    FROM
        sumMovementPerDay AS p
),
storeItemStatus AS (

    SELECT
        DISTINCT
        s.StoreId,
        s.ItemRef,
        s.[Status]
    FROM
        dbo.Stonks AS s
),
storeItemStatusOnAllDays AS (

    SELECT
        i.StoreId,
        i.ItemRef,
        i.[Status],
        d.[Date]
    FROM
        @dates AS d
        INNER JOIN storeItemStatus AS i ON 1 = 1 
)
--SELECT * FROM @dates                   ORDER BY [Date];
--SELECT * FROM sumMovementPerDay        ORDER BY TxnDate, StoreId, ItemRef, [Status];
--SELECT * FROM runningMovementPerDay    ORDER BY TxnDate, StoreId, ItemRef, [Status];
--SELECT * FROM storeItemStatus          ORDER BY          StoreId, ItemRef, [Status];
--SELECT * FROM storeItemStatusOnAllDays ORDER BY [Date],  StoreId, ItemRef, [Status];

SELECT
    s.StoreId,
    s.ItemRef,
    s.[Status],
    ISNULL( m.TxnDate, s.[Date]    ) AS TxnDate,
    ISNULL( m.NetStonksMovement, 0 ) AS NetStonksMovement
FROM
    storeItemStatusOnAllDays AS s
    LEFT OUTER JOIN runningMovementPerDay AS m ON
        s.StoreId = m.StoreId
        AND
        s.ItemRef = m.ItemRef
        AND
        s.[Status] = m.[Status]
        AND
        s.[Date] = m.TxnDate

ORDER BY
    TxnDate,
    StoreId,
    ItemRef,
    [Status];

To understand how this works, it helps to comment-out the bottom SELECT statement and uncomment one of the two SELECT _ FROM store... lines and run the query - this will show you all of the data which the CTE will yield if queried.

To save time and trouble (and because Markdown tables are nice, but are horrible for quickly sharing dense data-grid results...) here's some screenshots:

Part 2 - Problem 1 - CTEs `sumMovementPerDay` and `runningMovementPerDay`:

`sumMovementPerDay`	`runningMovementPerDay`
`SELECT * FROM sumMovementPerDay ORDER BY TxnDate, StoreId, ItemRef, [Status];`	`SELECT * FROM runningMovementPerDay ORDER BY TxnDate, StoreId, ItemRef, [Status];`

Part 2 - Problem 2 - CTEs `storeItemStatus` and `storeItemStatusOnAllDays`:

storeItemStatus returns all distinct ( StoreId, ItemRef, Status ) tuples.

`storeItemStatus`	`storeItemStatusOnAllDays`
`SELECT * FROM storeItemStatus ORDER BY StoreId, ItemRef, [Status];`	`SELECT * FROM storeItemStatusOnAllDays ORDER BY TxnDate, StoreId, ItemRef, [Status];`

Part 2 - Table-variable `@dates`:

SELECT * FROM @dates  ORDER BY [Date];

Final query: `storeItemStatusOnAllDays LEFT OUTER JOIN movementPerDay`:

SELECT
    s.StoreId,
    s.ItemRef,
    s.[Status],
    ISNULL( m.TxnDate, s.[Date]    ) AS TxnDate,
    ISNULL( m.NetStonksMovement, 0 ) AS NetStonksMovement
FROM
    storeItemStatusOnAllDays AS s
    LEFT OUTER JOIN movementPerDay AS m ON
        s.StoreId = m.StoreId
        AND
        s.ItemRef = m.ItemRef
        AND
        s.[Status] = m.[Status]
        AND
        s.[Date] = m.TxnDate

ORDER BY
    m.TxnDate,
    m.StoreId,
    m.ItemRef,
    m.[Status];

...of course, I have absolutely zero idea if these numbers are correct or not 😸

Here's the execution-plan I get when I create those indexes in the schema section - no obvious missing indexes, phew:

Running total by day for transactions SQL

Answers (1)

Part 1: Schema and sample input data

Part 2: Query _{(...well, query statement batch)}

Part 2 - Problem 1 - CTEs `sumMovementPerDay` and `runningMovementPerDay`:

Part 2 - Problem 2 - CTEs `storeItemStatus` and `storeItemStatusOnAllDays`:

Part 2 - Table-variable `@dates`:

Final query: `storeItemStatusOnAllDays LEFT OUTER JOIN movementPerDay`:

Related Questions

Running total by day for transactions SQL

Answers (1)

Part 1: Schema and sample input data

Part 2: Query (...well, query statement batch)

Part 2 - Problem 1 - CTEs sumMovementPerDay and runningMovementPerDay:

Part 2 - Problem 2 - CTEs storeItemStatus and storeItemStatusOnAllDays:

Part 2 - Table-variable @dates:

Final query: storeItemStatusOnAllDays LEFT OUTER JOIN movementPerDay:

Related Questions

Part 2: Query _{(...well, query statement batch)}

Part 2 - Problem 1 - CTEs `sumMovementPerDay` and `runningMovementPerDay`:

Part 2 - Problem 2 - CTEs `storeItemStatus` and `storeItemStatusOnAllDays`:

Part 2 - Table-variable `@dates`:

Final query: `storeItemStatusOnAllDays LEFT OUTER JOIN movementPerDay`: