Postgresql Generate_series. Generate dates by month

Question

I am using select

SELECT
              asl.id, asl.outstanding_principal as outstandingPrincipal, the_date as theDate, asl.interest_rate as interestRate, asl.interest_payment as interestPayment, asl.principal_payment as principalPayment,
                          asl.total_payment as totalPayment, asl.actual_delta as actualDelta, asl.outstanding_usd as outstandingUsd, asl.disbursement, asl.floating_index_rate as floatingIndexRate,
                          asl.upfront_fee as upfrontFee, asl.commitment_fee as commitmentFee, asl.other_fee as otherFee, asl.withholding_tax as withholdingTax, asl.default_fee as defaultFee,
                         asl.prepayment_fee as prepaymentFee, asl.total_out_flows as totalOutFlows, asl.net_flows as netFlows, asl.modified, asl.new_row as newRow, asl.interest_payment_modified as
                         interestPaymentModified, asl.date, asl.amortization_schedule_initial_id as amortizationScheduleInitialId, asl.tranche_id as trancheId, asl.user_id as userId, tr.local_currency_id as localCurrencyId,
              f.facility_id
            FROM
              GENERATE_SERIES
              (
                (SELECT MIN(ams.date) FROM amortization_schedules ams),
                (SELECT MAX(ams.date) + INTERVAL '1' MONTH FROM amortization_schedules ams),
                '1 MONTH'
              ) AS tab (the_date)
            FULL JOIN amortization_schedules asl on to_char(the_date, 'yyyy-mm') = to_char(asl.date, 'yyyy-mm')
            LEFT JOIN tranches tr ON asl.tranche_id = tr.id
            LEFT JOIN facilities f on tr.facility_id = f.id

In this select, I'm using generate_series to get each month since there are no records in the database for each month. But the matter is that this select gives me superfluous results. I use this select in my Spring Boot application. But the fact is that I need all the data, and for example only with a certain facility_id , and when I insert a condition

WHERE f.id = :id and tr.tranche_number_id = :trancheNumberId

My generate_series stops working (as I understand it, because I set certain conditions for generating a request) and instead of 30 lines, I get only 3.

How do I keep the ability to generate the theDate by month, with the ability to select specific IDs

I tried different options. With this option:

FULL JOIN amortization_schedules asl on to_char(the_date, 'yyyy-mm') = to_char(asl.date, 'yyyy-mm')

|  id    |  outstantandingprincipal  |            thedate         |
-------------------------------------------------------------------
|   1    |          10000            | 2022-05-16 00:00:00.000000 |
|   2    |          50000            | 2023-05-16 00:00:00.000000 |
|   3    |              0            | 2024-05-16 00:00:00.000000 |

In this case, it does not work correctly, since months are not generated and only three lines are displayed (if it is (the_date, 'yyyy-MM') = to_char(asl.date, 'yyyy-MM')).

If I change to (the_date, 'yyyy') = to_char(asl.date, 'yyyy') then the generation works, but it doesn't work correctly because it is year oriented.

|  id    |  outstantandingprincipal  |            thedate         |
-------------------------------------------------------------------
|   1    |          10000            | 2022-05-16 00:00:00.000000 |
|   1    |          10000            | 2022-06-16 00:00:00.000000 |
|   1    |          10000            | 2022-06-16 00:00:00.000000 |
|   1    |          10000            | 2022-07-16 00:00:00.000000 |
   ...               ...                       ....
|   1    |          10000            | 2022-12-16 00:00:00.000000 |
|   2    |          50000            | 2023-01-16 00:00:00.000000 |
|   2    |          50000            | 2023-02-16 00:00:00.000000 |
|   2    |          50000            | 2023-03-16 00:00:00.000000 |
|   2    |          50000            | 2023-04-16 00:00:00.000000 |
    ...               ...                      ....
|   3    |              0            | 2024-01-16 00:00:00.000000 |

but it should be:

|  id    |  outstantandingprincipal  |            thedate         |
-------------------------------------------------------------------
|   1    |          10000            | 2022-05-16 00:00:00.000000 |
|   1    |          10000            | 2022-06-16 00:00:00.000000 |
|   1    |          10000            | 2022-06-16 00:00:00.000000 |
|   1    |          10000            | 2022-07-16 00:00:00.000000 |
   ...               ...                       ....
|   1    |          10000            | 2023-04-16 00:00:00.000000 |
|   2    |          50000            | 2023-05-16 00:00:00.000000 |
|   2    |          50000            | 2023-06-16 00:00:00.000000 |
|   2    |          50000            | 2023-07-16 00:00:00.000000 |
|   2    |          50000            | 2023-08-16 00:00:00.000000 |
    ...               ...                      ....
|   3    |              0            | 2024-05-16 00:00:00.000000 |
|   3    |              0            | 2024-06-16 00:00:00.000000 |
|   3    |              0            | 2024-07-16 00:00:00.000000 |

Hambone · Answer

I'm making a few intuitive leaps here, so if something looks off it might be because I don't have the entire picture.

From what I can tell you want the amortization schedule starting from the "date" for each ID and then going out a specific amount of time. I am guessing it is not truly the max date in that entire table, and that it varies by ID. In your example you went out one year, so for now I'm going with that.

You can use a generate_series inline, which will explode out each row. I believe something like this will give you the output you seek:

with schedule as (
  select
    id,
    generate_series (date, date + interval '1 year', interval '1 month')::date as dt 
  from
    amortization_schedules
)
select
  asl.id, s.dt, asl.outstanding_principal
from 
  amortization_schedules asl
  join schedule s on asl.id = s.id
  JOIN tranches tr ON asl.tranche_id = tr.id
  JOIN facilities f on tr.facility_id = f.id
WHERE
  f.id = :id and 
  tr.tranche_number_id = :trancheNumberId

Is there another field that tells, by id, when the payments should end or one that will let us derive it (number of payments, payment end date, etc)?

One final note. If you use [left] outer joins and a where clause, as in below:

  LEFT JOIN tranches tr ON asl.tranche_id = tr.id
  LEFT JOIN facilities f on tr.facility_id = f.id
WHERE
  f.id = :id and 
  tr.tranche_number_id = :trancheNumberId

You have effectively nullified the "left" and made these inner joins. In this case, get rid of "left," not because it will return wrong results but because it misleads. You are saying those fields must have those specific values, which means they must first exist. That's an inner join.

If you truly wanted these as left joins, this would have been more appropriate, but I don't think this is what you meant:

  LEFT JOIN tranches tr ON
      asl.tranche_id = tr.id and 
      tr.tranche_number_id = :trancheNumberId  
  LEFT JOIN facilities f on 
      tr.facility_id = f.id and 
      f.id = :id

Postgresql Generate_series. Generate dates by month

1 Answers