SQL查询中valid列针对日期范围返回NULL值的问题排查

问题描述 #

我有一个包含MinStation和MaxStation列的数据集，尝试在查询中创建valid列，为TransactionDate在指定日期范围内的所有条目赋值0，但该列始终返回NULL。

当前查询语句：

select
    TransactionId
    ,ProductId
    ,StepId 
    ,TransactionDate
    ,ProductStation
    ,StartDate
    ,EndDate
    ,MinStation
    ,MaxStation
    ,case
        when TransactionDate between MinStation and MaxStation then 0
    end as valid
from 
    #temp
order by
    ProductId
    ,TransactionDate asc
    ,ProductStation asc

临时表创建与填充语句：

create table #temp(
    TransactionId int
    ,ProductId varchar(9)   
    ,StepId int
    ,TransactionDate datetime
    ,ProductStation varchar(5)
    ,StartDate datetime
    ,EndDate datetime
    ,MinStation datetime
    ,MaxStation datetime
)
go

insert into #temp values
(13570,'1001LX100',43,'2024-07-11 17:06:00.000','01: A','2024-07-11 17:06:00.000','7/11/24 17:06',NULL,NULL)
,(15189,'1001LX100',41,'2024-07-15 09:46:00.000','02: B','2024-07-15 09:46:00.000','7/15/24 9:46',NULL,NULL)
,(15427,'1001LX100',57,'2024-07-15 10:21:00.000','05: E','2024-07-15 10:21:00.000','7/15/24 10:21','7/15/24 10:21',NULL)
,(32539,'1001LX100',14,'2024-07-31 09:40:00.000','03: C','2024-07-31 09:40:00.000','7/31/24 9:40',NULL,NULL)
,(32652,'1001LX100',15,'2024-07-31 10:56:00.000','04: D','2024-07-31 10:56:00.000','7/31/24 10:56',NULL,NULL)
,(33360,'1001LX100',58,'2024-07-31 12:40:00.000','04: D','2024-07-31 12:40:00.000','7/31/24 12:40',NULL,'7/31/24 12:40')
,(33485,'1001LX100',60,'2024-07-31 14:20:00.000','08: H','2024-07-31 14:20:00.000','7/31/24 14:20',NULL,NULL)
,(33486,'1001LX100',56,'2024-07-31 14:20:00.000','08: H','2024-07-31 14:20:00.000','7/31/24 14:20',NULL,NULL)
,(36339,'1001LX100',46,'2024-08-02 16:14:00.000','09: I','2024-08-02 16:14:00.000','8/2/24 16:14',NULL,NULL)
,(14458,'2001LX240',43,'2024-07-12 13:02:00.000','01: A','2024-07-12 13:02:00.000','7/12/24 13:02',NULL,NULL)
,(17324,'2001LX240',41,'2024-07-17 08:26:00.000','02: B','2024-07-17 08:26:00.000','7/17/24 8:26',NULL,NULL)
,(17453,'2001LX240',14,'2024-07-17 09:20:00.000','03: C','2024-07-17 09:20:00.000',NULL,NULL,NULL)
,(17483,'2001LX240',15,'2024-07-17 09:45:00.000','04: D','2024-07-17 09:45:00.000',NULL,NULL,NULL)
,(20757,'2001LX240',57,'2024-07-19 10:20:00.000','05: E','2024-07-19 10:20:00.000','7/19/24 10:20','7/19/24 10:20',NULL)
,(26614,'2001LX240',58,'2024-07-25 11:32:00.000','04: D','2024-07-25 11:32:00.000',NULL,NULL,'7/25/24 11:32')
,(31988,'2001LX240',60,'2024-07-30 16:03:00.000','08: H','2024-07-30 16:03:00.000','7/30/24 16:03',NULL,NULL)
,(31990,'2001LX240',56,'2024-07-30 16:04:00.000','08: H','2024-07-30 16:04:00.000','7/30/24 16:04',NULL,NULL)
,(32483,'2001LX240',46,'2024-07-31 09:15:00.000','09: I','2024-07-31 09:15:00.000','7/31/24 9:15',NULL,NULL)
,(77245,'3001LX333',43,'2024-09-17 16:06:00.000','01: A','2024-09-17 16:06:00.000','9/17/24 16:06',NULL,NULL)
,(77270,'3001LX333',41,'2024-09-17 16:30:00.000','02: B','2024-09-17 16:30:00.000','9/17/24 16:30',NULL,NULL)
,(77295,'3001LX333',14,'2024-09-17 16:48:00.000','03: C','2024-09-17 16:48:00.000',NULL,NULL,NULL)
,(77309,'3001LX333',15,'2024-09-17 17:04:00.000','04: D','2024-09-17 17:04:00.000',NULL,NULL,NULL)
,(80851,'3001LX333',57,'2024-09-20 15:21:00.000','05: E','2024-09-20 15:21:00.000','9/20/24 15:21','9/20/24 15:21',NULL)
,(89269,'3001LX333',58,'2024-10-01 11:08:00.000','04: D','2024-10-01 11:08:00.000','10/1/24 11:08',NULL,'10/1/24 11:08')
,(89857,'3001LX333',60,'2024-10-01 15:22:00.000','08: H','2024-10-01 15:22:00.000','10/1/24 15:22',NULL,NULL)
,(89858,'3001LX333',56,'2024-10-01 15:22:00.000','08: H','2024-10-01 15:22:00.000','10/1/24 15:22',NULL,NULL)
,(90096,'3001LX333',46,'2024-10-02 08:23:00.000','09: I','2024-10-02 08:23:00.000','10/2/24 8:23',NULL,NULL)

预期结果：每个ProductId下，TransactionDate处于该产品对应的全局日期范围（该产品所有非NULL的MinStation最小值到MaxStation最大值）内的条目，valid列赋值为0，其余条目赋值为1。

错误原因 #

1. CASE语句缺少ELSE分支：原查询仅在满足条件时返回0，其他情况（包括条件不成立、MinStation/MaxStation为NULL）都会返回NULL，未定义默认值。
2. 范围判断逻辑错误：原查询使用每行自身的MinStation和MaxStation进行判断，但这些字段大多为NULL，且实际需求是基于每个ProductId的全局日期范围，而非单行字段值。

修正后的查询 #

select
    t.TransactionId
    ,t.ProductId
    ,t.StepId 
    ,t.TransactionDate
    ,t.ProductStation
    ,t.StartDate
    ,t.EndDate
    ,t.MinStation
    ,t.MaxStation
    ,case
        when t.TransactionDate between p.ProductMinStation and p.ProductMaxStation then 0
        else 1
    end as valid
from 
    #temp t
cross apply (
    select 
        MIN(MinStation) as ProductMinStation,
        MAX(MaxStation) as ProductMaxStation
    from #temp
    where ProductId = t.ProductId
) p
order by
    t.ProductId
    ,t.TransactionDate asc
    ,t.ProductStation asc

说明 #

• 使用CROSS APPLY为每个ProductId计算全局的最小MinStation和最大MaxStation，确保范围覆盖整个产品的有效区间。
• 为CASE语句添加ELSE 1分支，确保不满足条件的条目返回1，而非NULL。
• 修正后可实现：产品交易日期在全局范围内时valid为0，否则为1，完全符合预期结果。

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