I have a table in my SQL database that periodically tracks an employee. A typical record will look like this
ID Device DateTime Employee ID ----------------------------- - ------------------------------------------- 1 InReader 2013/05/05 08:00:00 1 2 InReader 2013/05/05 08:00:05 1 3 InReader 2013/05/05 08:01:00 2 InReader 2013/05/05 08:02:00 3 5 InReader 2013/05 / 05 08:03:00 4 6 Outreader 2013/05/05 17:00:00 1 7 Outreader 2013/05/05 17:05:05 2 8 Outreader 2013/05/05 17:05:10 29 Outreader 2013 / 05/05 17:10:00 3 10 Outreader 2013/05/05 17:30:00 4 Id is just an auto increment column
Device device I want to know, at the end of the day, when I am reporting a report Doing so, how can I reconcile with them from time to time, as if the output can look like this:
Employee ID timed out time -------- -------------------------------------------------- ------------- 1 2013/05/05 08:00:00 201 3/05/05 17:00:00 2 2013/05/05 08:01:00 2013/05 / 05 17:05:10 3 2013/05/05 08:02:00 2013/05/05 17:10: 00 4 2013/05/05 08:03:00 2013/05/05 17:30:00 Alerts:
- Notice that there are 2 records for Employee 1 "InReader", I want to record first - there are 2 records for "Outreader" of Employee 2, I only That Defense wants to latest records say
I could use it to use in and out records like LINQ? (Or TSQL if it is not possible in LINQ)
I created this query for you, and in LinqPad Have investigated. I'll give you full code so that you can try yourself.
Query auto:
Track. Group by (x = & gt; x.EmployeeId). Select (X => New {employee = x.cay, Inetime = X.Fastor Default (y = & gt; y.Device.Equals ("InReader")) Date Time, Outtime = X.list Order (y = & Gt; y.Device Integer sample sample: zero main () {var tracks = new [] {/ code> new track {id = 1, Equipment = "Inner", date time = new date time (2013,5,5,8,0,0), employee ID = 1}, new track {id = 2, device = "inner", date time = new date Time (2013,5,5,8,0,5), employee ID = 1}, new track {id = 3, tool = "inner", dina Cuttime = New date time (2013,5,5,8,1, 0), employee ID = 2}, new track {id = 4, device = "inner", date time = new date time (2013,5,5) , 8,2,0), employee ID = 3}, new track {id = 5, device = "inner", date = date = new date time (2013,5,5,8,3,0), employee ID = 4 }, New track {id = 6, appliance = "outreader", date = date = new date time (2013, 5,5,17,0,0), employee = 1}, new track {id = 7, tool = "Outreader ", Date time = new date time (2013,5,5,17,5,5), employee = 2}, new track {Id = 8, tool = "outrider", date = date = new date time (2013,5,5,17,5,10), employee ID = 2}, new track {id = 9, appliance = "outreader", date Time = New Date Time (2013,5,5,17,10,0), Employee ID = 3}, New Track {ID = 10, Device = "Outreader", Date Time = New Date Time (2013-2015) , 17,30,0), employee = 4},}; // Query Tracks.graphic (x => x.EmployeeId). Select (x = & gt; new {EmployeeId = x.Key, InTime = x.FirstOrDefault (y = & gt; y.Device.Equals ("InReader")) Date Time, Outtime = x.LastOrDefault (y = & Gt; y.Device.Equals ("Outreader")) Date Time}} Public Category Track {Public Ent ID (Get; Set;} Public String Device {get; Set;} Public Date Time Date Received {Set; ;} Public Interest Employee {Receive; Set;}}
No comments:
Post a Comment