Solved

TSQL Total Week Days in Period

Posted on 2004-04-08
4
1,794 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 500 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 69

Expert Comment

by:ScottPletcher
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

867 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now