How to group by week with MSSQL

  • Grouping by month is pretty much standard, just extract the year, the month and summarize by it, see: How to group sales by month with SQL Server
  • Grouping by week is very similar, but you need to make sure, that you are also summarizing by year and week, so you can span years.

Data used

  1. USE sql911;
  2. go
  3. SELECT counter,emp_short,trans_date,debit,credit
  4. FROM trans02
  5. ORDER BY trans_date;
  6. go
counter     emp_short trans_date              debit                 credit
----------- --------- ----------------------- --------------------- ---------------------
10          000190    2007-06-01 00:00:00.000 NULL                  125.00
20          000110    2007-06-01 00:00:00.000 NULL                  100.00
30          000190    2007-06-02 00:00:00.000 25.00                 NULL
40          000030    2007-07-15 00:00:00.000 357.00                NULL
50          000030    2007-07-16 00:00:00.000 NULL                  22.00
60          000030    2007-07-18 00:00:00.000 NULL                  41.00
70          000030    2007-07-20 00:00:00.000 NULL                  29.50
80          000110    2007-07-20 00:00:00.000 42.00                 NULL
90          000110    2007-07-22 00:00:00.000 66.00                 NULL
100         000110    2007-07-23 00:00:00.000 77.00                 NULL
110         000010    2007-07-23 00:00:00.000 11.00                 NULL
120         000010    2007-07-24 00:00:00.000 22.56                 NULL
130         000010    2007-07-25 00:00:00.000 33.69                 NULL
NULL        000070    2007-09-25 00:00:00.000 NULL                  11.11
NULL        000070    2007-09-26 00:00:00.000 NULL                  11.22
NULL        000070    2007-09-27 00:00:00.000 55.00                 NULL
NULL        000070    2007-10-01 00:00:00.000 52.50                 NULL
NULL        000160    2007-10-20 00:00:00.000 9.00                  NULL
NULL        000160    2007-10-20 01:00:00.000 11.50                 NULL
NULL        000160    2007-10-21 00:00:00.000 NULL                  45.00

(20 row(s) affected)

Grouping by week try #1

  1. SELECT DATEPART(YEAR,trans_date) AS 'Year',
  2.        DATEPART(wk,trans_date) AS 'Week #',
  3.        SUM(debit) AS 'Debits',
  4.        SUM(credit) AS 'Credits'
  5. FROM trans02
  6. GROUP BY DATEPART(YEAR,trans_date),
  7.          DATEPART(wk,trans_date)
  8. ORDER BY 1,2;
  9. go
Year        Week #      Debits                Credits
----------- ----------- --------------------- ---------------------
2007        22          25.00                 225.00
2007        29          399.00                92.50
2007        30          210.25                NULL
2007        39          55.00                 22.33
2007        40          52.50                 NULL
2007        42          20.50                 NULL
2007        43          NULL                  45.00
Warning: Null value is eliminated by an aggregate or other SET operation.

(7 row(s) affected)
  • Notice that NULL values are not including in any summary operation.

Grouping by week try #2

  1. SELECT DATEPART(YEAR,trans_date) AS 'Year',
  2.        DATEPART(wk,trans_date) AS 'Week #',
  3.        SUM(COALESCE(debit,0)) AS 'Debits',
  4.        SUM(COALESCE(credit,0)) AS 'Credits'
  5. FROM trans02
  6. GROUP BY DATEPART(YEAR,trans_date),
  7.          DATEPART(wk,trans_date)
  8. ORDER BY 1,2;
  9. go
Year        Week #      Debits                Credits
----------- ----------- --------------------- ---------------------
2007        22          25.00                 225.00
2007        29          399.00                92.50
2007        30          210.25                0.00
2007        39          55.00                 22.33
2007        40          52.50                 0.00
2007        42          20.50                 0.00
2007        43          0.00                  45.00

(7 row(s) affected)
  • By using the coalesce:
    1. We get actual amounts that make sense.
    2. We do not get any warning message.

Grouping by week try #3

We get the week #, but what are those weeks, is week 22 in May, June or July?

  1. SELECT DATEPART(YEAR,trans_date) AS 'Year',
  2.        DATEPART(wk,trans_date) AS 'Week #',
  3.        MIN(DATEADD(wk, DATEDIFF(wk,0,trans_date), 0)) AS 'Week date',
  4.        SUM(COALESCE(debit,0)) AS 'Debits',
  5.        SUM(COALESCE(credit,0)) AS 'Credits'
  6. FROM trans02
  7. GROUP BY DATEPART(YEAR,trans_date),DATEPART(wk,trans_date)
  8. ORDER BY 1,2;
  9. go
Year        Week #      Week date               Debits                Credits
----------- ----------- ----------------------- --------------------- ---------------------
2007        22          2007-05-28 00:00:00.000 25.00                 225.00
2007        29          2007-07-09 00:00:00.000 399.00                92.50
2007        30          2007-07-16 00:00:00.000 210.25                0.00
2007        39          2007-09-24 00:00:00.000 55.00                 22.33
2007        40          2007-10-01 00:00:00.000 52.50                 0.00
2007        42          2007-10-15 00:00:00.000 20.50                 0.00
2007        43          2007-10-15 00:00:00.000 0.00                  45.00

(7 row(s) affected)
  • Line 3: min(...) is to fulfill the group by clause.
  • Line 3: dateadd(wk, datediff(wk,0,trans_date), 0 gets the 1st day of that week according to the system settings.