I figured out how to calculate the number of a certain weekday in a date range, without using a function.

I came across a solution in VBA that was done in Excel, and replicated it in SQL. The VBA code is:

=INT((WEEKDAY('START DATE'-'DAY OF WEEK INDEX')+('END DATE'-'START DATE'))/7)

Where day of week index is, 1=Sunday, 7=Saturday.

My SQL code is as follows:

Temp test data:

create table ##TempMyDates

(

StartDate date,

EndDate date,

DOW varchar(3),

)

INSERT INTO ##TempMyDates VALUES

('2015-11-01','2015-11-30','Sun'),

('2015-11-01','2015-11-30','Mon'),

('2015-11-01','2015-11-30','Tue'),

('2015-11-01','2015-11-30','Wed'),

('2015-11-01','2015-11-30','Sat'),

('2015-10-01','2015-10-31','Sun'),

('2015-10-01','2015-10-31','Mon'),

('2015-10-01','2015-10-31','Wed'),

('2015-10-01','2015-10-31','Sat'),

('2015-09-01','2015-09-30','Sun'),

('2015-09-01','2015-09-30','Mon'),

('2015-09-01','2015-09-30','Tue'),

('2015-09-01','2015-09-30','Wed'),

('2015-09-01','2015-09-30','Sat')

As you will notice, my day of week is stored as truncated day name, not an integer.

To get the Weekday integer, I solved this using some sql code I found. http://stackoverflow.com/questions/387434/converting-the-name-of-a-day-to-its-integer-representation

Select CHARINDEX(SUBSTRING('SAT',1,3), 'SUNMONTUEWEDTHUFRISAT') / 3 + 1

Using this, in the above Excel VBA formula, this is what I came up with:

cast((datepart(weekday,DATEADD(d, DATEDIFF(d,'1899-12-30',StartDate)-CHARINDEX(SUBSTRING(DOW,1,3), 'MONTUEWEDTHUFRISATSUN') / 3 + 1,'1899-12-30'))+DATEDIFF(d,StartDate,EndDate))/7 as int) as NumberOfDOW

One thing to note. I wanted to get my date serial numbers from SQL Server to match what Excel outputs. I will also be using Excel to look at some of this data, so to trouble shoot any potential problems between SQL Server and Excel, I wanted to make sure the date serial values match. Excel converts dates into serial starting from 01/01/1900. To get the same output in SQL Server, you have to use 1899-12-30. I read this somewhere on the net and do not have the URL.

Here is my final query to go with the test data:

SELECT

[StartDate]

,[EndDate]

,[DOW]

,cast((datepart(weekday,DATEADD(d, DATEDIFF(d,'1899-12-30',StartDate)-(CHARINDEX(SUBSTRING(DOW,1,3), 'SUNMONTUEWEDTHUFRISAT') / 3 + 1),'1899-12-30'))+DATEDIFF(d,StartDate,EndDate))/7 as int) as CountDOW

FROM [dbo].[##TempMyDates]

I tested it using quite a few dates and it appears to work, but if anyone finds a problem please respond. I hope this helps others who need a similar solution.