select
h.PUN,
DATE_FORMAT(convert_tz(h.event_timestamp_utc,'+00:00','-05:00'),'%m-%d-%Y') as LDate,
u.eid,
SUM(h.distance) OLM,
First(h.distance) - Last(h.distance) as OHM,
'' OBM
from event_log h
Inner Join users u
on u.id=h.user_id
WHere h.PUN is not Null and h.PUN not like '' AND h.deleted_at is Null
group by h.PUN,u.external_id,DATE_FORMAT(convert_tz(h.event_timestamp_utc,'+00:00','-05:00'),'%m-%d-%Y')
order by h.PUN,LDate,u.eid
var milesDataList = (from events in context.event_log
from user in context.users.Where(t=>t.id == events.uid)
group new { events, user } by new
{
events.pun,
user.eid,
events.event_timestamp_utc.Value.Date,
}).Select(x => new MilesData
{
eid = x.Key.eid,
pun= x.Key.pun,
LDate = x.Key.Date,
OLM= x.Sum(t=>t.events.distance).ToString(),
OHM= (x.LastOrDefault().events.distance - x.FirstOrDefault().events.distance).ToString(),
OBM= string.Empty
}).ToList();
