时间区间按月计算使用天数

select 
     t3.*
    ,case
        when day_start > month_current and day_end < month_last then TIMESTAMPDIFF(DAY,day_start,day_end) + 1 -- '中间，结束时间 - 开始时间'
        when day_start > month_current then TIMESTAMPDIFF(DAY,day_start,month_last) + 1 -- '开头，当月最后一天 - 开始时间'
        when day_end < month_last      then TIMESTAMPDIFF(DAY,month_current,day_end) + 1 -- '结尾，结束时间 - 当月第一天'
        else day(month_last)  -- '全部，算全月'
    end as day_cnt -- 当月占用时间
    ,case
        when day_start > month_current and day_end < month_last then '中间，结束时间 - 开始时间'
        when day_start > month_current                          then '开头，开始时间 至 当月最后一天'
        when day_end < month_last                               then '结尾，当月第一天 至 结束时间'
        else '全部，算全月'
    end as day_cnt_flag -- 当月占用时间
from (
    select
         id
        ,t2.month                                             as month_current
        ,date_add(t2.month, interval - day(t2.month) + 1 day) as month_first
        ,LAST_DAY(t2.month)                                   as month_last
        ,t1.day_start
        ,t1.day_end
    from (
        select 
             t0.*
            ,date_add(t0.day_start, interval - day(t0.day_start) + 1 day) as day_start_first
            ,LAST_DAY(t0.day_end)                                         as day_end_last
        from (
            select 1 as id,'2024-01-01' as day_start,'2025-12-31' as day_end union all
            select 2 as id,'2023-03-11' as day_start,'2025-12-20' as day_end union all
            select 3 as id,'2024-12-02' as day_start,'2025-12-10' as day_end union all
            select 4 as id,'2024-12-03' as day_start,'2024-12-03' as day_end union all
            select 5 as id,'2024-08-17' as day_start,'2024-08-27' as day_end 
        ) t0
    ) t1
    left join (
        select 
             date_add('2022-01-01',interval row_number() over(order by ld_src_id) month) as month
            ,row_number() over(order by ld_src_id) as rn
        from dwd_cyjg_bt_progress_report_data
        limit 100
    ) t2
        on t2.month >= t1.day_start_first   and t2.month <= t1.day_end_last
) t3
order by id,month_current