I have a table of orders with varying dates and have been asked to get the weekly order count for past 8 weeks and cummulative order count overall
I have been using the
datepart clause to break my dates in week and the count function to calculate the amount of orders in that week. The code below breaks my dates into week numbers and there is a count for orders but I would like to roll it up if possible so all week 1 orders are on the same line?
Select datepart(wk,orders.date_created), COUNT(*) from Orders group by date_created;
I would also like to count the number of orders by week from a certain city.
Select datepart(wk,orders.date_created), COUNT(*) from Orders right join City as C on Orders.city_id = C.city_id Where city_name = 'Zurich' group by date_created
This works to a certain extent but not as neat as I would like. I would also like a cummulative total of orders as the weeks go on, week 1 + week 2, so on and so forth.