Expected payments by day given start and end date

Question

I'm trying to create a SQL view that gives me the expected amount to be received by calendar day for recurring transactions. I have a table containing recurring commitments data, with the following columns:

id, 
start_date, 
end_date (null if still active), 
payment day (1,2,3,etc.), 
frequency (monthly, quarterly, semi-annually, annually), 
commitment amount

For now, I do not need to worry about business days vs calendar days.

In its simplest form, the end result would contain every historical calendar day as well as future dates for the next year, and produce how much was/is expected to be received in those particular days.

I've done quite a bit of researching, but cannot seem to find an answer that addresses the specific problem. Any direction on where to start would be greatly appreciated.

The expect output would look something like this:

| Date    | Expected Amount |

|1/1/18   |  100  | 
|1/2/18   |  200  | 
|1/3/18   |  150  | 

Thank you ahead of time!

Link to data table in db-fiddle
Expected Output Spreadsheet

Answer 1

It's something like this, but I've never used Netezza

SELECT 
  cal.d, sum(r.amount) as expected_amount
FROM
  (
    SELECT MIN(a.start_date) + ROW_NUMBER() OVER(ORDER BY NULL) as d
    FROM recurring a, recurring b, recurring c
  )  cal 
  LEFT JOIN
  recurring r
  ON 
    (
     (r.frequency = 'monthly' AND r.payment_day = DATE_PART('DAY', cal.d)) OR 
     (r.frequency = 'annually' AND DATE_PART('MONTH', cal.d) = DATE_PART('MONTH', r.start_date) AND r.payment_day = DATE_PART('DAY', cal.d))
    ) AND 
    r.start_date >= cal.d AND 
    (r.end_date <= cal.d OR r.end_date IS NULL)
GROUP BY cal.d

In essence, we cartesian join our recurring table together a few times to generate a load of rows, number them and add the number onto the min date to get an incrementing date series.

The payments data table is left joined onto this incrementing date series on:

• (the day of the date from the series) = (the payment day) for monthlies
• (the month-day of the date from the series) = (the month and payment day of the start_date)

Finally, the whole lot is grouped and summed

I don't have a test instance of Netezza so if you encounter some minor syntax errors, do please have a stab at fixing them up yourself (to make it faster for you to get a solution). If you reach a point where you can't work out what the query is doing, let me know