Running total by day for transactions SQL-CodePudding

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:

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

Part 2 - Problem 2 - CTEs storeItemStatus and storeItemStatusOnAllDays:

Part 2 - Table-variable @dates:

Final query: storeItemStatusOnAllDays LEFT OUTER JOIN movementPerDay:

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

Part 2 - Table-variable `@dates`:

Final query: `storeItemStatusOnAllDays LEFT OUTER JOIN movementPerDay`: