在事件发生时标识日期范围的T-SQL查询
在事件发生时标识日期范围的T-SQL查询
提问于 2018-05-23 14:18:42
当一个组织在我们的“监视器”列表上时，我正在尝试识别日期范围。
我的数据如下：
OrgCode OrgName ReviewDate MonitorList
8000 Organization A 3/6/2014 1
8000 Organization A 6/4/2014 1
8000 Organization A 9/4/2014 1
8000 Organization A 12/4/2014 0
8000 Organization A 3/5/2015 1
8000 Organization A 6/4/2015 1
8000 Organization A 9/16/2015 1
8000 Organization A 12/16/2015 1
8000 Organization A 3/9/2016 1
8000 Organization A 6/2/2016 1
8000 Organization A 9/8/2016 1
8000 Organization A 12/8/2016 1
8000 Organization A 3/9/2017 0
8000 Organization A 6/14/2018 0我正在寻找的查询输出如下所示：
OrgCode OrgName MonitorStartDate MonitorEndDate
8000 Organization A 3/6/2014 12/4/2014
8000 Organization A 3/5/2015 3/9/2017该组织-组织A -已两次出现在我们的监测名单上:2014年6月3日至2014年4月12日，以及2015年5月3日至2017年9月3日。
我试着用几种方式来实现这一点，包括，
LEAD()和LAG()的变种；GROUP BY OrgCode, OrgName, MonitorList，并将MonitorStartDate定义为MIN(ReviewDate)，MonitorEndDate定义为MAX(ReviewDate)。
第二种方法没有考虑到这些组织可能多次出现/退出监测名单的事实。我仍然认为LEAD()或LAG()的某些组合可能有效；但是，它们本身是行不通的。
您所能提供的任何指导都将是非常棒的，感谢您的帮助！
回答 3
Stack Overflow用户
回答已采纳
发布于 2018-05-23 14:23:12
使用运行和将行分类为组，在遇到0时重新设置值，并使用lead获得下一行的日期，因为结束日期必须从遇到的前0开始。然后，在具有必要分组的相应列上使用min和max。
select orgcode,orgname
,min(case when monitorlist=1 then reviewdate end) as monitorstartdate
,max(next_dt) as monitorenddate
from (select t.*,
sum(case when monitorlist=0 then 1 else 0 end) over(partition by orgcode order by reviewdate) as grp,
lead(reviewdate) over(partition by orgcode order by reviewdate) as next_dt
from tbl t
) t
group by orgcode,orgname,grp
having max(cast(monitorlist as int))=1Stack Overflow用户
发布于 2018-05-23 16:08:43
使用此查询
select orgcode,orgname,format(min(reviewdate),'M/d/yyyy') as monitorstartdate,format(max(next_dt),'M/d/yyyy') as monitorenddate
from (select t.*,
sum(case when monitorlist=0 then 1 else 0 end)
over(partition by orgcode order by reviewdate) as grp,
lead(reviewdate) over(partition by orgcode order by reviewdate) as next_dt
from tbl t
) t
group by orgcode,orgname,grp,MonitorList
having MonitorList = 1结果如下
orgcode orgname monitorstartdate monitorenddate
8000 "Organization A" 3/6/2014 12/4/2014
8000 "Organization A" 3/5/2015 3/9/2017如果人们想验证的话，Fiddle链接就是这里。
Stack Overflow用户
发布于 2018-05-23 16:03:57
您可以通过在每一行上或每一行之后计数0的数目来标识组。其余的只是聚合：
select orgcode, orgname, min(ReviewDate) as MonitorStartDate,
coalesce(min(case when monitorlist = 0 then ReviewDate end),
max(ReviewDate)
) as MontiroEndDate
from (select t.*,
sum(case when monitorlist = 0 then 1 else 0 end) over (partition by orgcode order by reviewdate desc) as grp
from t
) t
group by orgcode, orgname, grp
having max(monitorlist) = 1;结束日期的逻辑有点棘手：
- 它是"0“记录的
ReviewDate。 - 如果没有，则使用最新的
ReviewDate。
这里是一个展示它的SQL。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/50490788
复制相关文章
相似问题
腾讯云开发者
