该SQL可以实现一条记录变成多条记录
比如项目计划开始时间 1月1日,项目实际开始时间 4月1日,则会产生4条数据,1月延期31天,2月延期 28天,3 月延期31天
4月延期1天
select p.name as 项目名称,a.*,
case when a.计划开始月份=a.实际开始月份 then date_part('day', cast(a.实际开始 as TIMESTAMP)- cast(a.计划开始 as TIMESTAMP))
when a.实际开始>a.最后一天 and a.中间=a.计划开始 then date_part('day', cast(a.最后一天 as TIMESTAMP)- cast(a.计划开始 as TIMESTAMP))
when a.实际开始>a.最后一天 and a.中间<>a.计划开始 and a.中间月<>a.实际开始月份 then date_part('day', cast(a.最后一天 as TIMESTAMP)- cast(a.中间月 as TIMESTAMP))+1
when a.实际开始月份=a.中间月 and a.计划开始月份<>a.实际开始月份 then date_part('day', cast(a.实际开始 as TIMESTAMP)- cast(a.实际开始月份 as TIMESTAMP))+1
else 0 end as 当月间隔
from (
select name
plan_start_time as 计划开始,to_char(plan_start_time,'YYYY-MM-01') AS 计划开始月份,
act_start_time as 实际开始,to_char(act_start_time,'YYYY-MM-01') AS 实际开始月份,
generate_series(plan_start_time,act_start_time,'1 month' ) as 中间,
to_char(generate_series(plan_start_time,act_start_time,'1 month' ),'YYYY-MM-01') AS 中间月,
(date_trunc('month',generate_series(plan_start_time,act_start_time,'1 month' )) + interval '1 month - 1 day')::date as 最后一天
from
project_list) as a
left join projects as p
on a.name=p.id::VARCHAR