?
Solved

TSQL Total Week Days in Period

Posted on 2004-04-08
4
Medium Priority
?
1,831 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?
0
Comment
Question by:JackieLee
  • 2
4 Comments
 
LVL 26

Accepted Solution

by:
Hilaire earned 2000 total points
ID: 10784134
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
 
LVL 26

Expert Comment

by:Hilaire
ID: 10784146
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
 
LVL 3

Expert Comment

by:jayrod
ID: 10784457
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
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 10784481
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

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question