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');
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;
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.
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy