How can I get a count of Tickets Closed on a given day?

Question

I have a table that stores the status changes of a ticket. Some tickets were re-opened after being closed. And in certain cases (example below) re-opened several times.

Ticket  SEQUENCE    NEWVALUE    OLDVALUE        TIMESTAMP
5879    15870       REQUEST     NULL        2015-06-10 12:13:28.000
5879    16256       Pending     REQUEST     2015-06-11 14:26:38.000
5879    21642       Open        Pending     2015-07-02 13:32:47.000
5879    21943       Pending     Open        2015-07-06 09:55:37.000
5879    23195       Open        Pending     2015-07-09 15:00:47.000
5879    24917       Pending     Open        2015-07-17 15:09:28.000
5879    25903       Closed      Pending     2015-07-22 11:13:03.000
5879    25940       Open        Closed      2015-07-22 12:56:05.000
5879    25972       Pending     Open        2015-07-22 13:54:30.000
5879    25996       Closed      Pending     2015-07-22 14:31:05.000
5879    26484       Pending     Closed      2015-07-24 14:43:28.000
5879    26776       Closed      Pending     2015-07-27 11:34:00.000

I am trying to determine the last time a ticket was Closed. But I have no idea how to even begin this query. Could someone provide a clue? Thanks so much!

The ultimate result that I am trying to get would look like this:

Date        Tickets Submitted   Tickets Closed
2015-06-01      123                 44
2015-06-02      125                 45
2015-06-03      147                 51
2015-06-04      190                 58
2015-06-05      132                 38
2015-06-06      93                  28
2015-06-07      126                 30
2015-06-08      167                 50

My current query is:

SELECT CAST(SUBMITDATE as date) as 'Date'

,COUNT(*) as [Tickets Submitted]
,SUM(CASE WHEN NEWVALUE = 'Closed' THEN 1 ELSE 0 END) as [Tickets Closed]

FROM dbo.MasterTable
FULL OUTER JOIN FIELDHISTORY
ON MasterTable.Ticket = FIELDHISTORY.Ticket

    WHERE SUBMITDATE >= '2015-06-01'
        AND SUBMITDATE < '2015-06-08'

GROUP BY CAST(SUBMITDATE as date)
ORDER BY CAST(SUBMITDATE as date)

The problem with this query is that it doesn't show me how many tickets were closed on that particular date. It merely shows the number of tickets that were closed from the pool of tickets that were opened on that particular day. In other words, from the example above, on 6/1/2015, 123 tickets were created and 44 of those tickets are currently closed. The 44 closed tickets figure can change if someone were to close another ticket that was created on 6/1.

Hope that isn't too confusing! =D

what have you tried? what if a ticket is closed and re-opened and not closed again? do you still count it? — Dave C, Jul 31 '15 at 19:34
If a ticket is closed and then re-opened..and currently still open, then it can not be counted as being closed. — Sung, Jul 31 '15 at 20:21
Can you show the whole schema? You show a single table but in your query you have other tables. — Matt Rowland, Aug 01 '15 at 04:22

score 1 · Answer 1 · answered Jul 31 '15 at 20:00

Assumptions:

In order to consider the ticket as closed, the last status has to be 'Closed' and a ticket can't start from 'Closed' status. You can try the below SQL.

DECLARE @Ticket int = 5879;

SELECT [Timestamp] FROM [Table_Name]
WHERE Ticket=@Ticket AND NewValue = 'Closed'  
AND [Timestamp] = (SELECT Max([Timestamp]) FROM [Table_Name] WHERE Ticket=@Ticket)

[Table_Name] is to be replaced with your table name.

score 1 · Answer 2 · answered Aug 01 '15 at 04:14

Based on your updates, I believe this is what you're after.

You can use the row_number windowed function to give a row# by ticket/date (i called this rid). Then you can do a count by ticket/date as well (i called this tdc).

Here is the output of mycte before I changed it to do counts and sums.

actdt       ticket  sequence    newvalue    oldvalue    rid tdc
2015-06-10  5879    15870       REQUEST     NULL        1   1
2015-06-11  5879    16256       Pending     REQUEST     1   1
2015-07-02  5879    21642       Open        Pending     1   1
2015-07-06  5879    21943       Pending     Open        1   1
2015-07-09  5879    23195       Open        Pending     1   1
2015-07-17  5879    24917       Pending     Open        1   1
2015-07-22  5879    25903       Closed      Pending     1   4
2015-07-22  5879    25940       Open        Closed      2   4
2015-07-22  5879    25972       Pending     Open        3   4
2015-07-22  5879    25996       Closed      Pending     4   4
2015-07-24  5879    26484       Pending     Closed      1   1
2015-07-27  5879    26776       Closed      Pending     1   1

Based on your info, sequence=25996 and sequence=26776 are the only events that could count as actually closed, as it was the last event of the day. Notice here, rid=tdc.

Where these values match is the last event of the day, should a ticket be re-opened/worked on the same date (you could also do the same with an inverse row_number, but this gives a bit more flexibility for checking other conditions). You can thus use these two new columns to evaluate whether an item counts as closed, but because its a conditional count, you have to sum a 0/1 instead.

Output:

actdt       submitted   closed
2015-06-10  1           0
2015-06-11  1           0
2015-07-02  1           0
2015-07-06  1           0
2015-07-09  1           0
2015-07-17  1           0
2015-07-22  4           1
2015-07-24  1           0
2015-07-27  1           1

Full code:

create table mydata (ticket int, sequence int, newvalue varchar(25), oldvalue varchar(25), [timestamp] datetime)
insert into mydata
select 5879,15870,'REQUEST','NULL','2015-06-10 12:13:28.000' union
select 5879,16256,'Pending','REQUEST','2015-06-11 14:26:38.000' union
select 5879,21642,'Open','Pending','2015-07-02 13:32:47.000' union
select 5879,21943,'Pending','Open','2015-07-06 09:55:37.000' union
select 5879,23195,'Open','Pending','2015-07-09 15:00:47.000' union
select 5879,24917,'Pending','Open','2015-07-17 15:09:28.000' union
select 5879,25903,'Closed','Pending','2015-07-22 11:13:03.000' union
select 5879,25940,'Open','Closed','2015-07-22 12:56:05.000' union
select 5879,25972,'Pending','Open','2015-07-22 13:54:30.000' union
select 5879,25996,'Closed','Pending','2015-07-22 14:31:05.000' union
select 5879,26484,'Pending','Closed','2015-07-24 14:43:28.000' union
select 5879,26776,'Closed','Pending','2015-07-27 11:34:00.000'

; with cte (actdt,ticket,sequence,newvalue,oldvalue,rid,tdc)
as
(
   select convert(date,[timestamp]), 
          ticket,
          sequence,
          newvalue,
          oldvalue,
          row_number() over(partition by ticket,convert(date,[timestamp]) order by sequence) as rid,
          count(2) over(partition by ticket,convert(date,[timestamp])) as tdc
   from mydata
)

select actdt,
       count(distinct sequence) as submitted,
       sum(case when newvalue='closed' and rid=tdc then 1 else 0 end) as closed
from cte
group by actdt

If I missed something, let me know, and I can update this as necessary.

SQLFiddle

score 0 · Answer 3 · answered Jul 31 '15 at 19:34

0

select TIMESTAMP from TABLE_NAME where NEWVALUE like 'Closed' or OLDVALUE like 'Closed'

answered Jul 31 '15 at 19:34

nabil

151
1
1
3

How can I get a count of Tickets Closed on a given day?

3 Answers3