Sum all the repeat event based on dates, aggregate by 7 days ,30 days >30 days

Juststarting 0
Viewed 33

I am trying to calculate repeat if there is a repeat event in 3,7,30 and >30 days.

In the image below the the yellow is the sql table, the green is transformation needed, where I find out what is the first event for Event A and Event B. and then find out what is the gap between the first event of A and next events of A.

Finally I need to aggregate and achieve the blue table where data is aggregate for the unique events.

I have been trying to achieve this in SQL but I am stuck as I am not sure how to filter and loop.

Original data and Expected outcome image

aggregate sql
1 Answers
DECLARE @reference_date DATE = '2022-08-02';
SELECT
  Event,
  MIN(Date)  as First_date,
  SUM(CASE WHEN DATEDIFF(day, @reference_date, Date) BETWEEN 1 AND 2
       THEN 1 ELSE 0 END) as "Within_3_Days",
  SUM(CASE WHEN DATEDIFF(day, @reference_date, Date) BETWEEN 1 AND 6
       THEN 1 ELSE 0 END) as "Within_7_Days",
  SUM(CASE WHEN DATEDIFF(day, @reference_date, Date) BETWEEN 1 AND 29
       THEN 1 ELSE 0 END) as "Within_30_Days",
  SUM(CASE WHEN DATEDIFF(day, @reference_date, Date)>=30
       THEN 1 ELSE 0 END) as ">_30_Days"
FROM event e0
GROUP BY Event

output:

Event First_date Within_3_Days Within_7_Days Within_30_Days >_30_Days
A 2022-08-01 0 1 2 1
B 2022-09-15 0 0 0 1
  • The @reference_date is used to reference the date needed to determine if a date is within x days.

DBFIDDLE

P.S. I use dates in the format YYYY-MM-DD, because that's the only way I am SURE about the ordering of the Day and the Month part.

EDIT: When using the first date of an event to determine the 'within' columns, you can do:

SELECT
  e0.Event,
  MIN(e0.Date)  as First_date,
  SUM(CASE WHEN DATEDIFF(day, e1.Date, e0.Date) BETWEEN 1 AND 2
       THEN 1 ELSE 0 END) as "Within_3_Days",
  SUM(CASE WHEN DATEDIFF(day, e1.Date, e0.Date) BETWEEN 1 AND 6
       THEN 1 ELSE 0 END) as "Within_7_Days",
  SUM(CASE WHEN DATEDIFF(day, e1.Date, e0.Date) BETWEEN 1 AND 29
       THEN 1 ELSE 0 END) as "Within_30_Days",
  SUM(CASE WHEN DATEDIFF(day, e1.Date, e0.Date)>=30
       THEN 1 ELSE 0 END) as ">_30_Days"
FROM event e0
INNER JOIN (SELECT Event,MIN(Date) as Date from event GROUP BY Event) e1 on e1.Event=e0.Event
GROUP BY e0.Event

see: DBFIDDLE2

Luuk0
Related