I have a table of transactions that I have successfully queried to get a running total of the amounts per day, partitioned by scenario_id as shown in the following example:

Tables:

Transactions

Filters

Code:

SELECT   transaction_date, scenario_id, SUM(transaction_amount) AS net_daily,
                             (SELECT   filters.starting_cash
                                FROM         filters)   SUM(SUM(transaction_amount)) OVER (PARTITION BY scenario_id
ORDER BY transaction_date) AS forecasted_cash
FROM         Transactions
WHERE     transaction_date >=
                             (SELECT   filters.start_date
                                FROM         filters)
GROUP BY transaction_date, scenario_id

Current Result

However, I would like to have all empty dates in the timeline that is filtered for to be populated with $0 net daily while carrying on the forecasted cash running total from the day before:

Desired Result

What is the best way to accomplish this?

CodePudding user response：

You need to generate a list of dates and then left join to the Transactions table.

CTE filters_dates recursively generates the list of dates. CTE scenario gets the distinct Scenario_id. CTE trans summarize the transactions by date & scenario_id as you have multiple entry with same date. The final result of Forecasted_cash is basically a running total of Transaction _Amount starting_cash

with 
filters_dates as
(
    select starting_cash, start_date, end_date, trans_date = start_date
    from   Filters
    union all
    select starting_cash, start_date, end_date, trans_date = dateadd(day, 1, trans_date)
    from   filters_dates
    where  trans_date < end_date
),
scenario as
(
    select distinct Scenario_id 
    from   Transactions
),
trans as
(
    select trans_date = transaction_date, Scenario_id, trans_amount = sum(transaction_amount)
    from   Transactions
    group by transaction_date, Scenario_id
)
select f.trans_date, 
       s.Scenario_id,
       net_daily = isnull(t.trans_amount, 0),
       Forecasted_cash = f.starting_cash
                         sum(isnull(t.trans_amount, 0)) over (partition by s.Scenario_id
                                                                  order by f.trans_date)
from   filters_dates f
       cross join scenario s
       left join trans t  on  f.trans_date = t.Trans_Date
                          and s.Scenario_id = t.Scenario_id
order by s.Scenario_id, f.trans_date;

CodePudding user response：

It sounds to me like a typical use for a Tally table. This question is similar to yours, make sure to check out the article linked in the accepted answer.

Fill Missing Dates In a Date-Sequenced in SQL using Tally Table