Track the week intervals from the month start date-CodePudding

I'm trying to find how many runners signed up for each 1 week period starting from 2021-01-01. I've got no problem with the query but i'm unable to do a mod (%) with the date to reach a 7 day figure mark to round up my query.

---My Schema
CREATE TABLE runners (
  "runner_id" INTEGER,
  "registration_date" DATE
);
INSERT INTO runners
  ("runner_id", "registration_date")
VALUES
  (1, '2021-01-01'),
  (2, '2021-01-03'),
  (3, '2021-01-08'),
  (4, '2021-01-15');


runner_id   registration_date
1   2021-01-01
2   2021-01-03
3   2021-01-08
4   2021-01-15

Desired O/P:

start_of_week   signups
2021-01-01    2
2021-01-08    1
2021-01-15    1

---My Query

WITH data1 AS (
  SELECT
    runner_id,
    registration_date,
    REGISTRATION_DATE - (DATEDIFF(DAY, REGISTRATION_DATE, '2021-01-01' ) % 7)  AS start_of_week
  FROM runners
)
select start_of_week, count(runner_id) as signup from data1 group by start_of_week;

Error

Operand type clash: date is incompatible with int

I also tried switching the date formats to mod with 7 but didn't work.

 registration_date - ((registration_date - '2021-01-01') % 7)

Error

The data types date and varchar are incompatible in the subtract operator.

CodePudding user response：

as per comments, try this:

 WITH data1 AS (
  SELECT
    runner_id,
    registration_date,
    (datediff(DAY,  '2021-01-01',REGISTRATION_DATE ) / 7)   1 AS start_of_week
  FROM runners
)
select
  dateadd(week, start_of_week - 1, '2021-01-01'),
  count(runner_id) as signup
from data1
group by start_of_week;

dbfiddle

https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=91a00cf6223f3c1367b6e8f014dccd6f

you can also cast it as date if you want to get rid of the time

select
  cast(dateadd(week, start_of_week - 1, '2021-01-01') as date),
  count(runner_id) as signup
from data1
group by start_of_week;