Reminders System

SQL Query to Pull Reminders

WITH next_holiday AS (
   SELECT
       "H"."event" AS "holiday_event",
       MIN("H"."date") AS "next_holiday_date"
   FROM
       "Holiday" "H"
   WHERE
       "H"."date" > CURRENT_DATE
   GROUP BY
       "H"."event"
)
SELECT DISTINCT ON ("A"."firstName", "next_event_date")
   "R"."userId",
   "A"."firstName",
   "A"."lastName",
   "R"."event",
   "U"."email",
   CASE
       WHEN "R"."event" = 'BIRTHDAY' THEN
           CASE
               WHEN EXTRACT(MONTH FROM "R"."date") < EXTRACT(MONTH FROM CURRENT_DATE) OR
                    (EXTRACT(MONTH FROM "R"."date") = EXTRACT(MONTH FROM CURRENT_DATE) AND EXTRACT(DAY FROM "R"."date") < EXTRACT(DAY FROM CURRENT_DATE))
               THEN
                   (EXTRACT(YEAR FROM CURRENT_DATE) + 1) || '-' || EXTRACT(MONTH FROM "R"."date") || '-' || EXTRACT(DAY FROM "R"."date")
               ELSE
                   EXTRACT(YEAR FROM CURRENT_DATE) || '-' || EXTRACT(MONTH FROM "R"."date") || '-' || EXTRACT(DAY FROM "R"."date")
           END::DATE
       WHEN "R"."event" = 'ANNIVERSARY' THEN
           CASE
               WHEN EXTRACT(MONTH FROM "R"."date") < EXTRACT(MONTH FROM CURRENT_DATE) OR
                    (EXTRACT(MONTH FROM "R"."date") = EXTRACT(MONTH FROM CURRENT_DATE) AND EXTRACT(DAY FROM "R"."date") < EXTRACT(DAY FROM CURRENT_DATE))
               THEN
                   (EXTRACT(YEAR FROM CURRENT_DATE) + 1) || '-' || EXTRACT(MONTH FROM "R"."date") || '-' || EXTRACT(DAY FROM "R"."date")
               ELSE
                   EXTRACT(YEAR FROM CURRENT_DATE) || '-' || EXTRACT(MONTH FROM "R"."date") || '-' || EXTRACT(DAY FROM "R"."date")
           END::DATE
       ELSE
           "NH"."next_holiday_date"
   END AS "next_event_date",
   "A"."state",
   "R"."createdAt"
FROM
   "ScheduledEvent" "R"
LEFT JOIN
   next_holiday "NH" ON "R"."event" = "NH"."holiday_event"
JOIN
   "Address" "A" ON "R"."userId" = "A"."userId"
JOIN
   "UserProfile" "U" ON "R"."userId" = "U"."id"
LEFT JOIN
   "Subscription" "S" ON "R"."userId" = "S"."userId"
WHERE
   "R"."archived" = FALSE
   AND "A"."firstName" <> 'Test'
ORDER BY
  "next_event_date";

Example Output

First Name	Last Name	Occasion	Email	Date	State	Created At
Ashley	Baldwin	ANNIVERSARY	kmarkmann.1@gmail.com	2024-09-08 00:00:00.000	ME	2024-05-08 14:55:02.431
Andrew	Potter	BIRTHDAY	apottere@gmail.com	2024-10-31 00:00:00.000	VT	2024-05-10 00:34:34.264
John	Warner	DIWALI	mjwarren3@gmail.com	2024-10-31 05:00:00.000	DC	2024-06-03 14:19:48.783
John	Warner	HALLOWEEN	mjwarren3@gmail.com	2024-10-31 22:00:00.000	DC	2024-06-03 14:19:50.532