CodeMonkey - 10 months ago 40

SQL 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

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.