SQL Server -查询最近的日期范围
如果我有一个这样的表结构：
ProductCode Date
Foo 4/1/2012
Foo 4/2/2012
Foo 4/3/2012
Foo 4/6/2012
Foo 4/7/2012
Foo 4/8/2012
Foo 4/9/2012
Foo 4/10/2012
Foo 4/15/2012
Foo 4/16/2012
Foo 4/17/2012有没有办法查询给定ProductCode和Date的日期范围(假设范围必须是连续的)？换句话说，对于这个表，Foo存在于3个日期范围：4/1-4/3；4/6-4/10；和4/15-4/17，我正在寻找给定日期的日期范围。
请注意，Foo没有date的4/4、4/5、4/11、4/12、4/13和4/14。
示例：
ProductCode=Foo, Date=4/2将返回4/1-4/3，因为这些条目是连续的。
ProductCode=Foo, Date=4/4不会返回任何内容
ProductCode=Foo, Date=4/7将返回4/6-4/10，因为这些条目是连续的。
ProductCode=Foo, Date=4/12不会返回任何内容
等。
回答 6
Stack Overflow用户
发布于 2012-04-21 21:33:45
当前一天没有行时，新的范围开始。如果运行的是SQL Server2012，则可以使用lag窗口函数检查行是否引入了新范围。一旦知道哪些行引入了新的范围，就可以计算标题行的数量，以便为每个范围分配一个唯一的数字。
有了范围编号，您就可以使用min和max查找开始和结束日期。之后，就是选择行的问题了：
; with IsHead as
(
select ProductCode
, Date
, case when lag(Date) over (partition by ProductCode
order by Date) = dateadd(day, -1, Date) then 0
else 1 end as IsHead
from YourTable
)
, RangeNumber as
(
select ProductCode
, Date
, sum(IsHead) over (partition by ProductCode order by Date)
as RangeNr
from IsHead
)
, Ranges as
(
select *
, min(Date) over (partition by RangeNr) as RangeStart
, max(Date) over (partition by RangeNr) as RangeEnd
from RangeNumber
)
select *
from Ranges
where ProductCode = 'Bar'
and Date = '4/2/2012'Example at SQL Fiddle.
Stack Overflow用户
发布于 2012-04-22 11:06:41
可以使用LAG，如果SQL Server 2005支持它的话。不幸的是，LAG window function只能在SQL Server2012和PostgreSQL 8.4 and above上运行;-)
在SQL Server2005上工作，我想，SQLFiddle没有SQL2005支持，只尝试了SQLFiddle的SQL Server2008，而不是2012：
with DetectLeaders as
(
select cr.ProductCode, CurRowDate = cr.Date, PrevRowDate = pr.Date
from tbl cr
left join tbl pr
on pr.ProductCode = cr.ProductCode AND cr.Date = DATEADD(DAY,1,pr.Date)
),
MembersLeaders as
(
select *,
MemberLeader =
(select top 1 CurRowDate
from DetectLeaders nearest
where nearest.PrevRowDate is null
and nearest.ProductCode = DetectLeaders.ProductCode
and DetectLeaders.CurRowDate >= nearest.CurRowDate
order by nearest.CurRowDate desc)
from DetectLeaders
)
select BeginDate = MIN(CurRowDate), EndDate = MAX(CurRowDate)
from MembersLeaders
where MemberLeader =
(select MemberLeader
from MembersLeaders
where ProductCode = 'Foo' and CurRowDate = '4/7/2012')实时测试：http://sqlfiddle.com/#!3/3fd1f/1
基本上，它是这样工作的：
PRODUCTCODE CURROWDATE PREVROWDATE MEMBERLEADER
Foo 2012-04-01 2012-04-01
Foo 2012-04-02 2012-04-01 2012-04-01
Foo 2012-04-03 2012-04-02 2012-04-01
Foo 2012-04-06 2012-04-06
Foo 2012-04-07 2012-04-06 2012-04-06
Foo 2012-04-08 2012-04-07 2012-04-06
Foo 2012-04-09 2012-04-08 2012-04-06
Foo 2012-04-10 2012-04-09 2012-04-06
Foo 2012-04-15 2012-04-15
Foo 2012-04-16 2012-04-15 2012-04-15
Foo 2012-04-17 2012-04-16 2012-04-15
Bar 2012-05-01 2012-05-01
Bar 2012-05-02 2012-05-01 2012-05-01
Bar 2012-05-03 2012-05-02 2012-05-01
Bar 2012-05-06 2012-05-06
Bar 2012-05-07 2012-05-06 2012-05-06
Bar 2012-05-08 2012-05-07 2012-05-06
Bar 2012-05-09 2012-05-08 2012-05-06
Bar 2012-05-10 2012-05-09 2012-05-06
Bar 2012-05-15 2012-05-15
Bar 2012-05-16 2012-05-15 2012-05-15
Bar 2012-05-17 2012-05-16 2012-05-15http://sqlfiddle.com/#!3/35818/11
Stack Overflow用户
发布于 2012-04-21 21:30:57
可以使用递归CTE。
declare @target_date datetime = convert(datetime, '04/07/2012', 101);
with source_table as (
select ProductCode, convert(datetime, Date, 101) as Date
from (
values
('Foo', '4/1/2012')
,('Foo', '4/2/2012')
,('Foo', '4/3/2012')
,('Foo', '4/6/2012')
,('Foo', '4/7/2012')
,('Foo', '4/8/2012')
,('Foo', '4/9/2012')
,('Foo', '4/10/2012')
,('Foo', '4/15/2012')
,('Foo', '4/16/2012')
,('Foo', '4/17/2012')
) foo(ProductCode, Date)
),
recursive_date_lower as (
select Date from source_table where Date = @target_date
union all
select dateadd(d, -1, r.Date) from recursive_date_lower r where exists (select 0 from source_table s where s.Date = dateadd(d, -1, r.Date))
),
recursive_date_upper as (
select Date from source_table where Date = @target_date
union all
select dateadd(d, 1, r.Date) from recursive_date_upper r where exists (select 0 from source_table s where s.Date = dateadd(d, 1, r.Date))
)
select
(select min(Date) from recursive_date_lower) as start,
(select max(Date) from recursive_date_upper) as finishhttps://stackoverflow.com/questions/10259157
复制相似问题
腾讯云开发者
