I have the below table (dates_table), that has rows that look like the below, for example with start_date = '2022-01-25' and end_date = '2022-02-04'.

and I'm trying to create a new table from the above and add another column days_count, that takes the start_date 1/25, sees how many days are in that month (so 7 in first row because 1/25, 1/26, 1/27, 1/28, 1/29, 1/30, 1/31) and 4 in the next row because 4 days for 2/1 - 2/4:

CodePudding user response：

You should add another column to indicate the type of count you're calculating, but you can accomplish this with datediff,last_day, and date_trunc(to get first of month). Here is an example

set start_date='2022-01-25'::date;
set end_date='2022-02-04'::date;

select $start_date,
       $end_date,
       'Start_Remainder' as label,
       datediff(day,$start_date,last_day($start_date)) 1 as days_count
union all
select $start_date,
       $end_date,
       'End_Accrued' as label,
       datediff(day,date_trunc('month',$end_date),$end_date) 1 as days_count

If I were you, I would have the counts in separate columns

select $start_date,
       $end_date,
       datediff(day,$start_date,last_day($start_date)) 1 as Start_Remainder_Days,
       datediff(day,date_trunc('month',$end_date),$end_date) 1 as End_Accrued_Days

CodePudding user response：

With one CTE to provide the data, and a couple of pre-conditioning CTE's

WITH data_table(start_date, end_date) as (
    SELECT * from values
        ('2022-01-25'::date, '2022-02-04'::date),
        ('2018-08-31'::date, '2018-10-05'::date)
), large_range as (
    SELECT row_number() over (order by null)-1 as rn
    FROM table(generator(ROWCOUNT => 1000))
), pre_condition as (
    SELECT 
        start_date
        ,end_date
        ,datediff('month', start_date, end_date) as m
    FROM data_table
)
SELECT
    date_trunc('month', dateadd('month', r.rn, d.start_date)) as month
    ,d.start_date
    ,d.end_date
    ,datediff('day', greatest(d.start_date, month), least(d.end_date, dateadd('day', -1, dateadd('month', 1, month)))) 1 as days_count
FROM pre_condition as d
JOIN large_range as r ON r.rn <= d.m
ORDER BY d.start_date, month;

gives:

And if you don't like the look for CTE's

SELECT
    date_trunc('month', dateadd('month', r.rn, d.start_date)) as month
    ,d.start_date
    ,d.end_date
    ,datediff('day', greatest(d.start_date, month), least(d.end_date, dateadd('day', -1, dateadd('month', 1, month)))) 1 as days_count
FROM (
    SELECT 
        start_date
        ,end_date
        ,datediff('month', start_date, end_date) as m
    FROM data_table
) as d
JOIN (
    SELECT row_number() over (order by null)-1 as rn
    FROM table(generator(ROWCOUNT => 1000))
) as r ON r.rn <= d.m
ORDER BY d.start_date, month;