Reporting - Calculating Time on Dispatch

 I am working on some SSRS reports that do calculations on Tech Time.  I am looking the table DispTech.  There is a Dispatch Date, Dispatch Time, Time On, Date Off, and Time Off.  The problem I am coming into is that sometimes the Dispatch date and Time is like 8/23 23:00, but the Time On is 00:015 with Date Off/Time Off of 8/24 03:00.  The problem is to get a Starting Date stamp I combine the Dispatch Date with the Time On field, but in this case it makes it look like the tech worked about 27 hrs.  Is there any one else doing this type of reporting and if so how are you handling it?  Thanks.

0

Comments

7 comments
  • Here is a small snip query to show you an example of what it seems like you are trying to get.

    select DATEDIFF(MINUTE, dispdate + TimeOn, dateoff + TimeOff) as ManHours from DispTech

    I am uncertain why some of your "TimeOn" fields are showing 00:015 but let me know if the query above extract the info you were looking for. I can also try to craft something up for you if you let me know what the exact data is your are looking to extract. Below is an example of a query we use (not including our specific WHERE clauses that narrow the searching down) that pulls a techs time for a given period. This is just a simple query that can be modified for lots of different data pulls. 

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

    Declare @start varchar(30) = '7/31/17'
    Declare @end varchar(30) = '8/6/17'
    Declare @emp varchar(30) = 'Bret O'


    SELECT distinct Dispatch.Dispatch, Dispatch.Invoice, (Dispdate + TimeOn) as StartTime,(Dateoff + Timeoff) as EndTime, Employee.EmpName AS Tech, Customer.LastName, Customer.FirstName, round(convert(float,(DATEDIFF(MINUTE, dispdate + TimeOn, dateoff + TimeOff)))/60,2) as ManHours, isnull(Sales.InvAmount,0) InvAmount

    FROM Sales INNER JOIN
    SalesLed ON Sales.Invoice = SalesLed.Invoice RIGHT OUTER JOIN
    Dispatch INNER JOIN
    DispTech ON Dispatch.Dispatch = DispTech.Dispatch INNER JOIN
    Employee ON DispTech.ServiceMan = Employee.EmpNo INNER JOIN
    Customer ON Dispatch.CustNo = Customer.CustNo LEFT OUTER JOIN
    SalesEmp ON DispTech.Dispatch = SalesEmp.Dispatch ON SalesLed.Invoice = Dispatch.Invoice

    WHERE DispDate >= @start and DateOff <= @end
    and Employee.EmpName = @emp

    ORDER BY StartTime

    0
    Comment actions Permalink
  • I had asked a while back about this one whether it would be approved or not. I am fine with it not being approved if this is not want you would like on the community board, but I would just like to know that. Thanks

    0
    Comment actions Permalink
  • Sorry about that Luke! Looks like Zendesk marked that original comment of yours as spam. Nooooo idea why. Approved! =)

    0
    Comment actions Permalink
  • Thanks Luke.  I had a type in my original one so it would have been 00:15.  I use something like you have there the problem is when they get dispatched on 9/19 23:30, but their time on is 00:15.  The query then make is looked like 9/19 00:15 instead of 9/20 00:15 and the calc then shows he worked over 27 hours instead of 3 hours if he was off on 9/20 03:15.  

    0
    Comment actions Permalink
  • What status (status code) triggers this behavior? Travelling? So for instance when a tech punches into travelling it marks date dispatched and time dispatched but not time on until they mark it working? Is kinda how its working for you?

    Thanks!

    0
    Comment actions Permalink
  • Hi, Here is a thought for you. You can run the queries below and see the output, they are safe to run, no updates, deletes, inserts or anything can just copy and paste and you get 4 outputs showing different things.

    You will see that what it does is calculate time only off of the DateOff column. How the logic works though is if the DateDiff between DateOff +TimeOn  and DateOff + TimeOff is negative then it will use the day before to calculate the time. It breaks if you have someone punched into a job with no breaks for longer than 24 hours, but normally we never see that...(and I think its illegal too).

    Anyway, thought I would craft it up and see if it helps at all, Ill still play around with it some and see if I can generate any other ideas. All you have to do is replace the dispatch at the top with your dispatch. 

    ======================================================

    Declare @dispatch varchar(30) = '50023'

    select * from DispTech where Dispatch = @dispatch

    --This is Time as we would normally calculate it
    select Dispatch,serviceman, round(convert(float,(DATEDIFF(MINUTE, dispdate + TimeOn, dateoff + TimeOff)))/60,2) "Non Modified Time" from DispTech where Dispatch = @dispatch

    --This is time if we only used DateOff and not dispdate, will calculate what you are looking for but not intellegent enough to handle days worked from day to day
    select Dispatch,serviceman, round(convert(float,(DATEDIFF(MINUTE, DateOff + TimeOn, DateOff + TimeOff)))/60,2) "Time only using DateOff" from DispTech where Dispatch = @dispatch

    /*This has the logic built in it so that it will run the 2nd query using only the DateOff and calculating the total time and if it is negative it will calculate the 1st query using the original date.
    This breaks if someone works more than 24 hours straight in a day without punching out as the time will overlap the original time.*/
    SELECT Dispatch, serviceman,
    CASE
    WHEN round(convert(float,(DATEDIFF(MINUTE, DateOff + TimeOn, dateoff + TimeOff)))/60,2) > 0 then round(convert(float,(DATEDIFF(MINUTE, DateOff + TimeOn, dateoff + TimeOff)))/60,2)
    ELSE round(convert(float,(DATEDIFF(MINUTE, DATEADD(DAY,-1,DateOff) + TimeOn, dateoff + TimeOff)))/60,2)
    END as "Corrected Time"


    FROM DispTech
    WHERE Dispatch = @dispatch

    0
    Comment actions Permalink
  • Luke thanks for the query.  I'll take look and looks like it should get me closer to where i need to be.

    Jason

    0
    Comment actions Permalink

Please sign in to leave a comment.

Didn't find what you were looking for?

New post