Site icon Laos Bet 77

Using modulo operator with Date

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.

Exit mobile version