T SQL查询.确定日期列中的开始日期 /结束日期

Question

我需要您的帮助。我使用SQL Server 2012。此查询用于日志应用程序。我有这样的桌子：

这是一个带有2个请求的样本，这是我要获得的结果。

我整个星期都一直在研究它，但仍未找到解决方案。我尝试了SQL Server 2012的滞后和铅功能。

IF OBJECT_ID('tempdb..#test', 'U') IS NOT NULL
DROP TABLE #test;
CREATE TABLE #test
(
    requestid INT,
    eventdate DATETIME,
   eventname VARCHAR(20)
);
INSERT #test
(
    requestid,
   eventdate,
    eventname
)
VALUES
(46444, '2016/08/08 10:20:33.000', 'OPENED'),
(46444, '2016/08/08 10:33:10.000', 'REVIEWED'),
(46444, '2016/08/08 11:09:55.000', 'OPENED'),
(46444, '2016/08/08 11:32:41.000', 'REVIEWED'),
(46444, '2016/08/08 11:39:01.000', 'CLOSED'),
(46444, '2016/08/08 15:00:04.000', 'OPENED'),
(46444, '2016/08/08 15:17:40.000', 'REVIEWED'),
(46444, '2016/08/08 15:29:36.000', 'OPENED'),
(46444, '2016/08/08 15:31:34.000', 'REVIEWED'),
(46444, '2016/08/08 15:36:37.000', 'CLOSED'),
(46444, '2016/08/08 17:04:27.000', 'OPENED'),
(46445, '2016/08/10 09:00:00.000', 'OPENED'),
(46445, '2016/08/11 01:33:10.000', 'REVIEWED'),
(46445, '2016/08/12 15:36:37.000', 'CLOSED');
select *
from #test

结果

requestid   eventdate           eventname    CLOSEDDate      CLOSEDEventname
 46444  2016-08-08 10:33:10.000 REVIEWED    2016-08-08 11:39:01.000 CLOSED
 46444  2016-08-08 15:17:40.000 REVIEWED    2016-08-08 15:36:37.000 CLOSED
 46445  2016-08-11 01:33:10.000 REVIEWED    2016-08-12 15:36:37.000 CLOSED

Answer 1

我可以看到您似乎想要的东西。对于每个关闭日期，您希望该近距离的最早审核日期。您可以通过枚举关闭来分配组然后汇总：

来做到这一点：

select requestid,
       min(case when eventname = 'REVIEWED' then eventdate end) as eventdate,
       'REVIEWED' as event,
       max(eventdate) as closedate,
       'CLOSED' as closedeventname
from (select t.*,
             sum(case when eventname = 'CLOSED' then 1 else 0 end) over (partition by requestid order by eventdate desc) as grp
      from #test t
     ) t
group by requestid, grp;
Having min(case when eventname = 'REVIEWED' then eventdate end) is not null

我很确定这会产生您想要的输出。如果您未能在问题中指定所需的其他规则，我强烈建议您提出一个更好的解释问题。