Post reply

Help! Business Days Question

  • December 18, 2004 at 12:05 pm

    #61501

    I have a Helpdesk Application that I need to know the lapse time from DateRecieved and Assigned Date. Also from DateRecieved to DateClosed ...etc. Below gives me the time in Minutes which is what I want but I want to exclude weekends and if at all possible exclude non-working hours. I would like it to output 1 Day 12 Hours 23 Minutes??? Is this possible?

    I also have a table that has all of the weekend dates in it. I am using NVARCHAR for the field types.

    SELECT     ROUND(DATEDIFF(s, PRBRCVDT, ASSIGNDT), 2) / 60 AS timelapse

    FROM         dbo.HELPDESKMAIN

    WHERE     (ASSIGNDT IS NOT NULL)

    Thank you in advance for any help!!!

    Jason

  • December 18, 2004 at 11:17 pm

    #534403

    I have used a lookup table.

    CREATE TABLE dbo.BusinessDays (

    BusinessDay datetime NOT NULL PRIMARY KEY CLUSTERED)

    go

    Load the table with dates, starting with the first business day of the year:

    TRUNCATE TABLE dbo.BusinessDays

    declare @i smallint

    SET @i = 0

    WHILE @i < 365

    BEGIN

      INSERT INTO dbo.BusinessDays

      SELECT DateAdd(dd, @i, '2005-01-03')

      SET @i = @i + 1

    END

     

    ALTER TABLE dbo.BusinessDays ADD DayNumber smallint IDENTITY (1, 1)

     

    declare @DateReceived datetime, @DateAssigned datetime, @ElapsedDays smallint

    SET @DateReceived = '2005-01-13 8:00AM'

    SET @DateAssigned = '2005-01-17 12:05PM'

    SET @ElapsedDays =

    (SELECT DayNumber FROM dbo.BusinessDays

      WHERE BusinessDay = CONVERT( varchar(8), @DateAssigned, 112)) -

    (SELECT DayNumber FROM dbo.BusinessDays

      WHERE BusinessDay = CONVERT( varchar(8), @DateReceived, 112))

    PRINT CAST(@ElapsedDays AS varchar(3))

    There is no "i" in team, but idiot has two.

  • December 18, 2004 at 11:19 pm

    #534404

    That's odd, the page redacted most of my comments.  Right before the ALTER TABLE you need to manually edit the table, deleting the rows which have dates that are not business days (weekends and holidays).  Also, you have to figure out the time component, but I am out of time myself here.  Maybe someone else can chip in.  Luck, Dave

    There is no "i" in team, but idiot has two.

  • December 20, 2004 at 5:14 pm

    #534550

    Can you show me an example?

Viewing 4 posts - 1 through 3 (of 3 total)

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