CODEWITHSUNDEEP
c#sqllinq
Tommy
Tommy

Reputation: 465

SQL Time Duration Between Records

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.

Upvotes: 0

Views: 173

Answers (1)

Tim Schmelter
Tim Schmelter

Reputation: 460028

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

Upvotes: 1

Related Questions