sql - group by week, get first day of week, pivot by weekday -

sql - group by week, get first day of week, pivot by weekday -

i need create query show how much of each item/unit combination each client ordered each week (group week) while showing first day week, , showing quantity ordered each day of week (pivot weekday). far have this, i'm not sure how grouping week.

select customer_name, item_code, item_desc, unit, delivery_date, [monday], [tuesday], [wednesday], [thursday], [friday], [saturday], [sunday] ( select customer_name, item_code , item_desc , unit, delivery_date, datename(dw, delivery_date) dayweek, qty order_items oi inner bring together orders on localid = local_order_id ) ordersitems pivot ( sum(qty) dayweek in ([monday], [tuesday], [wednesday], [thursday], [friday], [saturday], [sunday]) ) pvt

the next query groups customer/item/unit/week# combination. returns total number of orders # of orders broken downwards day of week. case statement used along sum function total number of orders each day of week

edit: revised query grouping week starting date (instead of week # previously)

select customer_name, item_code, item_desc, unit, case sign(7-(datepart(dw, action)+2)) when -1 cast(dateadd(dd, 7-(datepart(dw, action)+2), action) date) when 0 cast(action date) when 1 cast(dateadd(dd, -(datepart(dw, action)+2), action) date) end week_starting_friday sum(qty) total_orders, sum( case datename(dw, delivery_date) when 'monday' qty else 0 end ) [monday], sum( case datename(dw, delivery_date) when 'tuesday' qty else 0 end ) [tuesday], sum( case datename(dw, delivery_date) when 'wednesday' qty else 0 end ) [wednesday], sum( case datename(dw, delivery_date) when 'thursday' qty else 0 end ) [thursday], sum( case datename(dw, delivery_date) when 'friday' qty else 0 end ) [friday], sum( case datename(dw, delivery_date) when 'saturday' qty else 0 end ) [saturday], sum( case datename(dw, delivery_date) when 'sunday' qty else 0 end ) [sunday] order_items oi inner bring together orders on localid = local_order_id grouping customer_name, item_code, item_desc, unit, case sign(7-(datepart(dw, action)+2)) when -1 cast(dateadd(dd, 7-(datepart(dw, action)+2), action) date) when 0 cast(action date) when 1 cast(dateadd(dd, -(datepart(dw, action)+2), action) date) end order 5, customer_name, item_code, item_desc, unit;

references:

case statement on msdn datename on msdn

sql sql-server