SQL filling dimensional gaps with date dimension

Question

I have the output from the following query

SELECT INTO #GroupedData
SUM(Amount) OVER (PARTITION BY Dim1, Dim2 ORDER BY DimDate) AS RunningTotal
SUM(Amount) AS Total
Dim1, Dim2, DimDate
FROM FactTable
GROUP BY Dim1, Dim2, DimDate

This leaves me with possible holes so i have a CalendarTable that i can join in

To get future date values or fill gaps. But how do i do this taking dimensions into account?

I Understand this hasthe potential to yield many rows as its DimDate X Dim1 X Dim2 depending if you can limit it by actual Dim combinations.

Answer 1

Assuming you have each date in the fact table somewhere, then you can generate the rows using a cross join. Then a left join to bring in the rows from the fact table:

select d1.dim1, d2.dim2, d.dimdate, sum(f.amount) as total,
       sum(sum(amount)) over (parition by d1.dim1, d2.dim2 order by d.dimdate) as runningtotal
from (select distinct dim1 from facttable) d1 cross join
     (select distinct dim2 from facttable) d2 cross join
     (select distinct dimdate from facttable) d left join
     facttable f
     on f.dim1 = d1.dim1 and f.dim2 = d2.dim2 and f.dimdate = d.dimdate
group by d1.dim1, d2.dim2, d.dimdate;

This does assume that each date appears once in the table. And, d1 and d2` might already be in tables, so those tables can be used instead of a subquery.