Here is the working version:
- Code: Select all
WITH RECURSIVE subs(id, startdate, recurring_in_months, amount, recurrence) AS (
SELECT id, startdate, recurring_in_months, amount, 1
FROM subscription
UNION ALL
SELECT s.id, s.startdate + (s.recurrence * s.recurring_in_months * interval '1 month'), s.recurring_in_months, s.amount, s.recurrence+1
FROM subs s
WHERE s.startdate + (s.recurrence * s.recurring_in_months * interval '1 month') BETWEEN ? AND ?
)
SELECT id, sum(amount)
FROM subs
WHERE startdate BETWEEN ? AND ?
GROUP BY id