拉链表匹配筛选日期区间

select
    *
from (
    select 1 as start,5 as end union all
    select 6 as start,10 as end union all
    select 11 as start,15 as end union all
    select 17 as start,22 as end union all
    select 23 as start,30 as end
) t1
where 1 = 1
    and (
        (start >= 2 and start <= 23)  -- 起始时间与筛选范围交集
     or (end >= 2   and end <= 23)    -- 结束时间与筛选范围交集
     or (start <= 2 and end >= 23)    -- 起始时间完全包裹筛选范围
     or (start >= 2 and end <= 23)    -- 筛选范围完全包裹起始时间
    )
;

       sum(
         case 
            -- 1. 店员入职日期 小于 统计开始日期，店员离职日期 大于 统计结束日期，取 统计结束日期 - 统计开始日期
            when employee_status_name in ('正式','离职') and employee_entry_date <= '${ETL_END_MON1}' and employee_disabled_date >= '${ETL_END_DATE1}'
                then (date_diff('${ETL_END_DATE1}','${ETL_END_MON1}')+1)/(date_diff('${ETL_END_DATE1}','${ETL_END_MON1}')+1)

            -- 2. 店员入职日期 大于 统计开始日期，店员离职日期 大于 统计结束日期，取 统计结束日期 - 店员入职日期
            when employee_status_name in ('正式','离职') and employee_entry_date >= '${ETL_END_MON1}' and employee_disabled_date >= '${ETL_END_DATE1}'
                then (date_diff('${ETL_END_DATE1}',employee_entry_date)+1)/(date_diff('${ETL_END_DATE1}','${ETL_END_MON1}')+1)

            -- 3. 店员入职日期 小于 统计开始日期，店员离职日期 小于 统计结束日期，取 店员离职日期 - 统计开始日期
            when employee_status_name in ('正式','离职') and employee_entry_date <= '${ETL_END_MON1}' and employee_disabled_date <= '${ETL_END_DATE1}'
                then (date_diff(employee_disabled_date,'${ETL_END_MON1}')+1)/(date_diff('${ETL_END_DATE1}','${ETL_END_MON1}')+1)

            -- 4. 店员入职日期 大于 统计开始日期，店员离职日期 小于 统计结束日期，取 店员离职日期 - 店员入职日期
            when employee_status_name in ('正式','离职') and employee_entry_date >= '${ETL_END_MON1}' and employee_disabled_date <= '${ETL_END_DATE1}'
                then (date_diff(employee_disabled_date,employee_entry_date)+1)/(date_diff('${ETL_END_DATE1}','${ETL_END_MON1}')+1)
            else 0 
        end
    ) wt_people_qty --加权店铺员工数

【腾讯文档】拉链表匹配筛选日期区间
https://docs.qq.com/board/DRVNHT0doV2drTnZk