Create a Series of Dates between two Dates in a table

I have a table like this:

I want to list the rows per day between their Start Date and and End Date and Total Payment divided by number of days (I assume I would need a window function partition by name here). But my main concern is how to create those series of dates for each name based on their Start Date and End Date.

Using the table above I would like the output to look like this:

CodePudding user response：

Consider a range join with count window function to spread out total by days:

SELECT t."Name",
       t."Total Payment" / COUNT(dates) OVER(PARTITION BY t."Name") AS Payment,
       t."Start Date",
       t."End Date",
       dates AS "Date of"
FROM generate_series(
    timestamp without time zone '2022-01-01',
    timestamp without time zone '2022-12-31',
    '1 day'
) AS dates
INNER JOIN my_table t
   ON dates BETWEEN t."Start Date" AND t."End Date"

CodePudding user response：

You can get what your after is a single query by generate_series for getting each day, and by just subtracting the 2 dates. (Since you seem to want both dates included in the day count an additional 1 needs added).

select name, (total_payment/( (end_date-start_date)  1))::numeric(6,2), start_date, end_date, d::date date_of
  from test t
  cross join generate_series(t.start_date
                            ,t.end_date 
                            ,interval ' 1 day'
                            ) gs(d)  
order by name desc, date_of;

See demo. I leave for you what to do when the total_payment is not a multiple of the number of days. The demo just ignores it.