Hello,
I have a report right now with a filter on a date field for a total of about 4 months. I need to find the average of one field. In order to accomplish this, I need to know the total number of Mondays, Tuesdays etc within this time period. I have already set up a working Day of the week field which gives me the numerical value of the day of the week. I need to find a way to total the number of Wednesdays for example, that appear.
The list is essentially like this:
[Date] [Day of week] [Field X]
How would I find the total appearance of each day of the week for [Field X]
Thanks
I'm using Microsoft SQL and have a Date dimension in my database. Here's what I would do:
declare @start datetime
declare @end datetime
set @end = getdate()
set @start = cast({ts '2017-11-07 13:00:00.000'} as datetime)
select count(*) as MondayCount
from [Date] d
where d.FullDate between @start and @end
and d.DayName = 'Monday'
...where the variables above are replaced by the date parameters in your report.
Thanks
I will try this