We help IT Professionals succeed at work.

TSQL Total Week Days in Period

JackieLee
JackieLee asked
on
Medium Priority
1,884 Views
Last Modified: 2007-12-19
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?
Comment
Watch Question

Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Commented:
This function is not affected by langage settings (ie SET DATESFIRST won't affect the results)

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

Commented:
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
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
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)
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.