Calculate Time Duration Between Differents Records In A Table Based On Datetimes

For the question, Let's say I have a table that holds the following data : 1) Name 2) Mood 3) DateTime I could insert records like : Andy Happy '11.06.2012 - 14.06.07' -- In

Solution 1:

A possible SQL version:

WITH CTE AS(
    SELECTROW_NUMBER()OVER(PARTITIONBY [Name] ORDERBY [Time])As RowNum
    , *FROM@table T
)
SELECT 
    DiffSec=DATEDIFF(s,[Time],(SELECT [Time] FROM CTE c2 WHERE c2.[Name]=CTE.[Name] AND c2.RowNum=CTE.RowNum+1))
    , [Name]
    , Mood
    , [Time]
FROM CTE 
ORDERBY [Name],[RowNum]

Result:

DiffSecNameMoodTime1678    AndyHappy2012-06-11 14:06:07.000NULLAndySad2012-06-11 14:34:05.0005228    ArthurAngry2012-06-11 14:06:57.000NULLArthurHappy2012-06-11 15:34:05.000

Your test data:

declare@tabletable(name varchar(10),mood varchar(10),time datetime);
insertinto@tablevalues('Andy','Happy',convert(datetime,'11.06.2012 14:06:07',104));
insertinto@tablevalues('Arthur','Angry',convert(datetime,'11.06.2012 14:06:57',104));
insertinto@tablevalues('Andy','Sad',convert(datetime,'11.06.2012 14:34:05',104));
insertinto@tablevalues('Arthur','Happy',convert(datetime,'11.06.2012 15:34:05',104));

---

Edit Self-Joining a CTE seems to be a very bad idea ("If you self join the CTE it will kill you"). I've tested my query with 500000 records in a temporary table and cancelled the query after 30 minutes.

Here's a much faster approach(4 seconds for all) using a sub-query (with your commented schema):

SELECT T.*
    ,(SELECT DATEDIFF(s,MAX(T2.Time),T.Time)
      FROM dbo.Temp T2
      WHERE T2.HE_Id = T.HE_Id
          AND T2.Time < T.Time
    ) AS DiffSec
FROM dbo.Temp AS T
ORDERBY HE_Id ,DiffSec