My setup:

• FACT table of inventory transactions
• Calendar Dimension with 25 years of dates
• Item dimension

I want to be able to create a running on hand total for each inventory item by day – even when there are date ‘holes’ in the FACT table for the item.

Logic:

• Create summary by day
• Create running total by day for each item

If I do something like the following, I get holes, because the inventory item is null for those dates where there is no activity in the FACT table. It seems like I need some type of cross join to create blank dates for each item that does not have activity on that day.

SELECT
o."Item Name", b."CalYr Txn Date", 
SUM(coalesce(o.OnHand,0))  over (partition by
o."Item name"  order by
o."CalYr Txn Date", o."Item name" asc rows between unbounded preceding and current row) as "OnHandDaily"
from
QQubeUser.vd_CalYrTxnDate as b left outer join
(
SELECT i.LinkForItemID, i."Item Name", c."CalYr Txn Date",
SUM(coalesce(v."Inventory Line Inventory Quantity",0)) as OnHand 
from 
QQubeUser.vf_INVENTORY as v
join
QQubeUser.vd_Item as i
on
i.LinkForItemID = v.LinkToItemID
join
QQubeUser.vd_CalYrTxnDate as c
on c.LinkForTxnDateID = v.LinkToTxnDateID
where
i."Item Name" = 'Interior'
GROUP BY i.LinkForItemID, i."Item Name", c."CalYr Txn Date" ) as o
on
o."CalYr Txn Date" = b."CalYr Txn Date"