May 11, 2012 at 10:15 am
I have the following set of data
DTime id ticketid actionlog
2012-05-09 05:24:58.000 13957 1298 created
2012-05-09 08:36:05.000 13967 1298 reply_tech
2012-05-09 08:46:04.000 13968 1298 reply_tech
2012-05-09 09:08:03.000 13972 1298 reply_user
2012-05-09 09:41:04.000 13976 1298 reply_user
2012-05-09 09:50:03.000 13979 1298 reply_user
2012-05-09 09:52:45.000 13982 1298 reply_tech
2012-05-09 09:58:05.000 13986 1298 reply_user
2012-05-10 01:02:04.000 14008 1298 reply_user
2012-05-10 13:50:59.000 14019 1298 reply_tech
2012-05-10 13:55:50.000 14021 1298 reply_tech
2012-05-10 14:02:04.000 14023 1298 reply_user
I need to get a result set of the measure of time difference from the first user action in a potential group of user interactions, to the first tech interaction in a group of tech interactions, in a series of both tech and user interactions. Of the data above, there would be three numbers in the result set from the select:
03:11:07
00:44:42
03:52:54
This would be obtained by a calculation of this:
select convert(varchar(10),dateadd(second,datediff(second,'first user reply after last tech reply','first tech reply after first user reply after previous tech reply'),'1900-01-01'),8)
The actual values that would be calculated would be:
select convert(varchar(10),dateadd(second,datediff(second,'2012-05-09 05:24:58.000','2012-05-09 08:36:05.000'),'1900-01-01'),8)
select convert(varchar(10),dateadd(second,datediff(second,'2012-05-09 09:08:03.000','2012-05-09 09:52:45.000'),'1900-01-01'),8)
select convert(varchar(10),dateadd(second,datediff(second,'2012-05-09 09:58:05.000','2012-05-10 13:50:59.000'),'1900-01-01'),8)
Anyone have a script on how I could I mine out those date/time stamps as highlighted above (red to orange)? I have been using row_number over partition actionlog order by dtime,actionlog and a recursive CTE, but I can't seem to filter out the unwanted records.
create table #tmpmatt(DTime datetime,id int,ticketid int, actionlog varchar(20))
insert #tmpmatt
select '2012-05-09 05:24:58.000',13957,1298,'created'
union all
select '2012-05-09 08:36:05.000',13967,1298,'reply_tech'
union all
select '2012-05-09 08:46:04.000',13968,1298,'reply_tech'
union all
select '2012-05-09 09:08:03.000',13972,1298,'reply_user'
union all
select '2012-05-09 09:41:04.000',13976,1298,'reply_user'
union all
select '2012-05-09 09:50:03.000',13979,1298,'reply_user'
union all
select '2012-05-09 09:52:45.000',13982,1298,'reply_tech'
union all
select '2012-05-09 09:58:05.000',13986,1298,'reply_user'
union all
select '2012-05-10 01:02:04.000',14008,1298,'reply_user'
union all
select '2012-05-10 13:50:59.000',14019,1298,'reply_tech'
union all
select '2012-05-10 13:55:50.000',14021,1298,'reply_tech'
union all
select '2012-05-10 14:02:04.000',14023,1298,'reply_user'
select * from #tmpmatt
May 11, 2012 at 1:23 pm
Beh... feel dirty... resorted to loop instead of set based. If anyone has a set based with window functions in 2005 that is cleaner, I'd love to see it.
What I went with:
--drop table #tmpmatt
--drop table #tmpmatt2
SELECT * INTO #tmpMatt
FROM OPENQUERY(deskprob,'
select FROM_UNIXTIME(timestamp) as DTime,
id,
ticketid,
actionlog,
techid,
userid,
id_before,
id_after,
detail_before,
detail_after
from ticket_log
where DATE_ADD(CURDATE(), INTERVAL -50 DAY) < FROM_UNIXTIME(timestamp)
and actionlog in (''reply_tech'',''reply_user'',''created'')
ORDER BY ticketID, id
')
SELECT ROW_NUMBER() OVER (PARTITION BY ticketid ORDER BY dtime) AS tickID_rownum,
DENSE_RANK() OVER (ORDER BY ticketid) AS ticketRank,
*
INTO #tmpmatt2
FROM #tmpmatt
CROSS APPLY (SELECT CASE WHEN actionlog = 'reply_tech' THEN 2 ELSE 1 END AS techoruser) AS techuser
ORDER BY ticketid, dtime;
DECLARE @table TABLE(userStartID INT, dtUser datetime, techStartID INT, dtTech datetime, vcTimeDiff varchar(10), TicketID INT)
DECLARE @maxtickRank INT,
@currRankNum INT
DECLARE @maxtickRankInner INT,
@currRankNumInner INT
DECLARE @idUser INT,
@idTech INT,
@TicketID INT,
@dtUser datetime,
@dtTech datetime,
@vcTimediff varchar(10)
SELECT @maxtickRank = MAX(ticketrank),
@currRankNum = 1
FROM #tmpmatt2
WHILE @currRankNum <= @maxtickRank
BEGIN
SELECT @maxtickRankInner = MAX(tickid_rownum),
@currRankNumInner = 1
FROM #tmpmatt2
WHERE ticketrank = @currRankNum
WHILE @currRankNumInner <= @maxtickRankInner
BEGIN
SELECT @idUser = id, @dtUser = DTime FROM #tmpmatt2
WHERE ticketrank = @currRankNum
AND tickid_rownum = @currRankNumInner
AND techoruser = 1
AND (tickid_rownum - 1) <> ISNULL(( SELECT tickid_rownum FROM #tmpmatt2
WHERE ticketrank = @currRankNum
AND tickid_rownum = @currRankNumInner - 1
AND techoruser = 1 ),9999)
IF NOT @idUser IS NULL
BEGIN
SELECT TOP 1 @idTech = id,
@TicketID = TicketID,
@dtTech = DTime
FROM #tmpmatt2
WHERE ticketrank = @currRankNum
AND techoruser = 2
AND id > @idUser
ORDER BY tickID_rownum
IF NOT @idTech IS NULL
BEGIN
INSERT @table
SELECT @idUser, @dtUser, @idtech, @dtTech, convert(varchar(10),dateadd(second,datediff(second,@dtUser,@dtTech),'1900-01-01'),8), @ticketID
END
END
SELECT @idUser = NULL, @idtech = NULL, @TicketID = null, @dtUser = null, @dtTech = null
SELECT @currRankNumInner = @currRankNumInner + 1
END
SELECT @currRankNum = @currRankNum + 1
END
SELECT * FROM @table ORDER BY TicketID
SELECT * FROM #tmpmatt2
May 13, 2012 at 7:21 pm
This may provide you the results set you seek:
;WITH CTE AS (
SELECT DTime, id, ticketid, actionlog
,( SELECT TOP 1 DTime
FROM #tmpmatt b
WHERE a.actionlog <> b.actionlog and a.DTime < b.DTime
ORDER BY ticketid, DTime) As DTime2
,( SELECT TOP 1 actionlog
FROM #tmpmatt b
WHERE a.DTime > b.DTime
ORDER BY ticketid, DTime DESC) As actionlog2
FROM #tmpmatt a
)
SELECT id, ticketid, CONVERT(VARCHAR(10),DATEADD(second,DATEDIFF(second,DTime,DTime2),'1900-01-01'),8)
FROM CTE
WHERE actionlog IN ('created', 'reply_user') and DTime2 IS NOT NULL and
(actionlog2 <> actionlog OR actionlog2 IS NULL)
ORDER BY DTime
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
May 15, 2012 at 11:57 am
Thanks much Dwain! I will try it out when I get the free moment and see where it goes!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply