May 11, 2012 at 10:15 am
I have the following set of data
DTimeidticketidactionlog
2012-05-09 05:24:58.000139571298created
2012-05-09 08:36:05.000139671298reply_tech
2012-05-09 08:46:04.000139681298reply_tech
2012-05-09 09:08:03.000139721298reply_user
2012-05-09 09:41:04.000139761298reply_user
2012-05-09 09:50:03.000139791298reply_user
2012-05-09 09:52:45.000139821298reply_tech
2012-05-09 09:58:05.000139861298reply_user
2012-05-10 01:02:04.000140081298reply_user
2012-05-10 13:50:59.000140191298reply_tech
2012-05-10 13:55:50.000140211298reply_tech
2012-05-10 14:02:04.000140231298reply_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
FROMOPENQUERY(deskprob,'
selectFROM_UNIXTIME(timestamp) as DTime,
id,
ticketid,
actionlog,
techid,
userid,
id_before,
id_after,
detail_before,
detail_after
fromticket_log
whereDATE_ADD(CURDATE(), INTERVAL -50 DAY) < FROM_UNIXTIME(timestamp)
andactionlog in (''reply_tech'',''reply_user'',''created'')
ORDER BYticketID, 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 BYticketid, 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
WHEREticketrank = @currRankNum
WHILE @currRankNumInner <= @maxtickRankInner
BEGIN
SELECT @idUser = id, @dtUser = DTime FROM #tmpmatt2
WHEREticketrank = @currRankNum
AND tickid_rownum = @currRankNumInner
AND techoruser = 1
AND (tickid_rownum - 1) <> ISNULL((SELECTtickid_rownum FROM #tmpmatt2
WHEREticketrank = @currRankNum
ANDtickid_rownum = @currRankNumInner - 1
ANDtechoruser = 1 ),9999)
IF NOT @idUser IS NULL
BEGIN
SELECT TOP 1@idTech = id,
@TicketID = TicketID,
@dtTech = DTime
FROM#tmpmatt2
WHEREticketrank = @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