Quick way to subtract weekend hours when using datediff?

Question

Post reply

Quick way to subtract weekend hours when using datediff?

aaa121

SSCertifiable

Points: 5902
More actions
June 15, 2007 at 8:32 am

#153638

Hi All,
I am currently a datediff function to produce the number of hrs between an event beginning and an event completing. I need to be able to subtract the weekend hours from this figure when the code is executed. Does anyone have any smart ways of doing this please?
Thanks,
Adam

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

-=JLK=- SSCertifiable Points: 5315 More actions · Answer 1

See if this script will help:

http://www.sqlservercentral.com/scripts/viewscript.asp?scriptid=1320

James.

Jason- SSCrazy Points: 2795 More actions · Answer 2

The Link JLK posted will work and is a good script, (I promise I'm not trying to one up you JLK) I just spent time writting this so I'll post it anyway.

I haven't quite gotten the hang of the concept in this article yet (http://www.sqlservercentral.com/columnists/jSebastian/2944.asp) but if you do, it will speed up the population of @tblHours

---------------------------------------------

-- CREATE A TABLE TO HOLD SOME SAMPLE DATA --

---------------------------------------------

IF EXISTS(Select 1 from sysobjects where id = OBJECT_ID(N'tblTaskCompletion'))

DROP TABLE tblTaskCompletion

CREATE TABLE tblTaskCompletion (iTaskCompletionId INT IDENTITY(1,1) PRIMARY KEY,iTaskId INT,vcTaskName VARCHAR(255),dtTaskStartDate DATETIME,dtTaskEndDate DATETIME)

----------------------------------------------

-- INSERT SOME SAMPLE DATA TO THE NEW TABLE --

----------------------------------------------

INSERT INTO tblTaskCompletion

Select 1,'My Task that does''t span weekends','2007-06-04 15:07:33.000','2007-06-08 22:54:21.000'

UNION

Select 1,'My Task that does span weekends','2007-06-01 08:00:00.000','2007-06-26 02:00:00.000'

UNION

Select 2,'My Long Task','2005-01-01 14:00:43.000','2050-03-16 16:23:42.000'

UNION

Select 3,'My 4 day 24 task','2007-06-08 14:44:00.000','2007-06-11 14:44:00.000'

UNION

Select 3,'My Task That Ends on a weekend','2007-06-08 23:00:00.000','2007-06-09 16:43:02.000'

UNION

Select 1,'My Task That is only on weekend','2007-06-09 08:00:43','2007-06-10 12:30:00.00'

UNION

Select 10,'My 100 Year Task','1910-01-01','2010-01-01'

GO

-------------------------------------------------------

-- START THE PROCESS TO CALCULATE HOURS W/O WEEKENDS --

-------------------------------------------------------

/* This will create a memory table that holds each hour of the weekends between a start and

and stop range. Then using this table the process can then calculate the hours w/o weekends

in a set based select statement. */

SET NOCOUNT ON

DECLARE @tblHours TABLE (iRowId INT IDENTITY(1,1) PRIMARY KEY,dtDate DATETIME,iDayOfWeek INT)

/* If date ranges are typically large this table should be created and populated separately

so that the query time does not take too long to populate each hour. Do this by creating

a table and adding all dates between say 1901 and 2050 then execute only the

Select ... from tblTaskCompletion portion */

DECLARE @dtStartDate DATETIME

DECLARE @dtEndDate DATETIME

DECLARE @dtTrackDate DATETIME

SELECT @dtStartDate = min(dtTaskStartDate),@dtEndDate = max(dtTaskEndDate) from tblTaskCompletion

SET @dtTrackDate = cast(convert(varchar(30),@dtStartDate,110) as datetime)

WHILE @dtTrackDate <= dateadd(dd,1,cast(convert(varchar(30),@dtEndDate,110) as datetime)) BEGIN

INSERT INTO @tblHours Select @dtTrackDate,datepart(dw,@dtTrackDate)

SET @dtTrackDate = dateadd(hh,1,@dtTrackDate)

END

Select iTaskCompletionId

,iTaskId

,vcTaskName

,dtTaskStartDate

,dtTaskEndDate

-- The substraction of an hour prevents an hour being counted when the task ended during that hour

-- to count hours any time a minute of the hour is included remove the dateadd and leave just the column

,(Select count(*) from @tblHours where dtDate between dtTaskStartDate and dateadd(hh,-1,dtTaskEndDate)) - (select count(*) from @tblHours where dtDate between dtTaskStartDate and dateadd(hh,-1,dtTaskEndDate) and iDayOfWeek in (1,7)) iHoursNotWeekends

,(Select count(*) from @tblHours where dtDate between dtTaskStartDate and dtTaskEndDate) iHoursWeekends

From tblTaskCompletion t1

SET NOCOUNT OFF

-

-=JLK=- SSCertifiable Points: 5315 More actions · Answer 3

No problem Jason. I started down that path but then thought someone has to have done something similiar and a quick search turned up the script I posted a link to. Decided to be lazy and see if the original poster could figure it out from there. I'm sure he will appreciate your extra effort. I usually find it challenging and fun to do the grunt work myself but I was/am working a problem of my own and didn't have the time to dedicate to it.

James.

Sergiy SSC Guru Points: 110208 More actions · Answer 4

Everything is much simpler.

SELECT Datediff(wk, @WeekDay1, @WeekDay2)

will always return number of weekends between 2 weekdays.

Multiply it by 48 and get number of hours.

_____________
Code for TallyGenerator

aaa121 SSCertifiable Points: 5902 More actions · Answer 5

Thanks for your help all. I am going to use Sergiy's example. This will work as my completed date will always be on a weekday. If it was on a Saturday the datediff would still return 1 but I would need to multiply it by 24 instead of 48.

However, it works great for my sceanrio.

Thanks All!