How to calculate time duration using Microsoft SQL?

Question

I want to find the time duration for each person from one start time. I want to calculate the time duration from 1 start time for each day and multiple end times for multiple users. This is my code:

SELECT *,
      CAST(DATEDIFF(n, CAST(End_Time AS datetime), 
      CAST(Start_Time AS datetime))  AS FLOAT) / 60 AS Time_Duration
FROM 
     ( SELECT  NAME,
               MAX(CASE WHEN DESCRIPTION = 'Green' THEN Final_Value END) AS Start_Time,
               MAX(CASE WHEN DESCRIPTION = 'Red' THEN Final_Value END) AS End_Time
       FROM mydata
       WHERE NAME != ‘NA’
       GROUP BY NAME
     ) C

I am not able to get any results for time duration. This is what my output looks like:

    Name        Start_time           End_time        Time_Duration 
1   Day_1       5/6/15 2:30
2   John                           5/6/15 3:30          
3   Ben                            5/6/15 4:30          
4   Mike                           5/6/15 5:30          
5   Day_2       5/7/15 2:30
6   John_2                         5/7/15 4:30          
7   Ben_2                          5/7/15 5:30          
8   Mike_2                         5/7/15 6:30        

I want it to look like this:

    Name        Start_time           End_time        Time_Duration 
1   Day_1      5/6/15 2:30
2   John                           5/6/15 3:30          1.00
3   Ben                            5/6/15 4:30          2.00
4   Mike                           5/6/15 5:30          3.00
5   Day_2      5/7/15 2:30
6   John_2                         5/7/15 4:30          2.00
7   Ben_2                          5/7/15 5:30          3.00
8   Mike_2                         5/7/15 6:30          4.00

Answer 1

Assuming that the values in name column has suffix of the day number (and none for day 1)

WITH td AS
(
SELECT *,
       ROW_NUMBER() OVER (PARTITION BY [day] ORDER BY final_value) rnum
  FROM (SELECT *,
               CASE WHEN CHARINDEX('_', name) = 0
                    THEN '1'
                    ELSE SUBSTRING(name, CHARINDEX('_', name) + 1, LEN(name) - CHARINDEX('_', name))
                END [day]           
           FROM t_dur
        ) tt
)

SELECT t1.name, 
       CASE WHEN rnum = 1 THEN t1.final_value END start_time,
       CASE WHEN rnum <> 1 THEN t1.final_value END end_time,
       CASE CAST(DATEDIFF(hour, (SELECT t2.final_value FROM td t2 WHERE t2.[day] = t1.[day] AND t2.rnum = 1),
                       t1.final_value) AS DECIMAl(5,2)) 
            WHEN 0 THEN NULL
            ELSE CAST(DATEDIFF(hour, (SELECT t2.final_value FROM td t2 WHERE t2.[day] = t1.[day] AND t2.rnum = 1),
                       t1.final_value) AS DECIMAl(5,2)) 
         END time_duration
  FROM td t1

Result

name    start_time              end_time                time_duration
Day_1   2015-05-06 02:30:00.000 NULL                    NULL
John    NULL                    2015-05-06 03:30:00.000 1.00
Ben     NULL                    2015-05-06 04:30:00.000 2.00
Mike    NULL                    2015-05-06 05:30:00.000 3.00
Day_2   2015-05-07 02:30:00.000 NULL                    NULL
John_2  NULL                    2015-05-07 04:30:00.000 2.00
Ben_2   NULL                    2015-05-07 05:30:00.000 3.00
Mike_2  NULL                    2015-05-07 06:30:00.000 4.00