Link to home
Start Free TrialLog in
Avatar of Jacob L
Jacob LFlag for United States of America

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.
User generated image
I just have no idea where to start. Any help would be greatly appreciated. Thank you
Avatar of Geert G
Geert G
Flag of Belgium image

assumptions ... are the basis of wrong conversion
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

select GK_ITEM_ID 
                        , GK_STORE_ID, trunc(time_date, 'WEEK') TIME_WEEK, 
                , 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 
group by rollup (GK_ITEM_ID, GK_STORE_ID, trunc(time_date, 'WEEK')) 

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Shaun Kline
Shaun Kline
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Jacob L

ASKER

This is exactly what i needed. thank you.