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"

asked 02 Oct '13, 13:08

cpv's gravatar image

cpv
915511
accept rate: 0%

edited 02 Oct '13, 13:43

Breck%20Carter's gravatar image

Breck Carter
25.7k428592852

Be the first one to answer this question!
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×412

question asked: 02 Oct '13, 13:08

question was seen: 517 times

last updated: 02 Oct '13, 13:43