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

  • Just a comment. Are you sure you want to use the WK code for DATEPART? It doesn't calculate week number like "the rest of the world" does, according to the ISO 8601 standard. That is why we also got the ISO_WEEK code in SQL server 2008, to allow just for a "correct" week number calculation. Assuming you have done appropriate SET DATEFIRST, I should add... – Tibor Karaszi Dec 5 at 7:43
  • Not familiar with the ISO_WEEK code in sql server, I will look it up – RustyRyan Dec 5 at 19:41
  • I believe that 2017 is a year where these don't return the same value. So try a few dates from a number of years and for a year when they don't return the same value, use whichever calculates according to how you want to do that (probably matches your culture/language preference). – Tibor Karaszi Dec 6 at 7:56
up vote 3 down vote accepted
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

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.