Sql-server – Rolling week numbers into one row and getting cummulative values

sql serverssms

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.

Any ideas

Best Answer

create table orders(id int identity, dt_created date);

insert into  orders (dt_created) values
('20180101'),('20180108'),('20180109'),('20180110'),('20180112'),
('20180113'),('20180114'),('20180115'),('20180116'),('20180117'),
('20180118'),('20180120'),('20180121'),('20180122'),('20180123');
GO

15 rows affected

SELECT
    DATEPART(WK, dt_created) week,
    COUNT(*) CT
FROM
    orders
GROUP BY
    DATEPART(WK, dt_created)
GO

week | CT
---: | -:
   1 |  1
   2 |  5
   3 |  6
   4 |  3

Have a look at OVER clause and how to get a cumulative total. 

WITH WK AS
(
SELECT
    DATEPART(WK, dt_created) week,
    COUNT(*) CT
FROM
    orders
GROUP BY
    DATEPART(WK, dt_created)
)
SELECT
    week,
    SUM(CT) OVER (ORDER BY week) total
FROM
    WK;
GO

week | total
---: | ----:
   1 |     1
   2 |     6
   3 |    12
   4 |    15

db<>fiddle here

Related Question