October 28, 2013 at 10:19 am
This has the potential to become a long-winded post. I hope I can keep it short but still provide a sufficient amount of information. I've been trying to solve this problem here and there for a couple days, and I'm stumped.
I'm dealing with ticket times for software products. A ticket is opened and closed, and it can go through various different statuses during its lifetime. I'm tasked with calculating the time the ticket was open minus any statuses that are considered irrelevant to my department.
So, if a ticket is opened 2013-10-27 00:15 and closed 2013-10-27 11:15 but sits in status X from 2013-10-27 02:15 to 2013-10-27 04:15, the ticket should only be shown open for 9 hours.
I have been working with a couple "journal" tables that function like history tables for these tickets. These tables only store the new value and the date the new value was put in place for a given ticket. In the code below, you can see this would select all tickets who went into "on hold" status at some point during their life. (I was given the joins for these tables, so don't give me any credit for that.)
SELECT DISTINCT st.CSS_Service_Ticket_Id
FROM rsys_tables ta, Journal_Items ji, Journal_History jh, CSS_Service_Ticket st
WHERE ji.Reference_Table = ta.Tables_Id
AND ji.Journal_Pages_Id = jh.Journal_Pages_Id
AND st.CSS_Service_Ticket_Id = Reference_Record
AND Table_Name = 'Css_Service_ticket'
AND Field_Label = 'Status'
AND New_Value = 2 -- on hold
Anyway, I have tried throwing together a cursor to calculate what I need, but some problems are cropping up. First, I haven't written a cursor for a few years, and it was in PL/SQL last time. Next, from what I have read, it seems cursors are frowned upon. I would agree since my current attempt takes about 1.33 minutes to run. Lastly, I don't know how to output the results of my cursor. (I'm using PRINT to see how the calculations are functioning.)
Ideally, I would be able to return a result set from whatever solution I use. The result set would have the ticket ID and the time the ticket was open (along with other peripheral fields).
Any ideas? I can also post my terrible attempt a cursor if necessary. I'm not even sure the calculations are correct.
October 28, 2013 at 10:53 am
To get better help, please read the article linked in my signature, it will tell you how to post DDL, sample data and expected results in the way someone can easily work on your issue.
Your problem seems like a common issue posted in these forums and you can get a great answer, but we need sample data to test.
Your code could be useful to understand your process and change it to a better solution.
October 28, 2013 at 11:01 am
Thanks for the reply, Luis. I will read that link and post more information. It will take me some time to come up with this. 🙂
October 28, 2013 at 12:23 pm
Meanwhile I would recommend that you use ANSI-92 style joins instead of the older ANSI-89 style. The end result is exactly the same thing but it is a bit easier read and less prone to error.
SELECT DISTINCT st.CSS_Service_Ticket_Id
FROM rsys_tables ta
inner join Journal_Items ji on ji.Reference_Table = ta.Tables_Id
inner join Journal_History jh on ji.Journal_Pages_Id = jh.Journal_Pages_Id
inner join CSS_Service_Ticket st on st.CSS_Service_Ticket_Id = Reference_Record
Where ta.Table_Name = 'Css_Service_ticket'
AND ta.Field_Label = 'Status'
AND st.New_Value = 2 -- on hold
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 29, 2013 at 12:34 pm
I believe I've thrown together the required code. The primary keys are all BINARY(8) in this database, but I chose to use INT instead for the temp tables. All of the tables are complete other than CSS_Service_Ticket because I only need the two columns provided (for now).
IF OBJECT_ID('TempDB..#rsys_tables_temp','U') IS NOT NULL
DROP TABLE #rsys_tables_temp
CREATE TABLE #rsys_tables_temp
(
Tables_Id INT PRIMARY KEY
, Table_Name NVARCHAR(31)
)
INSERT INTO #rsys_tables_temp
(Tables_Id, Table_Name)
VALUES (1, 'CSS_Service_Ticket');
IF OBJECT_ID('TempDB..#journal_items_temp','U') IS NOT NULL
DROP TABLE #journal_items_temp
CREATE TABLE #journal_items_temp
(
Journal_Items_Id INT PRIMARY KEY
, Rn_Create_Date DATETIME
, Rn_Create_User INT
, Reference_Table INT
, Reference_Record INT
, Journal_Pages_Id INT
, Primary_Reference INT
)
INSERT INTO #journal_items_temp
(Journal_Items_Id, Rn_Create_Date, Rn_Create_User
, Reference_Table, Reference_Record, Journal_Pages_Id, Primary_Reference)
VALUES (111,'2013-02-01 01:50:01.320',10,1,50,95,1)
, (222,'2013-02-01 05:28:46.843',20,1,51,96,1)
, (333,'2013-02-03 19:48:46.783',20,1,52,97,1)
, (444,'2013-02-04 01:56:04.177',20,1,53,98,1)
, (555,'2013-02-04 02:18:57.587',30,1,54,99,1);
IF OBJECT_ID('TempDB..#journal_history_temp','U') IS NOT NULL
DROP TABLE #journal_history_temp
CREATE TABLE #journal_history_temp
(
Time_Stamp DATETIME
, Journal_Pages_Id INT
, Journal_History_Id INT PRIMARY KEY
, Field_Label NVARCHAR(100)
, Rn_Create_Date DATETIME
, Rn_Create_User INT
, New_Value NVARCHAR(100)
)
INSERT INTO #journal_history_temp
(Time_Stamp, Journal_Pages_Id, Journal_History_Id
, Field_Label, Rn_Create_Date, Rn_Create_User, New_Value)
VALUES ('2013-02-01 01:50:01.260',95,101,'Status','2013-02-01 01:50:01.337',10,2)
, ('2013-02-01 05:28:46.823',96,202,'Status','2013-02-01 05:28:46.847',20,2)
, ('2013-02-03 19:48:46.763',97,303,'Status','2013-02-03 19:48:46.783',20,2)
, ('2013-02-04 01:56:03.680',98,404,'Status','2013-02-04 01:56:04.220',20,2)
, ('2013-02-04 02:18:57.540',99,505,'Status','2013-02-04 02:18:57.587',30,2);
IF OBJECT_ID('TempDB..#css_service_ticket_temp','U') IS NOT NULL
DROP TABLE #css_service_ticket_temp
CREATE TABLE #css_service_ticket_temp
(
CSS_Service_Ticket_Id INT PRIMARY KEY
, Ticket_Number NVARCHAR(20)
)
INSERT INTO #css_service_ticket_temp
(CSS_Service_Ticket_Id, Ticket_Number)
VALUES (50,'12340000')
, (51,'12340001')
, (52,'12340002')
, (53,'12340003')
, (54,'12340004');
Here is the redone SELECT:
SELECT *
FROM #rsys_tables_temp ta
INNER JOIN #journal_items_temp ji ON ji.Reference_Table = ta.Tables_Id
INNER JOIN #journal_history_temp jh ON ji.Journal_Pages_Id = jh.Journal_Pages_Id
INNER JOIN #css_service_ticket_temp st ON st.CSS_Service_Ticket_Id = ji.Reference_Record
WHERE Table_Name = 'Css_Service_ticket'
AND jh.Field_Label = 'Status'
AND jh.New_Value = 2 -- on hold
Here is my terrible attempt at a cursor (which I still need to update with the new joins):
DECLARE
@prevID AS BINARY(8)
, @currID AS BINARY(8)
, @prevDate AS DATETIME
, @currDate AS DATETIME
, @prevStat AS INT
, @currStat AS INT
, @timeDiff AS INT
DECLARE timeCursor CURSOR --FORWARD_ONLY
FOR
SELECT st.CSS_Service_Ticket_Id
, DATEADD(hh,5,jh.Rn_Create_Date)
, New_Value
FROM rsys_tables ta, Journal_Items ji, Journal_History jh, CSS_Service_Ticket st
WHERE ji.Reference_Table = ta.Tables_Id
AND ji.Journal_Pages_Id = jh.Journal_Pages_Id
AND st.CSS_Service_Ticket_Id = Reference_Record
AND Table_Name = 'Css_Service_ticket'
AND Field_Label = 'Status'
ORDER BY st.CSS_Service_Ticket_Id
, DATEADD(hh,5,jh.Rn_Create_Date);
OPEN timeCursor;
FETCH NEXT FROM timeCursor
INTO @prevID, @prevDate, @prevStat;
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM timeCursor
INTO @currID, @currDate, @currStat;
IF @currID = @prevID AND @prevStat IN (7,8,13,15,16) AND @currStat NOT IN (7,8,13,15,16)
BEGIN
SET @timeDiff = @timeDiff + DATEDIFF(n,@prevDate,@currDate);
PRINT 'Prev:' + CAST(CAST(@prevID AS BIGINT) AS VARCHAR(MAX)) + ' - ' + CAST(@prevDate AS VARCHAR(MAX)) + ' - ' + CAST(@prevStat AS VARCHAR(MAX));
PRINT 'Curr:' + CAST(CAST(@currID AS BIGINT) AS VARCHAR(MAX)) + ' - ' + CAST(@currDate AS VARCHAR(MAX)) + ' - ' + CAST(@currStat AS VARCHAR(MAX));
PRINT 'Diff:' + CAST(DATEDIFF(n,@prevDate,@currDate) AS VARCHAR(MAX));
PRINT 'Time:' + CAST(@timeDiff AS VARCHAR(MAX));
END
ELSE IF @currID <> @prevID
SET @timeDiff = 0;
--END;
SET @prevID = @currID;
SET @prevDate = @currDate;
SET @prevStat = @currStat;
END;
CLOSE timeCursor;
DEALLOCATE timeCursor;
GO
October 29, 2013 at 12:35 pm
I would also like to attach this relationship diagram.
October 29, 2013 at 4:34 pm
Hi
I don't think you have provided enough data for the problem you are describing, but I'm going to take a stab in the dark at it. I hope I haven't totally misread/misunderstood your requirements. I added a few additional records to the journal history to test, but I'm not sure I got that right.
-- Some additional rows for testing
INSERT INTO #journal_history_temp
(Time_Stamp, Journal_Pages_Id, Journal_History_Id
, Field_Label, Rn_Create_Date, Rn_Create_User, New_Value)
VALUES ('2013-02-01 01:50:01.260',95,102,'Status','2013-02-01 01:50:01.337',10,8)
, ('2013-02-01 01:52:01.260',95,103,'Status','2013-02-01 01:50:01.337',10,7)
, ('2013-02-01 01:54:01.260',95,104,'Status','2013-02-01 01:50:01.337',10,7)
, ('2013-02-01 01:56:01.260',95,105,'Status','2013-02-01 01:50:01.337',10,13)
, ('2013-02-01 01:58:01.260',95,106,'Status','2013-02-01 01:50:01.337',10,2);
-- The query
with ticketList AS ( -- Add a sequence to the rows based on the timestamp
SELECT st.CSS_Service_Ticket_Id
,jh.Rn_Create_date
,jh.Time_Stamp
,jh.New_Value
, ROW_NUMBER() OVER (PARTITION BY CSS_Service_Ticket_Id ORDER BY Time_Stamp) Sequence
FROM #rsys_tables_temp ta
INNER JOIN #journal_items_temp ji ON ji.Reference_Table = ta.Tables_Id
INNER JOIN #journal_history_temp jh ON ji.Journal_Pages_Id = jh.Journal_Pages_Id
INNER JOIN #css_service_ticket_temp st ON st.CSS_Service_Ticket_Id = ji.Reference_Record
WHERE Table_Name = 'Css_Service_ticket'
),
journalHistDuration AS ( -- join the ticket list to the next item
SELECT tl1.CSS_Service_Ticket_Id
,tl1.Time_Stamp StartTime
,DATEDIFF(minute,tl1.Time_Stamp,ISNULL(tl2.Time_Stamp,tl1.Time_Stamp)) DurationMinutes
,CASE WHEN tl1.New_Value IN (7,8,13,15,16) AND tl1.New_Value != tl2.New_Value THEN 1 ELSE 0 END isSummed -- is it summed. Is this the right logic
FROM ticketList tl1
LEFT OUTER JOIN ticketList tl2 ON tl1.CSS_Service_Ticket_Id = tl2.CSS_Service_Ticket_Id
AND tl1.Sequence = tl2.Sequence - 1
)
SELECT CSS_Service_Ticket_Id, MIN(StartTime) Opened, MAX(StartTime) Closed, SUM(DurationMinutes * isSummed) Duration
FROM journalHistDuration
GROUP BY CSS_Service_Ticket_Id;
Is this along the right lines?
October 29, 2013 at 7:47 pm
Strike that. Not sure what I was thinking.
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
October 29, 2013 at 8:37 pm
Now that I've had some coffee, I think this is what I meant.
SELECT CSS_Service_Ticket_id
,Time_Stamp, jh.New_Value
INTO #Temp
FROM #rsys_tables_temp ta
INNER JOIN #journal_items_temp ji ON ji.Reference_Table = ta.Tables_Id
INNER JOIN #journal_history_temp jh ON ji.Journal_Pages_Id = jh.Journal_Pages_Id
INNER JOIN #css_service_ticket_temp st ON st.CSS_Service_Ticket_Id = ji.Reference_Record
WHERE Table_Name = 'Css_Service_ticket'
AND jh.Field_Label = 'Status';
ALTER TABLE #Temp ADD Duration INT NULL;
ALTER TABLE #Temp ALTER COLUMN CSS_Service_Ticket_id INT NOT NULL;
ALTER TABLE #Temp ALTER COLUMN Time_Stamp DATETIME NOT NULL;
ALTER TABLE #Temp ADD PRIMARY KEY (CSS_Service_Ticket_ID, Time_Stamp);
DECLARE @Duration INT = 0
,@Last_New_Value INT = 0
,@Ticket_ID INT = 0
,@Time_Stamp DATETIME;
UPDATE #Temp WITH(TABLOCKX)
SET @Duration = Duration = CASE WHEN @Ticket_ID = CSS_Service_Ticket_id AND @Last_New_Value IN (7,8,13,15,16)
THEN DATEDIFF(minute, @Time_Stamp, Time_Stamp) ELSE 0 END
,@Last_New_Value = New_Value
,@Time_Stamp = Time_Stamp
,@Ticket_ID = CSS_Service_Ticket_id
OPTION (MAXDOP 1)
SELECT CSS_Service_Ticket_id, Opened=MIN(Time_Stamp)
,Closed=MAX(Time_Stamp)
,Duration=SUM(Duration)
FROM #Temp
GROUP BY CSS_Service_Ticket_id;
GO
DROP TABLE #Temp;
Long live the Quirky Update! Long may it reign!
http://qa.sqlservercentral.com/articles/T-SQL/68467/
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
October 29, 2013 at 9:14 pm
Note that SQL 2012 has a new analytical function (LAG) that makes short work of this.
SELECT CSS_Service_Ticket_id
,Opened=MIN(Time_Stamp)
,Closed=MAX(Time_Stamp)
,Duration=SUM(Duration)
FROM
(
SELECT CSS_Service_Ticket_id
,Time_Stamp, jh.New_Value
,Duration=DATEDIFF(minute
,CASE WHEN LAG(New_Value, 1) OVER (PARTITION BY CSS_Service_Ticket_id ORDER BY Time_Stamp) IN (7,8,13,15,16)
THEN LAG(Time_Stamp, 1) OVER (PARTITION BY CSS_Service_Ticket_id ORDER BY Time_Stamp)
ELSE Time_Stamp END
,Time_Stamp)
FROM #rsys_tables_temp ta
INNER JOIN #journal_items_temp ji ON ji.Reference_Table = ta.Tables_Id
INNER JOIN #journal_history_temp jh ON ji.Journal_Pages_Id = jh.Journal_Pages_Id
INNER JOIN #css_service_ticket_temp st ON st.CSS_Service_Ticket_Id = ji.Reference_Record
WHERE Table_Name = 'Css_Service_ticket'
AND jh.Field_Label = 'Status'
) a
GROUP BY CSS_Service_Ticket_id;
I have reason to believe that it may not perform as swiftly as the QU though.
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
November 1, 2013 at 8:42 am
Sorry I haven't replied. I haven't gotten a chance to try all of these yet. I know I can't use LAG because I'm using SQL Server 2008. I've used LEAD/LAG with Oracle before, and they can be quite useful.
Regarding the comment about the data in the INSERT statements I provided: yes, I did not provide good sample data. I should have thought that through more.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply