Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in August and this forum will be retired.

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.


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

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"
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 
QQubeUser.vf_INVENTORY as v
QQubeUser.vd_Item as i
i.LinkForItemID = v.LinkToItemID
QQubeUser.vd_CalYrTxnDate as c
on c.LinkForTxnDateID = v.LinkToTxnDateID
i."Item Name" = 'Interior'
GROUP BY i.LinkForItemID, i."Item Name", c."CalYr Txn Date" ) as o
o."CalYr Txn Date" = b."CalYr Txn Date"

asked 02 Oct '13, 13:08

cpv's gravatar image

accept rate: 0%

edited 02 Oct '13, 13:43

Breck%20Carter's gravatar image

Breck Carter

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



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "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:


question asked: 02 Oct '13, 13:08

question was seen: 1,079 times

last updated: 02 Oct '13, 13:43