基于日期范围计算状态字段的客户关联产品SQL查询需求

需要创建一条SQL查询语句，返回与客户关联的产品信息，查询逻辑基于指定的日期范围变量@Startperiod和@Endperiod，并根据日期范围与产品字段的匹配规则返回对应状态。

示例数据 #

declare @Startperiod datetime= '2019-03-02'
,@Endperiod datetime = '2019-10-10'

create table #customer
(
[customer_bk] [varchar](100) NULL
,[product_bk] [varchar](100) NULL
)

insert into  #customer
(customer_bk
,product_bk
)
values ('cust_1','2-76')

create table #product
(
    [product_bk] [varchar](100) NULL,
    [valid_from] [date] NULL,
    [valid_till] [date] NULL,
    [product_type] [nvarchar](30) NULL,
    [product_number] [nvarchar](34) NULL,
    [status] [nvarchar](20) NULL,
    [start_date] [date] NULL,
    [end_date] [date] NULL
)

insert into #product
           ([product_bk]
           ,[valid_from]
           ,[valid_till]
           ,[product_type]
           ,[product_number]
           ,[status]
           ,[start_date]
           ,[end_date])
values ('2-76', '2018-11-01',   '2999-12-31',   'Hypothecair krediet',  'NL00NIBC01176' ,   'Frozen'    ,'2010-01-01',  '2999-12-31'),
       ('2-76', '2010-01-01',   '2018-11-01',   'Hypothecair krediet',  'NL00NIBC01176' ,   'Active',   '2010-01-01',   '2999-12-31')

-- 查看示例数据
select * from #customer
select * from #product

查询规则 #

• 若@Startperiod和@Endperiod均早于或晚于产品的end_date字段，返回状态为'No Data Found'
• 若@Startperiod早于end_date且@Endperiod晚于end_date，返回状态为'discontinued'
• 若@Startperiod和@Endperiod均在产品的start_date与end_date范围内，则检查日期范围是否处于valid_from与valid_till区间，返回对应有效区间的status

预期输出示例 #

示例1：日期范围在产品有效期内且匹配valid区间 #

当@Startperiod='2019-03-02'、@Endperiod='2019-10-10'时：

示例2：日期范围跨越产品end_date #

当@Startperiod='2019-03-02'、@Endperiod='2021-10-10'时：

解决方案SQL #

declare @Startperiod datetime= '2019-03-02'
,@Endperiod datetime = '2019-10-10'

select 
    c.customer_bk,
    p.product_bk,
    p.product_number,
    case
        -- 规则1：日期范围完全在end_date之外
        when (@Endperiod <= p.end_date or @Startperiod >= p.end_date) then 'No Data Found'
        -- 规则2：日期范围跨越end_date
        when (@Startperiod < p.end_date and @Endperiod > p.end_date) then 'discontinued'
        -- 规则3：日期范围在start_date和end_date内，匹配valid区间
        when (@Startperiod between p.start_date and p.end_date 
              and @Endperiod between p.start_date and p.end_date) then
            case
                when (@Startperiod between p.valid_from and p.valid_till 
                      and @Endperiod between p.valid_from and p.valid_till) then p.status
                else 'No Data Found'
            end
        else 'No Data Found'
    end as status
from #customer c
inner join #product p on c.product_bk = p.product_bk

内容的提问来源于stack exchange，提问作者Eseosa Omoregie