Joining tables with one to many relationship 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
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