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?
LVL 1
JackieLeeAsked:
Who is Participating?
 
HilaireConnect With a Mentor Commented:
create function ufn_get_days(@d1 datetime, @d2 datetime, @day int)
returns int as
begin
return
abs(datediff(d, @d1, @d2)/7) -- 1 day per full week
+ case when abs(datediff(d, @d1, @d2)%7) >= (15+@day-@@datefirst-datepart(dw, @d1))%7 then 1 else 0 end
end

go

--to count number of mondays between @date1 and @date2
-- use 2 instead of 1 to count tuesdays, and so on to 7 for sundays
select dbo.ufn_get_days(@date1, @date2, 1)

Hilaire
0
 
HilaireCommented:
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

0
 
jayrodCommented:
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
0
 
Scott PletcherSenior DBACommented:
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)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.