I have select:
SELECT date, outstanding_principal FROM amortization_schedules GROUP BY date,outstanding_principal ORDER BY date.
It gives the following result:
| date | outstanding_principal |
+----------------------------+-----------------------------+
| 2022-05-16 00:00:00.000000 | 56000 |
| 2022-06-20 00:00:00.000000 | 90000 |
| 2022-08-01 00:00:00.000000 | 80000 |
| 2022-08-03 00:00:00.000000 | 3000000 |
| .... | ..... |
| 2023-05-16 00:00:00.000000 | 28000 |
........
and also Generate_series, which generates a range of all dates from a table
following result:
| date |
+----------------------------+
| 2022-05-16 00:00:00.000000 |
| 2022-06-16 00:00:00.000000 |
| 2022-07-16 00:00:00.000000 |
| 2022-08-16 00:00:00.000000 |
| .... |
| 2023-05-16 00:00:00.000000 |
........
In the first select, I deliberately left two numbers - 56000 and 28000. They have one id, and they also have one month and day (except for the year)
Now when I want to combine these two selects into one For example
SELECT x.date, asl.outstanding_principal
FROM (
SELECT generate_series(min(a.date), max(a.date) + INTERVAL '1' MONTH, '1 MONTH')::date AS date
FROM amortization_schedules a
) x
LEFT JOIN amortization_schedules asl USING (date)
GROUP BY x.date, asl.outstanding_principal
ORDER BY x.date;
I get the result, not the one I need
| date | outstanding_principal |
+----------------------------+-----------------------------+
| 2022-05-16 00:00:00.000000 | 56000 |
| 2022-06-16 00:00:00.000000 | <null> |
| 2022-07-16 00:00:00.000000 | <null> |
| 2022-08-16 00:00:00.000000 | <null> |
| .... | ..... |
| 2023-05-16 00:00:00.000000 | 8000 |
........
As I already said, 56000 and 28000 have one ID and they have a similar date (except for the year) If I take separately generate_series this select gives me a date ending with the 16th. And the combined select, as I understand it, is guided by this date, I form data only for these numbers. How can I change my select so that for each id (I did not specify the id in the select), I would form my own range. That is something like this:
| date | outstanding_principal |
+----------------------------+-----------------------------+
| 2022-05-16 00:00:00.000000 | 56000 |
| 2022-06-16 00:00:00.000000 | <null> |
| 2022-07-16 00:00:00.000000 | <null> |
| 2022-08-16 00:00:00.000000 | <null> |
| .... | ..... |
| 2023-05-16 00:00:00.000000 | 8000 |
........
| 2022-06-20 00:00:00.000000 | 90000 |
| 2022-07-20 00:00:00.000000 | <null> |
| 2022-08-20 00:00:00.000000 | <null> |
| 2022-09-20 00:00:00.000000 | <null> |
.... ...
| 2023-02-20 00:00:00.000000 | 21000 |
and so on for each id