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.
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"