Filling missing weekend rows with previous working day values

The below is a solution that uses recursion instead of a calendar table.

It essentially works by 'extending' your original data to create some extra rows with 0 balances for every customer at:

1. The min date in the table (if the customer didn't already have a record at the min date)
2. The max date in the table (if the customer didn't already have a record at the max date)
3. The day after the last record for the customer (as long as this doesn't go over the max date in the table)

It then uses recursion to plug the gaps between the dates for each customer.

With balances as (
    -- This is a simplified version of the data already in your table
    SELECT '2022-07-01' as dt, 1 as customer_id, 100 as balance
    UNION ALL SELECT '2022-07-04' as dt, 1 as customer_id, 150 as balance
    UNION ALL SELECT '2022-07-05' as dt, 1 as customer_id, 200 as balance
    UNION ALL SELECT '2022-07-31' as dt, 1 as customer_id, 650 as balance
    UNION ALL SELECT '2022-07-07' as dt, 2 as customer_id, 200 as balance
    UNION ALL SELECT '2022-07-08' as dt, 2 as customer_id, 300 as balance
    UNION ALL SELECT '2022-07-11' as dt, 2 as customer_id, 400 as balance
    UNION ALL SELECT '2022-07-19' as dt, 2 as customer_id, 750 as balance
) 
, min_records as (
    -- This can create a 0 balance record for each customer at the min date in the table
    SELECT dt, customer_id, 0 as balance
    FROM (
        SELECT min(dt) as dt
        FROM balances
    ) as min_dt
    CROSS JOIN (
        SELECT DISTINCT customer_id
        FROM balances
    ) as customers
)
, max_records as (
    -- This can create a 0 balance record for each customer at the max date in the table
    SELECT dt, customer_id, 0 as balance
    FROM (
        SELECT max(dt) as dt
        FROM balances
    ) as min_dt
    CROSS JOIN (
        SELECT DISTINCT customer_id
        FROM balances
    ) as customers
)
, max_customer_records as (
    -- This creates a 0 balance record for each customer for the day after their last record,
    -- so long as that date does not go beyond the max date in the table
    SELECT dateadd(day, 1, max(dt)) as dt, customer_id, 0 as balance
    FROM balances as a
    CROSS JOIN (
        SELECT max(dt) as max_dt
        FROM balances
    ) as m
    GROUP BY customer_id, max_dt
    HAVING max(dt) < max_dt
)
, extended_balances as (
    -- We then join all of the tables above to the original balances table.
    -- Grouping to the dt + customer level and sum(balance) wont cause issues for customers
    -- who already had a record on the min(dt) or max(dt) because x + 0 still = x
    SELECT dt, customer_id, sum(balance) as balance
    FROM (
        SELECT * 
        FROM balances
        UNION 
        SELECT dt, customer_id, balance
        FROM min_records
        UNION 
        SELECT dt, customer_id, balance
        FROM max_records
        UNION 
        SELECT dt, customer_id, balance
        FROM max_customer_records
    ) AS A
    GROUP BY dt, customer_id
)
, recursive_query as (
    -- Now we use recursion to fill in the gaps between the dates
    SELECT dt as original_dt
        , dt
        , customer_id
        , balance
        -- We use lead() to find the date when a new balance exists
        , coalesce(lead(dt) over(partition by customer_id order by dt asc), dateadd(day, 1, dt)) as next_dt
    FROM extended_balances
    UNION ALL
    SELECT original_dt
        , dateadd(day, 1, dt)
        , customer_id
        , balance
        , next_dt
    FROM recursive_query 
    WHERE dateadd(day, 1, dt) < next_dt 
)

SELECT dt, customer_id, balance
FROM recursive_query
ORDER BY customer_id, dt

To help illustrate the steps, I've included examples of key tables:

Balances:

Extended Balances:

First 10 records of the recursive query: