SQL Time Duration Between Records

Question

I have the following table:

CamId   RegNumber   DateSeen
5   G1234B  18/02/2014 11:54
3   G1234B  18/02/2014 11:51
5   G11854  18/02/2014 11:50
3   G11854  18/02/2014 11:49
3   G24581  18/02/2014 11:48

I need to know the time taken from when a registration number is seen at CamId 3 to CamId 5, a reg number must exist in both CamId 3 and 5 for this to work.

The result i am looking for is a list of registration numbers together with a time difference in seconds (for the purpose of this demo in minutes):

RegNumber   Duration
G1234B             3
G11854             1

I then want to add up all these durations and get the median or average value.

Hopefully someone can assist, a linq sql statement would be ideal.

yes it would have to match on the same regnumber and the date of camid 3 must be less to that of camid5 — Tommy, Feb 18 '14 at 11:04
there can also be other entries but it must get the last two available for a reg number. e.g. there might be 10 entries on cam id 5 and 10 entries on cam id 3 but it should use the latest values only — Tommy, Feb 18 '14 at 11:05

Tim Schmelter · Answer 1 · 2014-02-18T13:29:38.637

You can use Enumerable.GroupBy, then select the latest record with CamId == 5, subtract it with the earliest record with CamId == 3 and use TimeSpan.TotalSeconds.

var query = db.Registration
    .GroupBy(r => r.RegNumber)
    .Select(grp => new 
    {
        RegNumber = grp.Key,
        Duration = (grp.Where(r => r.CamId == 5)
                      .OrderByDescending(r => DateSeen)
                      .Select(r => r.DateSeen)
                      .FirstOrDefault()  
                  - grp.Where(r => r.CamId == 3)
                      .OrderBy(r => DateSeen)
                      .Select(r => r.DateSeen)
                      .FirstOrDefault()).TotalSeconds
    });

Update: "Would you be able to provide the above in an SQL statement?"

WITH CTE AS
(
     SELECT [CamId], [RegNumber], [DateSeen],
       Duration = DATEDIFF(second, 
                          (SELECT MIN(DateSeen)FROM dbo.Registration r2
                           WHERE r1.RegNumber=r2.RegNumber
                           AND   r2.CamId = 3),
                          (SELECT MAX(DateSeen)FROM dbo.Registration r2
                           WHERE r1.RegNumber=r2.RegNumber
                           AND   r2.CamId = 5)),
       RN = ROW_NUMBER() OVER (PARTITION BY RegNumber ORDER BY DateSeen)
     FROM dbo.Registration r1
)
SELECT [RegNumber], [Duration]
FROM CTE 
WHERE [Duration] IS NOT NULL AND RN = 1

Demo

@Tommy: There is no `Where` on the main query so it will return all records grouped by `RegNumber`. What does `query.ToList().Count` return? — Tim Schmelter, Feb 18 '14 at 11:56
returns an error "ArgumentException was unhandled" DbArithmeticExpression arguments must have a numeric common type. — Tommy, Feb 18 '14 at 12:00
@Tommy: you haven't mentioned entity framework, have a look: http://stackoverflow.com/questions/9820401/dbarithmeticexpression-arguments-must-have-a-numeric-common-type — Tim Schmelter, Feb 18 '14 at 12:16
@Tommy: edited my answer. Change `DATEDIFF(second ...` to `DATEDIFF(minute ...` if you want to see the minutes instead. — Tim Schmelter, Feb 18 '14 at 13:29

SQL Time Duration Between Records

1 Answers1