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.
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
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
Sorry about that Luke! Looks like Zendesk marked that original comment of yours as spam. Nooooo idea why. Approved! =)
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.
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!
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
Luke thanks for the query. I'll take look and looks like it should get me closer to where i need to be.
Jason
Please sign in to leave a comment.