Calculate Hours into Date Range without weekends

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Calculate Hours into Date Range without weekends

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-07-12 : 11:18:57

Uriel Bravo writes "Uriel Bravo writes "I am trying to create a list of how many hours counting into a date range (startdate/enddate) without weekend days Saturday/Sunday, a employee work in our system.

So if an employee has registered 8 hours by day and the request was created with the datestamp "2006-01-01 00:00:00.000" which is a sunday, and I run the report on "2006-01-15 00:00:00.000" I would get 80 hours (Just the Absolute number of hours by day it means 24 hours by day) because we have
day 1,7,8,14 and 15 as weekdays.

Any experts out there who can give me a helping hand?""

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-12 : 11:21:23

Make use of F_TABLE_DATE here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519&SearchTerms=F_TABLE_DATE

barry
Starting Member

14 Posts

Posted - 2006-07-12 : 17:38:14

This function may help; you could get the number of work days between dates

create function workdays_between
(@date_1 datetime, @date_2 datetime)
RETURNS INT
AS
BEGIN
DECLARE @startdate datetime, @enddate datetime, @weeks int, @days int
IF @date_1 > @date_2
BEGIN
SET @startdate = @date_2
SET @enddate = @date_1
END
ELSE
BEGIN
SET @startdate = @date_1
SET @enddate = @date_2
END
--SELECT @startdate = dbo.add_workdays(@startdate,0)
--SELECT @enddate = dbo.add_workdays(@enddate,0)
SELECT @weeks = DATEDIFF(day, @startdate, @enddate) / 7
SELECT @days = DATEDIFF(day, @startdate, @enddate) % 7
IF (DATEPART(dw, @enddate -1) < DATEPART(dw, @startdate -1))
BEGIN
SELECT @days = @days - 2
END
RETURN (@weeks * 5) + @days
END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources