asp.net - count of last item of the day -

i have query in 1 of fields contains count of status. status can change several times day , count should based on final status of day. instance, have status1.

countstatus1 = (from status in mydatacontext.statushistory      status.userid == theuserid      status.statusdatetime.month == themonth.month      status.statusdatetime.year == themonth.year      status.newstatus == 1 // last status of day == 1      select status.statusid).count() 

the problem want select last status of day equal 1, , count those. status day can change 1 4 2 5 3 , 1; if write query this, count include 2 1's , 4,2,5 , 3 counted in countstatus4, countstatus3, countstatus"n".

the return data monthly report grouped day, each day row.

the structure of query looks this:

var outputstatusreport = w in mydatacontext.workhistory     w.userid == theuserid     w.workdatetime.month == themonth.month     w.workdatetime.year == themonth.year     group w w.datetime.date daygroups     select new myobjectmodel     {          countstatus1 = ....,          countstatus2 = ....,          countstatus3 =......     }; 

so need day of count match day of daygroups.

i'm struggling figure 1 out , welcome.

thanks.

this set operations question, , there couple ways this:

1) each instance there multiple conditions, select condition value wanted

select mydesiredinformation
tables
allmyotherconditions ,
mymultiplecondition = max(select allcases of multiple condition)

2) join table on itself, comparing multiple conditions, , take 1 meets desired criteria

select mydesiredinformation
thetablewithmultipleconditions t1 left outer join thetablewithmultipleconditions t2
allmyotherconditions ,
t1.magicvalue > t2.magicvalue , t2.value null

there other ways, 1 of these enough solve problem have described.

---

edit: in response comments

var query = status in mydatacontext.statushistory             status.userid == theuserid             status.statusdatetime.month == themonth.month             status.statusdatetime.year == themonth.year             status.newstatus == 1              status.statusupdatetime == (                  status in mydatacontext.statushistory                  (all conditions above)                  select status.statusupdatetime).max()             select status.statusid 

this implement #1 , can see implementing #2 not different.