Post reply

Need Help With SQL For Calculating Time Worked

  • February 28, 2006 at 4:57 pm

    #113596

    I have the following table structure and data:

    Calls

    CallID int

    CallType varchar(3)

    RecvdDate varchar(10)

    RecvdTime varchar(8)

    ClosedDate varchar(10)

    ClosedTime varchar(8)

    TimeSpent Decimal(6,2)

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

    Sample Record:

    CallID  CallType  RecvdDate   RecvdTime  ClosedDate   ClosedTime  TimeSpent

    1        ABC        2006-02-14  12:08:15    2006-02-23  12:11:23          0

     

    What I need to do is calculate the number of hours between RecvdDate +

    RecvdTime as StartDate and ClosedDate + ClosedTime as EndDate, as TimeSpent ,in hours

    exclusive of weekend dates, company holiday dates, and the hours between 8PM and 7AM for working days.  I have a calendar table which lists dates between 01/01/1997 and 12/31/2050 and has indicator flags if the day is a weekend and/or holiday.

    Any suggestions for the SQL?

     

  • February 28, 2006 at 5:58 pm

    #623833

    Select

    @StartTime = convert(datetime, RecvdDate + ' ' + RecvdTime),

    @EndTime = convert(datetime, ClosedDate + ' ' + ClosedTime)

    SELECT @TimeSpent = datediff(n, @StartTime, @EndTime)/60.0

    If you need seconds precision,

    SELECT @TimeSpent = datediff(ss, @StartTime, @EndTime)/3600.0

    P.S. What a stupid idea to have date and time 1st - separated, 2nd - in varchar!

    _____________
    Code for TallyGenerator

  • February 28, 2006 at 6:19 pm

    #623837

    Always Use the correct data type for your data & time storage, it makes things so so much easier.

    Also post your calendar DDL, more sample data & expected result.

  • February 28, 2006 at 7:17 pm

    #623844

    This script is based on RecvdDate & ClosedDate as datetime data type. Not knowing how your calendar table looks like, i assume a simple table to illustrate the codes.

    You can cut & paste the following into Query Analyser to see the result.

    create table #Calls

    (

    CallID int,

    CallType varchar(3),

    RecvdDate datetime,

    ClosedDate datetime,

    TimeSpent Decimal(6,2)

    )

    create table #Calendar

    (

    Calendar_Date datetime,

    Weekend int

    )

    insert into #Calendar

    select '2006-02-14', 0 union all

    select '2006-02-15', 0 union all

    select '2006-02-16', 0 union all

    select '2006-02-17', 0 union all

    select '2006-02-18', 1 union all

    select '2006-02-19', 1 union all

    select '2006-02-20', 0 union all

    select '2006-02-21', 0 union all

    select '2006-02-22', 0 union all

    select '2006-02-23', 0

    insert into #Calls

    select 1, 'ABC', '2006-02-14 12:08:15', '2006-02-23 12:11:23', 0 union all

    select 2, 'ABC', '2006-02-20 19:00:00', '2006-02-22 08:02:03', 0

    select CallID,

    sum(case when dateadd(day, 0, datediff(day, 0, a.RecvdDate)) = c.Calendar_Date then

    datediff(hour, RecvdDate, dateadd(hour, 20, c.Calendar_Date))

    when dateadd(day, 0, datediff(day, 0, a.ClosedDate)) = c.Calendar_Date then

    datediff(hour, dateadd(hour, 7, c.Calendar_Date), a.ClosedDate)

    else

    13

    end

    ) as [TimeSpent in Hours]

    from #Calls a inner join #Calendar c

    on dateadd(day, 0, datediff(day, 0, a.RecvdDate)) = c.Calendar_Date

    where c.Weekend = 0

    group by CallID

  • March 1, 2006 at 4:29 am

    #623905

    Why make things simple when there are so many beautiful ways of complicating them?

    I suppose, your Calendar doesn't use correct datetime-format either ...

    SELECT

    SUM(DATEDIFF(ss,convert(datetime, RecvdDate + ' ' + RecvdTime),convert(datetime, ClosedDate + ' ' + ClosedTime))  AS niceTime

    FROM tableCalls

    LEFT OUTER JOIN tblCalendar

    ON RecvdDate = CalendarDate

    WHERE CalendarDate IS NULL


    _/_/_/ paramind _/_/_/

  • March 1, 2006 at 4:34 am

    #623906

    >P.S. What a stupid idea to have date and time 1st - separated, 2nd - in varchar!<

    True. But I know where it originates Probably from some ill application interface design, driven by users complaining about having to enter the date part twice for each interval. Since there are no 4th GL programming languages available, this is of course a big problem ...


    _/_/_/ paramind _/_/_/

  • March 1, 2006 at 9:03 am

    #624022

    Everyone is correct about the ill-designed table. It is a commercial application design, not our own.

    Thanks for everyone's help.

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

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