跟踪零售店所有权随时间的变化

Question

Sample Data Set and Desired Query Result

我有一份随时间改变所有权的零售店清单(从公司所有，到特许或特许经营，再回到公司所有)。我正在尝试跟踪每个所有权类型下每个月新开的商店的数量。对于所有权的每次更改，我在数据集中都有一个新的行项，每个行项都有相应的更改开始日期和结束日期。

我正在尝试为每个月的数据按所有权类型计算门店数量，但我在计算所有权开始日期和结束日期之间的所有月份的门店数量时遇到了麻烦。希望这张照片能让我清楚地知道我想做什么。

select
    b.fscl_yr_num
    ,b.fscl_per_in_yr_num
    ,a.ownr_type_cd
    ,sum(case when a.line_start_dt < b.end_dt and a.line_end_dt <= b.End_Dt then 1 else 0 end)
from
(
    (
    select *
    from
        (select
            store_num
            ,ownr_type_cd
            ,case when store_term_dt is not null then 'Closed' else 'Open' end as Status
            ,case when to_date(trim(store_open_dt),'DD-MON-YY') > to_date(trim(eff_from_dt),'DD-MON-YY') then to_date(trim(store_open_dt),'DD-MON-YY') else to_date(trim(eff_from_dt),'DD-MON-YY') end as Line_Start_Dt
            ,case when store_term_dt is null then eff_to_dt 
                when to_date(trim(store_term_dt),'DD-MON-YY') < to_date(trim(eff_to_dt),'DD-MON-YY') then to_date(trim(store_term_dt),'DD-MON-YY') else to_date(trim(eff_to_dt),'DD-MON-YY') end as Line_End_Dt
        from
            (select
                store_num
                ,store_open_dt
                ,store_term_dt
                ,eff_from_dt
                ,eff_to_dt
                ,ownr_type_cd
            from
                appca.d_store_vers
            where
                upper(cntry_cd_2_dgt_iso) = 'GB'
                and postal_cd not like ('BT%')
                and store_open_dt is not null
            group by
                store_num
                ,store_open_dt
                ,store_term_dt
                ,eff_from_dt
                ,eff_to_dt
                ,ownr_type_cd
            order by
              store_num
              ,eff_from_dt)
        group by
            store_num
            ,ownr_type_cd
            ,case when store_term_dt is not null then 'Closed' else 'Open' end
            ,case when to_date(trim(store_open_dt),'DD-MON-YY') > to_date(trim(eff_from_dt),'DD-MON-YY') then to_date(trim(store_open_dt),'DD-MON-YY') else to_date(trim(eff_from_dt),'DD-MON-YY') end
            ,case when store_term_dt is null then eff_to_dt 
                when to_date(trim(store_term_dt),'DD-MON-YY') < to_date(trim(eff_to_dt),'DD-MON-YY') then to_date(trim(store_term_dt),'DD-MON-YY') else to_date(trim(eff_to_dt),'DD-MON-YY') end
        order by
            1 asc
            ,2 asc
            ,3 asc)
    where
        to_date(trim(line_start_dt),'DD-MON-YY') < to_date(trim(line_end_dt),'DD-MON-YY')
    ) A
right join

    --Calendar Table--
    (
    select 
        fscl_yr_num, fscl_per_in_yr_num, Cal_dt min(to_date(trim(cal_dt),'DD-MON-YY')) as Start_Dt, max(to_date(trim(cal_dt),'DD-MON-YY')) as End_Dt
    from
        appca.d_cal
    where
        fscl_yr_num is between 1990 and 2018
    group by
        fscl_yr_num, fscl_per_in_yr_num 
    order by 
        1 asc, 2 asc
    ) B
on A.line_end_dt = B.cal_dt
)
group by
    b.fscl_yr_num
    ,b.fscl_per_in_yr_num
    ,a.ownr_type_cd
order by
    b.fscl_yr_num
    ,b.fscl_per_in_yr_num
;

Answer 1

尝试此案例：

sum(case when createTime >(cast(year(createTime) as varchar) +'-'+cast( MONTH(createTime) as varchar)+'-1') and createTime <(cast(year(createTime) as varchar) +'-'+cast( MONTH(createTime) as varchar)+'-31')
then 1 else 0 end) 'Company'