Grouping ORDER ITEMS from hourly to daily

Question

I would like to SUM the price of these Order Items together for each Order for a Contract.

I want to reduce my granularity from hourly to daily and so reduce the row count that we pass to the fact table and then the SSAS cube.

i.e. Contract A which can have many Orderlines, consider Orderline 1 which can have many Order Items. I have had to screen the Order Items, but they are just sequential id numbers.

The problem is that I have to roll this up to a daily granularity from hourly, but still be able to give users on the cube access to the Order Item level

Answer 1

you can use SUM as a window function. this will effectively write the same sum to multiple rows for each order-item; just like the price.

for example

SELECT ......
, SUM(price) OVER (PARTITION BY order_item)
....
FROM ....
GROUP BY ....