PostgreSQL - assign exact date based on day of month-CodePudding

I don't even know where to start with this one, or if it's even possible to do in PostgreSQL (or if I'll just need to handle it in my code)

I have a table of Bills, which specify which day of the month each one is due

id	bill_name	day_of_month
1	Mortgage	5
2	Car Payment	11
3	Water	14
4	Electricity	27

and I have a table of 4 week Periods

id	start	end
1	07 Sep 2022	04 Oct 2022
2	05 Sep 2022	01 Nov 2022
3	02 Nov 2022	29 Dec 2022

I want to join the two tables together and determine if each bill falls within the start and end of a period and then assign its exact date

I want to end up with an exact_date column

start	end	bill_name	day_of_month	exact_date
07 Sep 2022	04 Oct 2022	Car Payment	11	11 Sep 2022
07 Sep 2022	04 Oct 2022	Water	14	14 Sept 2022
07 Sep 2022	04 Oct 2022	Electricity	27	27 Sep 2022
05 Oct 2022	01 Nov 2022	Mortgage	5	5 Oct 2022
05 Oct 2022	01 Nov 2022	Car Payment	11	11 Oct 2022
05 Oct 2022	01 Nov 2022	Water	14	14 Oct 2022
05 Oct 2022	01 Nov 2022	Electricity	27	27 Oct 2022
02 Nov 2022	29 Dec 2022	Mortgage	5	5 Nov 2022
02 Nov 2022	29 Dec 2022	Car Payment	11	11 Nov 2022
02 Nov 2022	29 Dec 2022	Water	14	14 Nov 2022
02 Nov 2022	29 Dec 2022	Electricity	27	27 Nov 2022

Is this possible? Any help in the right direction would be greatly appreciated

CodePudding user response：

Use generate_series():

select p.start, p.end, b.bill_name, b.day_of_month, 
       gs.exact_date::date
  from periods p
       cross join lateral generate_series(
                            p.start, p.end, interval '1 day') as gs(exact_date)
       join bills b on b.day_of_month = extract(day from gs.exact_date)
 order by exact_date;

Fiddle here

CodePudding user response：

You can cross join the tables and add the exact date(-1)

SELECT "start", "end","bill_name", "day_of_month" ,date_trunc('month', "start")::date   "day_of_month" - 1
FROM periods CROSS JOIN due

| start               | end                  | bill\_name | day\_of\_month | ?column? |
|:--------------------|:---------------------|:-----------|---------------:|:---------|
| 2022-09-07 02:00:00 | 2022-10-04 02:00:00  | Mortgage    | 5             | 2022-09-05 |
| 2022-09-07 02:00:00 | 2022-10-04 02:00:00  | Car Payment | 11            | 2022-09-11 |
| 2022-09-07 02:00:00 | 2022-10-04 02:00:00  | Water       | 14            | 2022-09-14 |
| 2022-09-07 02:00:00 | 2022-10-04 02:00:00  | Electricity | 27            | 2022-09-27 |
| 2022-10-05 02:00:00 | 2022-11-01 01:00:00  | Mortgage    | 5             | 2022-10-05 |
| 2022-10-05 02:00:00 | 2022-11-01 01:00:00  | Car Payment | 11            | 2022-10-11 |
| 2022-10-05 02:00:00 | 2022-11-01 01:00:00  | Water       | 14            | 2022-10-14 |
| 2022-10-05 02:00:00 | 2022-11-01 01:00:00  | Electricity | 27            | 2022-10-27 |
| 2022-11-02 01:00:00 | 2022-12-29 01:00:00  | Mortgage    | 5             | 2022-11-05 |
| 2022-11-02 01:00:00 | 2022-12-29 01:00:00  | Car Payment | 11            | 2022-11-11 |
| 2022-11-02 01:00:00 | 2022-12-29 01:00:00  | Water       | 14            | 2022-11-14 |
| 2022-11-02 01:00:00 | 2022-12-29 01:00:00  | Electricity | 27            | 2022-11-27 |

SELECT 12

fiddle