How to find total number Mondays in a time period

[D1234]

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

[dougp]

I'm using Microsoft SQL and have a Date dimension in my database.  Here's what I would do:

Code Select Expand

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.

[D1234]

Thanks

I will try this