JackieLee
asked on
TSQL Total Week Days in Period
Hi,
I need to write some TSQL that will return to me the total number of Mondays, tuesdays, wednesdays, thursdays, fridays, saturdays and sundays in a given date period. It needs to be as efficient as possible. What is the best way to do this?
I need to write some TSQL that will return to me the total number of Mondays, tuesdays, wednesdays, thursdays, fridays, saturdays and sundays in a given date period. It needs to be as efficient as possible. What is the best way to do this?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I had fun writing this one...
alternative not as nice as hilaire's :)
declare @startDate as smallDateTime
declare @endDate as smallDateTime
declare @dateReached as int
set @startDate = getDate()
set @endDate = getDate() + 10
set @dateReached = 0
print (@startDate)
print (@endDate)
--Create Temp Table
create table #weekday(dayOfWeek varchar(15), [count] int)
while(@dateReached = 0)
begin
if(datePart(dd, @startDate) = datePart(dd, @endDate))
begin
set @dateReached = 1
end
else
begin
declare @startDateDay as varchar(20)
set @startDateDay = dateName(dw, @startDate)
if exists (select 1 from #weekDay where dayOfWeek = @startDateDay)
begin
update #weekDay set [count] = [count] + 1 where dayOfWeek = @startDateDay
end
else
begin
insert #weekDay (dayOfWeek, [count]) values (@startDateDay, 1)
end
end
set @startDate = @startDate + 1
end
select * from #weekDay
drop Table #weekDay
alternative not as nice as hilaire's :)
declare @startDate as smallDateTime
declare @endDate as smallDateTime
declare @dateReached as int
set @startDate = getDate()
set @endDate = getDate() + 10
set @dateReached = 0
print (@startDate)
print (@endDate)
--Create Temp Table
create table #weekday(dayOfWeek varchar(15), [count] int)
while(@dateReached = 0)
begin
if(datePart(dd, @startDate) = datePart(dd, @endDate))
begin
set @dateReached = 1
end
else
begin
declare @startDateDay as varchar(20)
set @startDateDay = dateName(dw, @startDate)
if exists (select 1 from #weekDay where dayOfWeek = @startDateDay)
begin
update #weekDay set [count] = [count] + 1 where dayOfWeek = @startDateDay
end
else
begin
insert #weekDay (dayOfWeek, [count]) values (@startDateDay, 1)
end
end
set @startDate = @startDate + 1
end
select * from #weekDay
drop Table #weekDay
If you can live with a maximum of 99 weeks in the date range, you can calculate the totals in one statement, although you would still have to split the final value into 7 different values. The code below will give you a single, 14-digit total where the first two digits are the total number of Mons, the next two are Tues, etc..
Note that if you can insure that the @startDate is before @endDate, you can remove the ABS() and replace the "CASE WHEN @startDate <= @endDate THEN @startDate ELSE @endDate END" with "@startDate". IMO, it is dangerous to code ABS() and then always use the start date (@d1) in the second part of the calculation; if I were looking at the code, I would believe after seeing the ABS() that the code could handle either date coming first.
DECLARE @startDate DATETIME
DECLARE @endDate DATETIME
DECLARE @day INT
SET @startDate = '04/05/2004'
SET @endDate = '04/12/2004'
SELECT (ABS(DATEDIFF(DAY, @startDate, @endDate)) / 7) * CAST(01010101010101 AS BIGINT) +
CAST(CASE WHEN ABS(DATEDIFF(DAY, @startDate, @endDate)) % 7 >= (15 + 1 + @@DATEFIRST -
DATEPART(WEEKDAY, CASE WHEN @startDate <= @endDate THEN @startDate ELSE @endDate END)) % 7
THEN '01' ELSE '00' END +
CASE WHEN ABS(DATEDIFF(DAY, @startDate, @endDate)) % 7 >= (15 + 2 + @@DATEFIRST -
DATEPART(WEEKDAY, CASE WHEN @startDate <= @endDate THEN @startDate ELSE @endDate END)) % 7
THEN '01' ELSE '00' END +
CASE WHEN ABS(DATEDIFF(DAY, @startDate, @endDate)) % 7 >= (15 + 3 + @@DATEFIRST -
DATEPART(WEEKDAY, CASE WHEN @startDate <= @endDate THEN @startDate ELSE @endDate END)) % 7
THEN '01' ELSE '00' END +
CASE WHEN ABS(DATEDIFF(DAY, @startDate, @endDate)) % 7 >= (15 + 4 + @@DATEFIRST -
DATEPART(WEEKDAY, CASE WHEN @startDate <= @endDate THEN @startDate ELSE @endDate END)) % 7
THEN '01' ELSE '00' END +
CASE WHEN ABS(DATEDIFF(DAY, @startDate, @endDate)) % 7 >= (15 + 5 + @@DATEFIRST -
DATEPART(WEEKDAY, CASE WHEN @startDate <= @endDate THEN @startDate ELSE @endDate END)) % 7
THEN '01' ELSE '00' END +
CASE WHEN ABS(DATEDIFF(DAY, @startDate, @endDate)) % 7 >= (15 + 6 + @@DATEFIRST -
DATEPART(WEEKDAY, CASE WHEN @startDate <= @endDate THEN @startDate ELSE @endDate END)) % 7
THEN '01' ELSE '00' END +
CASE WHEN ABS(DATEDIFF(DAY, @startDate, @endDate)) % 7 >= (15 + 7 + @@DATEFIRST -
DATEPART(WEEKDAY, CASE WHEN @startDate <= @endDate THEN @startDate ELSE @endDate END)) % 7
THEN '01' ELSE '00' END AS BIGINT)
Note that if you can insure that the @startDate is before @endDate, you can remove the ABS() and replace the "CASE WHEN @startDate <= @endDate THEN @startDate ELSE @endDate END" with "@startDate". IMO, it is dangerous to code ABS() and then always use the start date (@d1) in the second part of the calculation; if I were looking at the code, I would believe after seeing the ABS() that the code could handle either date coming first.
DECLARE @startDate DATETIME
DECLARE @endDate DATETIME
DECLARE @day INT
SET @startDate = '04/05/2004'
SET @endDate = '04/12/2004'
SELECT (ABS(DATEDIFF(DAY, @startDate, @endDate)) / 7) * CAST(01010101010101 AS BIGINT) +
CAST(CASE WHEN ABS(DATEDIFF(DAY, @startDate, @endDate)) % 7 >= (15 + 1 + @@DATEFIRST -
DATEPART(WEEKDAY, CASE WHEN @startDate <= @endDate THEN @startDate ELSE @endDate END)) % 7
THEN '01' ELSE '00' END +
CASE WHEN ABS(DATEDIFF(DAY, @startDate, @endDate)) % 7 >= (15 + 2 + @@DATEFIRST -
DATEPART(WEEKDAY, CASE WHEN @startDate <= @endDate THEN @startDate ELSE @endDate END)) % 7
THEN '01' ELSE '00' END +
CASE WHEN ABS(DATEDIFF(DAY, @startDate, @endDate)) % 7 >= (15 + 3 + @@DATEFIRST -
DATEPART(WEEKDAY, CASE WHEN @startDate <= @endDate THEN @startDate ELSE @endDate END)) % 7
THEN '01' ELSE '00' END +
CASE WHEN ABS(DATEDIFF(DAY, @startDate, @endDate)) % 7 >= (15 + 4 + @@DATEFIRST -
DATEPART(WEEKDAY, CASE WHEN @startDate <= @endDate THEN @startDate ELSE @endDate END)) % 7
THEN '01' ELSE '00' END +
CASE WHEN ABS(DATEDIFF(DAY, @startDate, @endDate)) % 7 >= (15 + 5 + @@DATEFIRST -
DATEPART(WEEKDAY, CASE WHEN @startDate <= @endDate THEN @startDate ELSE @endDate END)) % 7
THEN '01' ELSE '00' END +
CASE WHEN ABS(DATEDIFF(DAY, @startDate, @endDate)) % 7 >= (15 + 6 + @@DATEFIRST -
DATEPART(WEEKDAY, CASE WHEN @startDate <= @endDate THEN @startDate ELSE @endDate END)) % 7
THEN '01' ELSE '00' END +
CASE WHEN ABS(DATEDIFF(DAY, @startDate, @endDate)) % 7 >= (15 + 7 + @@DATEFIRST -
DATEPART(WEEKDAY, CASE WHEN @startDate <= @endDate THEN @startDate ELSE @endDate END)) % 7
THEN '01' ELSE '00' END AS BIGINT)
Of course if you need the best performances possible, take the code and put it directly in your SQL code instead of calling a funciton for each and every row
HTH
Hilaire