Hi, I’m at my end here. Trying for 2 days and just don’t know how. I’ve got data:
Now starting 2020-11-23 (Monday) I need to Group those data by WeekDay, AND differentiate the StartTime BUT here’s the catch.
I need to group them to: “Ordinary”, “Monday”, “Friday”, “Saturday”, “Sunday”
When grouping Ordinary, I need to have an Average of “Tuesday”, “Wednesday”, “Thursday”
When grouping any other, I need to have a Sum of those days
The Resulting table should look like this: (without the Note column)
For now I’ve got this. But don’t know how to continue…
I don’t understand how do I Sum the special days and Average the Ordinary days.
Be aware that your usage of the GroupDayFilter (which is really a projection not a filter) will likely result in all the results being pulled into c# and not summarised on the database server. If this is intended to run over 10,000s of records, this won’t scale well.
To answer your question, you need to return tues/wed/thurs because ultimately you need values by those segments. You’d be best IMO to just calculate the sum or avg you need for each day individually as it will look less grotesque.
Not sure if it would work but maybe something more or less like this?
you don’t need the “FinishTime” in the group by clause
you can use a “sum or average” logic in the calculation of the count. You need to round the value since, average will result in a double value.
Also, depending upon the data type of start time, you may get surprising results. In general I convert date time data types to integer or string values before using them in group by. In this case, I would suggest that you convert Start Time to TimeSpan or String (hh::mm:ss) if you notice that, group by is behaving weirdly.
E.g.
C# devs
null reference exceptions