Jacob L
asked on
sql select total by week ending
I have a query that returns the total ordered of an item between a selected date range as shown below.
select GK_ITEM_ID
, GK_STORE_ID
, sum(case when USER_ORDER_QTY is null
then ORDER_SIZE_QTY else USER_ORDER_QTY end) TotalOrdered
from DW_FCT_ORDERS
where GK_STORE_ID = 106
and GK_ITEM_ID = 111872
and TIME_DATE between '2017-04-01' and '2017-04-10'
group by GK_ITEM_ID, GK_STORE_ID
returns the following.
GK_ITEM_ID GK_STORE_ID TotalOrdered
111872 106 4.000
What i need is instead of using the selected date range "TIME_DATE between '2017-04-01' and '2017-04-10' "
I need the total ordered by item, by store, by week. Week being Sunday to Saturday. So the results would look like this.
storeid GK_ITEM weekending Totalordersize
123 111872 4-Mar 56
251 565473 11-Mar 35
200 354648 18-Mar 123
145 987321 25-Mar 354
The database has a table called DW_Dim_Time that i think i should be able to use that looks like this.
I just have no idea where to start. Any help would be greatly appreciated. Thank you
select GK_ITEM_ID
, GK_STORE_ID
, sum(case when USER_ORDER_QTY is null
then ORDER_SIZE_QTY else USER_ORDER_QTY end) TotalOrdered
from DW_FCT_ORDERS
where GK_STORE_ID = 106
and GK_ITEM_ID = 111872
and TIME_DATE between '2017-04-01' and '2017-04-10'
group by GK_ITEM_ID, GK_STORE_ID
returns the following.
GK_ITEM_ID GK_STORE_ID TotalOrdered
111872 106 4.000
What i need is instead of using the selected date range "TIME_DATE between '2017-04-01' and '2017-04-10' "
I need the total ordered by item, by store, by week. Week being Sunday to Saturday. So the results would look like this.
storeid GK_ITEM weekending Totalordersize
123 111872 4-Mar 56
251 565473 11-Mar 35
200 354648 18-Mar 123
145 987321 25-Mar 354
The database has a table called DW_Dim_Time that i think i should be able to use that looks like this.
I just have no idea where to start. Any help would be greatly appreciated. Thank you
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
This is exactly what i needed. thank you.
you are assuming the date format is of format YYYY-MM-DD
imho,
a iso week is from monday to sunday
use a group by cube or group by rollup with trunced dates
Open in new window