Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Select only first record within a time interval

I have following table

CREATE TABLE [dbo].[DeviceLogs](
    [DeviceLogId] [int] IDENTITY(1,1) NOT NULL,

    [UserId] [nvarchar](50) NULL,
    [LogDate] [datetime2](0) NULL,
)
GO

Data Sample

   1     1    2013-05-29 11:05:15   //accepted (its the first occurance for userid 1)
   2     1    2013-05-29 11:05:20   //discarded (within 5 mins from 1st record)
   3     1    2013-05-29 11:07:56   //discarded (within 5 mins from 1st record)
   4     1    2013-05-29 11:11:15   //accepted (after 5 mins from 1st occurance)
   5     2    2013-05-29 11:06:05   //accepted (its the first occurance for userid 2)
   6     2    2013-05-29 11:07:18   //discarded (within 5 mins from 1st record)
   7     2    2013-05-29 11:09:38   //discarded (within 5 mins from 1st record)
   8     2    2013-05-29 11:12:15   //accepted (after 5 mins from 1st occurance)

I want to select only records which have occured after 5 mins from previous selected record and including the first record within the dataset

Desired output is

 1     1     2013-05-29 11:05:15   
 4     1     2013-05-29 11:11:15
 5     2     2013-05-29 11:06:05 
 8     2     2013-05-29 11:12:15

I am trying GroupBy but doesn't give date

db.DeviceLogs.GroupBy(g=>new {g.LogDate.Year, 
                              g.LogDate.Month, 
                              g.LogDate.Day, 
                              g.LogDate.Hour, 
                              g.LogDate.Minutes, 
                              g.UserID})
             .Select(s=>new {UserID=s.Key.UserID, s.???});

Thank you in advance.

like image 436
Marshal Avatar asked May 29 '13 08:05

Marshal


1 Answers

var result =
    from log in db.DeviceLogs
    let byId = 
        db.DeviceLogs.Where(item => item.UserId == log.UserId)
    let first =
        byId.First(item => item.LogDate == byId.Min(min => min.LogDate))
    where 
        log.Equals(first) || (log.LogDate - first.LogDate).Minutes > 5
    select log;
like image 148
Jan P. Avatar answered Sep 21 '22 06:09

Jan P.