November 26, 2015 at 10:06 pm
Good day,
I developed a solution to finding the number of a given day of week between two dates. My solution involves using a function and I am wondering if there is a way to implement something like what I have put together, except without relying on a function?
As always, all help is greatly appreciated. I learn a ton from the work that is posted here, and I hope some of my solution below can be of use to others if they come across the same problem.
Create the temporary test data
create table ##TempMyDates
(
StartDate date,
EndDate date,
DOW varchar(3),
)
INSERT INTO ##TempMyDates VALUES
('11/01/2015','11/30/2015','Wed'),
('10/01/2015','10/31/2015','Sun'),
('09/01/2015','09/30/2016','Sat'),
('07/01/2015','07/31/2016','Mon');
Function to calculate the number of instances of the day of week between the dates. I got the majority of this code (ie: 99% of it) from this website. Thank you to "SSCrazy" who posted such an excellent solution. I modified his solution into a function as I did not know how to make it work referencing another table containing multiple start and end dates.
IF OBJECT_ID(N'NumDOWBetween', N'FN') IS NOT NULL
DROP FUNCTION NumDOWBetween ;
GO
CREATE FUNCTION NumDOWBetween
(
@DayName Varchar(3) ,
@StartDate DATE,
@EndDate DATE
)
RETURNS INT
AS
BEGIN
DECLARE @NumOfDays INT
DECLARE @NumDOWBetween INT
SET @NumOfDays = DATEDIFF(DD,@StartDate , @EndDate) + 1 ;
WITH Tens AS
(
SELECT 1 N UNION ALL
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1
),
HUNDREDS AS
(
SELECT T1.N FROM TENS T1 CROSS JOIN TENS T2
),
THOUSANDS AS
(
SELECT T1.N FROM HUNDREDS T1 CROSS JOIN HUNDREDS T2
),
Numbers AS
(
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 0)) RN FROM THOUSANDS
)
SELECT TOP 1
@NumDOWBetween = count(DATEADD( DD,(RN - 1) , @StartDate ))
FROM
Numbers
WHERE
RN <= @NumOfDays AND
LEFT(DATENAME ( WEEKDAY, (DATEADD( DD,(RN - 1) , @StartDate )) ),3) = @DayName
RETURN @NumDOWBetween;
END
SQL query to pull the results (which appears to work properly)
select startdate, enddate, DOW, dbo.NumDOWBetween(DOW,startdate,enddate) as DOWCount
from dbo.##TempMyDates
November 27, 2015 at 7:23 am
Another option is to figure out the first and last day of week within the interval and get the number of weeks . Using iTVF
create function NumDOWBetween (@StartDate DATETIME, @EndDate DATETIME, @thisday varchar(3))
returns table as
return select
nofd = case
when firstthisday > lastthisday then 0
else datediff(week, firstthisday, lastthisday) + 1 end
from (
select
wdn = datepart(dw,dateadd(d,n,'20010101')),
wds = datename(weekday,dateadd(d,n,'20010101'))
from (values (1),(2),(3),(4),(5),(6),(7)) as t(n)
) t2
cross apply(
select
firstthisday = case when datepart(dw,@startdate) <= wdn
then @startdate + wdn - datepart(dw,@startdate)
else @startdate + 7 - wdn + datepart(dw,@enddate)
end,
lastthisday = case when datepart(dw,@enddate) >= wdn
then @enddate + wdn - datepart(dw,@enddate)
else @enddate - 7 + wdn - datepart(dw,@enddate)
end
) interval
where left(wds,3) = @thisday;
go
November 27, 2015 at 9:31 am
Thank you for the reply. Is there a way to implement your solution without it being a function, where it pulls the start date, end date, and day of week from a table?
November 27, 2015 at 10:25 am
This isn't really all that complex, and we can use code that doesn't require any specific date setting(s). I used a CROSS APPLY just to get the date calc out of the main query.
select *, dbo.NumDOWBetween
(
DOW,
StartDate,
EndDate) AS Function_Result
,CASE WHEN startdate_DOW > enddate THEN 0 ELSE
1 + DATEDIFF(DAY, startdate_DOW, EndDate) / 7 END AS Query_Result
from ##TempMyDates
cross apply (
select DATEADD(DAY, -DATEDIFF(DAY, CASE DOW WHEN 'Mon' THEN 0 WHEN 'Tue' THEN 1 WHEN 'Wed' THEN 2
WHEN 'Thu' THEN 3 WHEN 'Fri' THEN 4 WHEN 'Sat' THEN 5 ELSE 6 END,
DATEADD(DAY, 6, StartDate))%7, DATEADD(DAY, 6, StartDate)) as startdate_DOW
) as find_first_dow_on_or_past_startdate
Edit: Put SQL code in a code block. Hopefully the names used explain the code, but if you need additional clarification, naturally please just say so.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
November 29, 2015 at 6:05 pm
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.
November 30, 2015 at 2:32 am
This code is language-independent
Select CHARINDEX(SUBSTRING('SAT',1,3), 'SUNMONTUEWEDTHUFRISAT') / 3 + 1
while DATEPART() is language dependent. Mixing them may be dangerous. Consider using language dependent code instead
select wdn
from (
select
wdn = datepart(dw,dateadd(d,n,'20010101')), -- any date will do
wds = left(datename(weekday,dateadd(d,n,'20010101')),3) --the same date
from (values (1),(2),(3),(4),(5),(6),(7)) as t(n)
) as t
where wds='sat'
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply