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.
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;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With