查询在指定日期范围内处于活动状态的记录

我有一个表，记录项目的某个字段更改的任何时间，以及更改的日期。我需要查询数据，以查找在请求的日期范围内任何时候该字段具有特定值的所有项目。

换句话说，如果项目在数据范围的开始、结束或任何时候都具有该值，则应将其包括在内。

示例数据：

Item  Valid  Date Changed
----  -----  ------------
A     Yes    2015-01-01
B     No     2015-01-01
B     Yes    2017-03-01
C     Yes    2015-01-01
C     No     2017-04-01
D     No     2015-01-01
D     Yes    2017-05-01
D     No     2017-06-01
E     Yes    2015-01-01
E     No     2017-05-01
E     Yes    2017-06-01
F     Yes    2015-01-01
F     No     2018-02-01
G     Yes    2017-12-31
V     No     2015-01-01
V     Yes    2018-02-01
W     Yes    2015-01-01
W     No     2016-01-01
X     No     2015-01-01
Y     Yes    2018-01-01
Z     Yes    2015-01-01
Z     No     2017-01-01

因此，如果我需要2017年有效的所有物品，查询将包括：

A(自2015年起生效)
B(2017年生效)
C(有效期至2017年年中)
D(2017年有效期为一个月)
E(2017年初和年末有效)
F(2017年全年有效)
G(2017年生效)

查询不包括V、W、X、Y或Z，这些在2017年都无效。(要特别注意G&Z，这是棘手的边缘情况！)

-- Sample data
create table #Temp (
ItemID    char,
Valid     bit,
StartDate date
);
insert into #Temp (ItemID, Valid, StartDate)
values ('A', 1, '2015-01-01'),
('B', 0, '2015-01-01'),
('B', 1, '2017-03-01'),
('C', 1, '2015-01-01'),
('C', 0, '2017-04-01'),
('D', 0, '2015-01-01'),
('D', 1, '2017-05-01'),
('D', 0, '2017-06-01'),
('E', 1, '2015-01-01'),
('E', 0, '2017-05-01'),
('E', 1, '2017-06-01'),
('F', 1, '2015-01-01'),
('F', 0, '2018-02-01'),
('G', 1, '2017-12-31'),
('V', 0, '2015-01-01'),
('V', 1, '2018-02-01'),
('W', 1, '2015-01-01'),
('W', 0, '2016-01-01'),
('X', 0, '2015-01-01'),
('Y', 1, '2018-01-01'),
('Z', 1, '2015-01-01'),
('Z', 0, '2017-01-01');

仅供参考，以下是我发现的其他一些SO问题，它们提出了类似的问题，但并不完全相同：

SQL查询：在给定时间间隔内活动的所有ID的列表，按其开始时间排序
提取指定时间段内的信号状态
查询以查找在某个日期范围内处于活动状态的记录

首先，您可以打开时间戳的原始列表：

ItemID有效开始日期---------------------A 1 2015-01-01B 0 2015-01-01B 1 2017-03-01C 1 2015-01-01C 0 2017-04-01D 0 2015-01-01D 1 2017-05-01D 0 2017-06-01E 1 2015-01-01E 0 2017-05-01E 1 2017-06-01F 1 2015-01-01F 0 2018-02-01G 1 2017-12-31V 0 2015-01-01V 1 2018-02-01W 1 2015-01-01W 0 2016-01-01X 0 2015-01-01Y 1 2018-01-01Z 1 2015-01-01Z 0 2017-01-01

进入范围列表，其中结束日期是项目的下一个条目的StartDate，或者，如果当前行是最后一个条目，则为今天的日期：

ItemID有效开始日期结束日期--------------------------------A 1 2015-01-01(今日)B 0 2015-01-01 2017-03-01B 1 2017-03-01(今日)C 1 2015-01-01 2017-04-01C 0 2017-04-01(今日)D 0 2015-01-01 2017-05-01D 1 2017-05-01 2017-06-01D 0 2017-06-01(今日)E 1 2015-01-01 2017-05-01E 0 2017-05-01 2017-06-01E 1 2017-06-01(今日)F 1 2015-01-01 2018-02-01F 0 2018-02-01(今天)G 1 2017-12-31V 1 2018-02-01(今日)W 1 2015-01-01 2016-01-01W 0 2016-01-01(今天)X 0 2015-01-01(今天Z 0 2017-01-01(今天)

您可以使用LEAD分析函数来实现：

EndDate = LEAD(StartDate, 1, CAST(CURRENT_TIMESTAMP AS date))
OVER (PARTITION BY ItemID ORDER BY StartDate ASC)

一旦您有了一个范围列表，就可以使用这种已建立的查找相交范围的方法(表中的范围与查询参数中指定的范围相交)来轻松地匹配行：

StartDate < @EndDate AND EndDate > @StartDate

以下是完整的解决方案：

DECLARE
@StartDate date = '2017-01-01',
@EndDate   date = '2018-01-01',
@ValidValue bit = 1
;
WITH
ranges AS
(
SELECT
ItemID,
Valid,
StartDate,
EndDate = LEAD(StartDate, 1, CAST(CURRENT_TIMESTAMP AS date))
OVER (PARTITION BY ItemID ORDER BY StartDate ASC)
FROM
#Temp
)
SELECT DISTINCT
ItemID
FROM
ranges
WHERE
Valid = @ValidValue
AND StartDate < @EndDate
AND EndDate > @StartDate
;

您可以在db<>的演示中使用此方法不停摆弄

注意：在完成我的答案后，我意识到它最终与萨米的非常相似。不同之处在于处理项目的最后条目

以下是的解决方案

DECLARE @SD DATE = '2017-01-01',
@ED DATE = '2017-12-31';
WITH BSD AS
(
SELECT *,
LAST_VALUE(Valid) OVER(PARTITION BY ItemID ORDER BY StartDate) LV,
COUNT(1) OVER(PARTITION BY ItemID ORDER BY StartDate DESC) CNT
FROM #Temp
WHERE StartDate <= @SD
)
SELECT ItemID
FROM BSD
WHERE LV = 1 AND CNT = 1
UNION 
SELECT ItemID
FROM #Temp
WHERE Valid = 1
AND
StartDate <= @ED
AND
StartDate >= @SD;

实时演示

这是我提出的解决方案：

-- Date range includes all of 2017
declare
@beginSearchDate date = '2017-01-01',
@endSearchDate date = '2017-12-31';
with
-- CTE: Existing data combined with current value as of today
a as (
select ItemID, Valid, StartDate
from #Temp
union
select t1.ItemID, t1.Valid, convert(date, getdate())
from (
select ItemID, max(StartDate) as LatestStartDate
from #Temp
group by ItemID
) as t2
inner join #Temp as t1
on t1.ItemID = t2.ItemID
and t1.StartDate = t2.LatestStartDate
),
-- CTE: Current and previous values included in each record
b as (
select a1.*,
lag(a1.Valid) over ( partition by a1.ItemID order by a1.StartDate )
as PrevValid,
lag(a1.StartDate) over ( partition by a1.ItemID order by a1.StartDate )
as PrevStartDate
from a as a1
inner join a as a2
on a1.ItemID = a2.ItemID
and a1.StartDate = a2.StartDate
),
-- CTE: Values as a series of date ranges
c as (
select distinct ItemID,
StartDate     as UntilDate,
PrevValid     as Valid,
PrevStartDate as FromDate
from b
where PrevValid is not null
)
-- Find all records where date range overlaps
select distinct ItemID
from c
where Valid = 1
and FromDate <= @endSearchDate
and UntilDate > @beginSearchDate
order by ItemID;

结果：

ItemID
------
A
B
C
D
E
F
G

这是我的浏览。我用有效flag=1的项目构建了第一个表，这些项目位于结束日期之后的任何位置。这将说明项目A或任何类似项目。

然后，我将其与每个项目的最后一个无效日期进行匹配(如果有)，然后按日期过滤掉。

declare
@beginSearchDate date = '2017-01-01',
@endSearchDate date = '2017-12-31';

;WITH CTE as (  
select itemid, VALID, MAX(StartDate) stDate from #temp
where valid <> 0 and StartDate <= @endSearchDate
group by itemID, VALID
)
SELECT t1.ItemID, VALID , stDate 
from CTE t1
outer apply (
SELECT ItemID, MAX(StartDate) inValDate from #Temp 
where Valid = 0 
and StartDate <= @endSearchDate 
and ItemID = t1.ItemID GROUP BY ItemID) t2
WHERE t2.inValDate IS NULL 
or (t1.stDate > t2.inValDate OR t1.stDate > @beginSearchDate OR t2.inValDate > @beginSearchDate)

相关内容

最新更新

热门标签：