Post reply

Quick way to subtract weekend hours when using datediff?

  • 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

  • June 15, 2007 at 10:30 am

    #713319

    See if this script will help:

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

    James.

  • June 15, 2007 at 1:09 pm

    #713379

    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://qa.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

    -

  • June 15, 2007 at 2:00 pm

    #713392

    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.

  • June 15, 2007 at 5:34 pm

    #713428

    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

  • June 18, 2007 at 2:15 am

    #713507

    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!

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

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