I have a query in which one of the fields contains the count of status. The status can change several times a day and the count should be based on the final status of the day. For instance, this is what I have for Status1.

CountStatus1 = (from status in MyDataContext.StatusHistories
     where status.UserID == TheUserID
     where status.StatusDateTime.Month == TheMonth.Month
     where status.StatusDateTime.Year == TheMonth.Year
     where status.NewStatus == 1 // where the LAST STATUS OF THE DAY == 1
     select status.StatusID).Count()

The problem is that I want to select the last status of the day to be equal to 1, and count those. The status for a day can change from 1 to 4 to 2 to 5 to 3 and then to finally to 1; if I write the query like this, the count will include 2 1's and then the 4,2,5 and 3 will also be counted in CountStatus4, CountStatus3, CountStatus"n".

The return data is a monthly report grouped by day, where each day is a row.

The structure of the query looks like this:

var OutputStatusReport = from w in MyDataContext.WorkHistory
    where w.UserID == TheUserID
    where w.WorkDatetime.Month == TheMonth.Month
    where w.WorkDatetime.Year == TheMonth.Year
    group w by w.Datetime.Date into daygroups
    select new MyObjectModel
    {
         CountStatus1 = ....,
         CountStatus2 = ....,
         CountStatus3 =......
    };

So I need the day of the count to match the day of daygroups.

I'm struggling to figure this one out and any help is very welcome.

Thanks.