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.
