Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Hi I have a service ticketing system database (SQL Server 2008) which i want to get the hour elapsed for the tickets. One table contained the Ticket number and the created date/time while the other table contained the status log of each ticket. My problem is how to get the time elapsed considering the last update from the other table. Here is a sample table.

Table1
------
Ticket_Number   created
1234            06-12-2014 08:00:00

Table2
------
Ticket_Number        statuschange
1234              06-12-2014 08:30:00
1234              06-12-2014 08:45:00
1234              06-12-2014 10:00:00
share|improve this question

2 Answers 2

You can use a cte to get the latest change and then join it with the ticket table.

DECLARE @Table1 TABLE (Ticket_Number int NOT NULL, created Datetime)
DECLARE @Table2 TABLE (Ticket_Number int NOT NULL, statuschange Datetime)

INSERT INTO @Table1 (Ticket_Number, created)
  VALUES (1234, '2014-12-06 8:00:00'),
         (1235, '2014-12-08 8:00:00'),
         (1236, '2014-12-08 8:00:00');

INSERT INTO @Table2 (Ticket_Number, statuschange)
  VALUES (1234, '2014-12-06 8:30:00'),
         (1234, '2014-12-06 8:45:00'),
         (1234, '2014-12-06 10:00:00'),
         (1235, '2014-12-08 9:30:00'),
         (1235, '2014-12-08 8:45:00'),
         (1235, '2014-12-08 8:22:00');

;WITH cte_lastChange
AS
(
  SELECT  Ticket_Number,
          statuschange,
          ROW_NUMBER() OVER (PARTITION BY Ticket_Number ORDER BY statuschange desc) AS RowNum
  FROM @Table2
--  WHERE Ticket_Number = 1234
)
SELECT  t1.Ticket_Number,
        t1.created AS ticket_started,
        t2.statuschange AS last_change,
        DATEDIFF(MINUTE, t1.created, t2.statuschange) AS differnece_minutes
FROM @Table1 t1
  INNER JOIN cte_lastChange t2 ON t1.Ticket_Number = t2.Ticket_Number
WHERE t2.RowNum = 1

This results in:

Ticket_Number ticket_started          last_change             differnece_minutes
------------- ----------------------- ----------------------- ------------------
1234          2014-12-06 08:00:00.000 2014-12-06 10:00:00.000 120
1235          2014-12-08 08:00:00.000 2014-12-08 09:30:00.000 90

If you need the time between changes you can extend the cte in this way:

;WITH cte_lastChange
AS
(
  SELECT  Ticket_Number,
          statuschange
  FROM @Table2
--  WHERE Ticket_Number = 1234
)
SELECT  t1.Ticket_Number,
        t1.created AS ticket_started,
        t2.statuschange AS last_change,
        DATEDIFF(MINUTE, 
                 t1.created, 
                 t2.statuschange) AS minutes_since_create,
        DATEDIFF(MINUTE, 
                 LEAD(t2.statuschange, 1, t1.created) OVER(PARTITION BY t2.Ticket_Number ORDER BY t2.statuschange desc), 
                 t2.statuschange) as minutes_since_last_step
FROM @Table1 t1
  INNER JOIN cte_lastChange t2 ON t1.Ticket_Number = t2.Ticket_Number
ORDER BY t1.Ticket_Number, last_change

That gives you the following output:

Ticket_Number ticket_started      last_change         minutes_create minutes_last_step
------------- ------------------- ------------------- -------------- -----------------
1234          2014-12-06 08:00:00 2014-12-06 08:30:00 30             30
1234          2014-12-06 08:00:00 2014-12-06 08:45:00 45             15
1234          2014-12-06 08:00:00 2014-12-06 10:00:00 120            75
1235          2014-12-08 08:00:00 2014-12-08 08:22:00 22             22
1235          2014-12-08 08:00:00 2014-12-08 08:45:00 45             23
1235          2014-12-08 08:00:00 2014-12-08 09:30:00 90             45    

Please note that the LEAD() function is not available for the SQL Server 2008. This function is included from SQL Server 2012 onwards.

Lothar

share|improve this answer
2  
You should add a note that LEAD() needs 2012+ versions to work. –  ypercube Jun 12 at 12:44
    
Question is for SQL Server 2008, so this solution will not work for @Arvin –  Mark Sinkinson Jun 12 at 13:15
    
@MarkSinkinson the first query will work I think, it only uses ROW_NUMBER() which is available from 2005. –  ypercube Jun 12 at 13:22
    
Oops, that's me misreading. Thought the whole solution included the LEAD() –  Mark Sinkinson Jun 12 at 13:27
    
Opps, that's me missreading too :). I diddn't realize that the original poster asked for SQL Server 2008. But as ypercube pointed out the first query will work on 2008. Sorry for that. –  Lothar Kraner Jun 13 at 4:33

There are several ways to do this:

JOIN / GROUP BY

SELECT t1.ticket_number,
       elapsed_time_in_minutes = DATEDIFF(minute, t1.created, MAX(t2.statuschange))
FROM table1 AS t1
  LEFT JOIN table2 AS t2
    ON t2.ticket_number = t1.ticket_number
GROUP BY t1.ticket_number, t1.created ;

GROUP BY / JOIN

SELECT t1.ticket_number,
       elapsed_time_in_minutes = DATEDIFF(minute, t1.created, t2.max_statuschange)
FROM table1 AS t1
  LEFT JOIN 
    ( SELECT ticket_number, 
             max_statuschange = MAX(statuschange)
      FROM table2
      GROUP BY ticket_number
    ) AS t2
    ON t2.ticket_number = t1.ticket_number ;

inline subquery

SELECT t1.ticket_number,
       elapsed_time_in_minutes = DATEDIFF(minute, t1.created, 
                   ( SELECT MAX(t2.statuschange)
                     FROM table2 AS t2
                     WHERE t2.ticket_number = t1.ticket_number
                   )                     )
FROM table1 AS t1 ;

OUTER APPLY - v1

SELECT t1.ticket_number,
       elapsed_time_in_minutes = DATEDIFF(minute, t1.created, t2x.max_statuschange)
FROM table1 AS t1
  OUTER APPLY 
    ( SELECT max_statuschange = MAX(t2.statuschange)
      FROM table2 AS t2
      WHERE t2.ticket_number = t1.ticket_number
    ) AS t2x ;

OUTER APPLY - v2

SELECT t1.ticket_number,
       elapsed_time_in_minutes = DATEDIFF(minute, t1.created, t2x.max_statuschange)
FROM table1 AS t1
  OUTER APPLY 
    ( SELECT TOP (1) max_statuschange = t2.statuschange
      FROM table2 AS t2
      WHERE t2.ticket_number = t1.ticket_number
      ORDER BY t2.statuschange DESC
    ) AS t2x ;
share|improve this answer
    
What about if i want the format in hours and minutes e.g. 02:30? –  Arvin Jun 12 at 15:22

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.