Postgres sql query to explode all the dates between start and end date for each customer-CodePudding

I have a table with the following columns: customerID, startdate, enddate. E.g.:

CustomerID     startdate(mm/dd/yyyy)       Enddate(mm/dd/yyyy)
c1             10-15-2020                  10-18-2020
c2             02-20-2021                  02-25-2021
c3             12-01-2021                  12-08-2021

How can I write a SQL query to explode the all the dates between start and end date for each customer respectively?

So expected output would be:

CustomerID        exploedcalendardate
c1                10-15-2020
c1                10-16-2020
c1                10-17-2020
c1                10-18-2020
c2                02-20-2021
c2                02-21-2021
c2                02-22-2021
c2                02-23-2021
c2                02-24-2021
c2                02-25-2021
c3                12-01-2021
c3                12-02-2021
c3                12-03-2021
c3                12-04-2021
c3                12-05-2021
c3                12-06-2021
c3                12-07-2021
c3                12-08-2021

CodePudding user response：

You can achieve this using generate_series() function. Here in an example

select customerID, dd::date AS exploedcalendardate 
from customer C
JOIN LATERAL generate_series(C.startdate, c.enddate, '1 day'::interval) dd ON true

Test the query here

CodePudding user response：

SELECT 'CustomerID exploedcalendardate '
    || string_agg(customerID || ' ' || startdate || ' ' || enddate, ' ')
  FROM your_table
 ORDER BY customerID