December 4, 2016 at 7:34 am
Hi Experts,
I need your help.
I use sql server 2012.
I have a table like this:
requestid eventdate eventname
464442016/08/08 10:20:33.000OPENED
464442016/08/08 10:33:10.000REVIEWED
464442016/08/08 11:09:55.000OPENED
464442016/08/08 11:32:41.000REVIEWED
464442016/08/08 11:39:01.000CLOSED
464442016/08/08 15:00:04.000OPENED
464442016/08/08 15:17:40.000REVIEWED
464442016/08/08 15:29:36.000OPENED
464442016/08/08 15:31:34.000REVIEWED
464442016/08/08 15:36:37.000CLOSED
464442016/08/08 17:04:27.000OPENED
And I want to identify
1- For each Eventname = "CLOSED"
2- I want to identify the first eventname ="Reviewed" after a eventname = "OPENED"
3- So to have a result like this: I tried the sql lag function but it doesn t work. 🙁
requestid Revieweddate eventname ClosedDate Eventname
46444 2016/08/08 10:33:10.000REVIEWED 2016/08/08 11:39:01.000CLOSED
46444 2016/08/08 15:17:40.000REVIEWED 2016/08/08 15:36:37.000CLOSED
December 4, 2016 at 9:18 am
If a RequestId has been 'Closed', shouldn't the next RequestId have a different value? If they're all 46444, what's the point of this number?
Please post your data in a consumable format in future, like this:
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');
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
December 4, 2016 at 9:55 am
Hi Phil,
Thank you for your answer. This query is for a log application.
I have focused on 1 request ( requestid). But it is possible they are several requestid
This is a sample with 2 requestid and here is the result I would like to obtain.
I Have worked on it all week end and still haven't found a solution.
I tried lag and lead function of sql server 2012 but it doesn't work.
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
The result
requestid eventdateeventname CLOSEDDate CLOSEDEventname
464442016-08-08 10:33:10.000REVIEWED 2016-08-08 11:39:01.000CLOSED
464442016-08-08 15:17:40.000REVIEWED 2016-08-08 15:36:37.000CLOSED
464452016-08-11 01:33:10.000REVIEWED 2016-08-12 15:36:37.000CLOSED
December 4, 2016 at 9:57 am
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
December 4, 2016 at 11:06 am
I have an inelegant solution to this inelegant problem 🙂 There will be better ways, but this seems to work, at least for the test data provided:
SELECT
tst.requestid
, ReviewDate = erd.EarliestReviewDate
, ClosedDate = tst.eventdate
FROM #test tst
CROSS APPLY
(
SELECT
PrevClosedDate = MAX(t.eventdate)
FROM #test t
WHERE t.requestid = tst.requestid
AND t.eventdate < tst.eventdate
AND t.eventname = 'CLOSED'
) pcd
CROSS APPLY
(
SELECT
EarliestReviewDate = MIN(t.eventdate)
FROM #test t
WHERE t.requestid = tst.requestid
AND t.eventdate < tst.eventdate
AND (
t.eventdate > pcd.PrevClosedDate
OR pcd.PrevClosedDate IS NULL
)
AND t.eventname = 'REVIEWED'
) erd
WHERE tst.eventname = 'CLOSED'
ORDER BY tst.requestid
, tst.eventdate;
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
December 4, 2016 at 2:18 pm
Thks Phil for your help. 🙂
I have this solution too.
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
December 4, 2016 at 3:14 pm
Well done. I compared execution plans and my version seems to have the edge. Especially if you add an index on (requestid, eventdate, eventname). Might be worth testing both on a larger dataset and seeing what happens.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply