General approach when needing to calculate values between rows

Question

For the following table :

Id    Timestamp    AssignedTo
1     2012-01-01   User1
2     2012-01-02   User2
3     2012-01-10   User3
4     2012-01-15   User1

what would be the general approach in MS SQL Server to calculating how many days/hours/minutes the entity was assigned to a specific user?

As a developer (with basic SQL knowledge), I've always opted for Functions or Stored procedures when doing these kind of queries (these kind = queries including results based on the difference between two rows in a set).

In this example, I'd have a cursor iterating over the items, keeping the previous one in a variable, and then calculating the difference. However, I've been told that this is a serious performance concern, especially on large sets.

EDIT: Sample results

User    TotalTime
User1   23d
User2   8d
User3   5d

i.e. the total time that an item was assigned to a specific user. User1's TotalTime is 23 days because it has been assigned to him since 2012-01-15 (and it's 2012-02-06 now), along with the first day it was assigned to him.

Depending on the database, you could use advanced SQL features, so what database is this? Besides, can you provide some example output? I'm not sure what result you'd expect... — Lukas Eder, Feb 06 '12 at 09:19
Too bad it's MSSQL/SQL Server, so you cannot use `ROWS 1 PRECEDING` and similar window function constructs. But check out this question here, it'll contain some answers to a similar problem: http://stackoverflow.com/questions/860966/calculate-a-running-total-in-sqlserver — Lukas Eder, Feb 06 '12 at 09:48
@El because it was assigned to `User2` on these days: 2 3 4 5 6 7 8 9, which is eight days — AakashM, Feb 06 '12 at 10:11

GarethD · Accepted Answer · 2012-02-06T10:13:08.167

In this example I'd use a subquery within the select statement to get the end date for each row, then use this end date to get the time assigned to a specific user. The below is SQL Server syntax but the same principal can be applied whatever the RDBMS.

SELECT  AssignedTo, 
        SUM(DATEDIFF(DAY, TimeStamp, ISNULL(EndDate, GETDATE()))) [Days]
FROM    (   SELECT  ID,
                    [TimeStamp],
                    AssignedTo,
                    (   SELECT  MIN(TimeStamp)
                        FROM    [YOURTABLE] b
                        WHERE   b.TimeStamp > a.TimeStamp
                    ) [EndDate]
            FROM    [YOURTABLE] a
        )  a
GROUP BY AssignedTo

Even though I was looking for a general approach, this answer lead me to it... a practical example of what I believe is called set theory. — Marcel, Feb 06 '12 at 18:55

General approach when needing to calculate values between rows

1 Answers1