0

I have a table shown below (sample) and I want to create a new table with an extra column 'NewDate' which will look at StartDate and show the last date of the month for start date and subsequently last date of every month till the end date for each ID and if my ID has End Date as Null the series will stop at the last date of current month which is May 2022.

ID     StartDate      EndDate            
100     1/01/2022     26/04/2022    
101    20/04/2022     Null
102     1/01/2022     27/02/2022          
....

I am using Postgresql and my Expected Output:

ID  StartDate       EndDate          NewDate
100     1/01/2022    26/04/2022      31/01/2022
100     1/01/2022    26/04/2022      28/02/2022
100     1/01/2022    26/04/2022      31/03/2022
100     1/01/2022    26/04/2022      30/04/2022
101     20/04/2022    Null           30/04/2022
101     20/04/2022    Null           31/05/2022
102     1/01/2022    27/02/2022      31/01/2022
102     1/01/2022    27/02/2022      28/02/2022
...
Ash
  • 319
  • 1
  • 11

1 Answers1

1

demo

(
    SELECT
        id,
        start_date,
        end_date,
        (new_date::date + interval '1 month - 1 day')::date
    FROM
        test_date,
        generate_series((date_trunc('month', start_date)), (date_trunc('month', end_date) + interval '1 month - 1 day'), interval '1 month') g (new_date)
    ORDER BY
        id)
UNION ALL ((
        SELECT
            id,
            start_date,
            end_date,
            (date_trunc('month', start_date) + interval '1 month - 1 day')::date
        FROM
            test_date
        WHERE
            test_date.end_date IS NULL)
    UNION ALL (
        SELECT
            id,
            start_date,
            end_date,
            (date_trunc('month', (date_trunc('month', start_date) + interval '1 month - 1 day')::date) + interval '2 month - 1 day')::date
        FROM
            test_date
        WHERE
            test_date.end_date IS NULL)
    ORDER BY
        id);

key gotta: How to get the last day of month in postgres? Maybe there is some simple version, but anyway this way works.

jian
  • 4,119
  • 1
  • 17
  • 32
  • Thank you @Mark but I got an error : ```Invalid operation: function generate_series(timestamp without time zone, timestamp without time zone, interval) does not exist; ``` – Ash May 22 '22 at 22:45
  • and this is a known issue : https://stackoverflow.com/questions/17282276/using-sql-function-generate-series-in-redshift, do you suggest any other work around on this one. – Ash May 22 '22 at 22:52