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.