Tsql Complex Query : Calculating Total timetaken for an event in set of events when we have starttime and endtime

Hi... I am trying to take data from a inspection/timeclock software and create useable reports for our payroll department.

My problem is that the data is in a difficult form to sum and I am not entirely sure how I can do this.

The employees perform inspections on their vehicle with a handheld unit.  For each row of data there is a starttime and an endtime which shows the beginning and ending times just for the inspection itself.  An inspection might take anywhere from 30 seconds to 15 minutes.

Before an employee starts a route, they must perform an inspection.  They will choose a category at the beginning of the inspection that will specify what type of route they are about to run (there could be any number of different route types).  They then perform the inspection (this is their IN time punch) and cradle the handheld unit, which will send the data up to the system.  The employee then begins his/her route. 
When the employee is finsihed with his/her route, they are required to run another inspection, which will signify their OUT time punch.

An employee could run a number of routes during the day.  If the employee performs the inspections like they should, there should be two punches for each route they run. 

My issue is that I have to group and sort these pairs of time punches and sum the total time for each category and person.
I have no idea how I can do this because each punch is in its own row of data and I have to use the "starttime" from the first punch for a route and the "endtime" for the second punch for the route as the beginning and end punches for the employee for that particular route type.  Then I have to figure out the difference in minutes between those two punches and display that on my report.

The data would look like this:

idoperatorroutetypeRouteTypeCategoryctimestampcstarttimecendtimeNumMins
8247Driver, Tory Bradley0Regular11/27/09 12:0011/27/09 12:0011/27/09 12:011.23
8248Driver, Tory Bradley0Regular11/27/09 13:3711/27/09 13:3711/27/09 13:370.48
8249Driver, Tory Bradley5Mid Day11/27/09 13:3811/27/09 13:3811/27/09 13:380.38
8250Driver, Tory Bradley5Mid Day11/27/09 13:5011/27/09 13:4911/27/09 13:500.47
8251Driver, Tory Bradley45Field Trip11/27/09 13:5111/27/09 13:5111/27/09 13:510.48
8252Driver, Tory Bradley45Field Trip11/27/09 13:5811/27/09 13:5811/27/09 13:580.4
8253Driver, Tory Bradley0Regular11/27/09 13:5911/27/09 13:5911/27/09 14:011.65
8254Driver, Tory Bradley0Regular11/27/09 14:3711/27/09 14:3611/27/09 14:370.62
8255Driver, Tory Bradley0Regular11/27/09 14:5711/27/09 14:5711/27/09 14:580.72
8256Driver, Tory Bradley0Regular11/27/09 15:0211/27/09 15:0211/27/09 15:030.68

From that data you can see that the driver was Tory Bradley.  She ran a REGULAR route type first and the beginning inspection for that started at 12 noon.  She finished the inspection at 12:01pm, so the inspection took 1 minute and 23 seconds to complete.
She then ran her route.  She finished the route and ran an inspection at 13:37 and that inspection was finished within 48 seconds.

To find her total hours, I would need to take the "cstarttime" from the first inspection for the REGULAR type as her IN punch and the "cendtime" from her second inspection for the REGULAR type as her OUT punch.

Likewise, you can see that she ran another route with a type of MID DAY after the first route and you can see her punches for that route.  Then she ran a FIELD TRIP route and then two REGULAR routes.

Ideally, I would be able to produce a report that shows something like this:

Driver:  Tory Bradley
-- Route Type  -- Duration (# minutes)
REGULAR  --  149 minutes
MIDDAY    --   12 minutes
FIELDTRIP --     7 minutes

------- Tory Bradley Total:  168 minutes

Solution :


Here is the correct query that calculates the output given your latest set of sample data:

DECLARE @ZonarInspections TABLE
(
   id INT,
   operator VARCHAR(30),
   opid INT,
   routetype INT,
   ctimestamp DATETIME,
   cstarttime DATETIME,
   cendtime DATETIME,
   NumMins NUMERIC(18,8)
)

DECLARE @ZonarInspectionsData TABLE
(
   RouteType INT,
   RouteTypeCat VARCHAR(20)
)

INSERT INTO @ZonarInspectionsData
SELECT 0,  'Regular' UNION ALL
SELECT 1, 'Route Type 1' UNION ALL
SELECT 5,  'Mid' UNION ALL
SELECT 43, 'Route Type 43' UNION ALL
SELECT 45, 'Field Trip' UNION ALL
SELECT 63, 'Route Type 63'

INSERT INTO @ZonarInspections
SELECT 8235, 'Manager, Michael Waters', 60, 0, '11/25/09 8:18', '11/25/09 8:18', '11/25/09 8:19', 1.63 UNION ALL
SELECT 8236, 'Manager, Michael Waters', 60, 0, '11/25/09 8:41', '11/25/09 8:41', '11/25/09 8:41', 0.45 UNION ALL
SELECT 8237, 'Manager, Michael Waters', 60, 1, '11/25/09 8:52', '11/25/09 8:52', '11/25/09 8:53', 0.42 UNION ALL
SELECT 8238, 'Manager, Michael Waters', 60, 1, '11/25/09 9:13', '11/25/09 9:13', '11/25/09 9:14', 0.47 UNION ALL
SELECT 8239, 'Manager, Michael Waters', 60, 43, '11/25/09 9:33', '11/25/09 9:33', '11/25/09 9:34', 0.68 UNION ALL
SELECT 8240, 'Manager, Michael Waters', 60, 43, '11/25/09 9:40', '11/25/09 9:40', '11/25/09 9:41', 0.58 UNION ALL
SELECT 8241, 'Manager, Michael Waters', 60, 45, '11/25/09 9:42', '11/25/09 9:41', '11/25/09 9:42', 0.5 UNION ALL
SELECT 8242, 'Manager, Michael Waters', 60, 45, '11/25/09 10:13', '11/25/09 10:12', '11/25/09 10:13', 0.5 UNION ALL
SELECT 8243, 'Manager, Michael Waters', 60, 63, '11/25/09 12:14', '11/25/09 12:13', '11/25/09 12:14', 0.68 UNION ALL
SELECT 8244, 'Manager, Michael Waters', 60, 63, '11/25/09 13:10', '11/25/09 13:10', '11/25/09 13:11', 0.58 UNION ALL
SELECT 8245, 'Manager, Michael Waters', 60, 0, '11/25/09 15:09', '11/25/09 15:09', '11/25/09 15:09', 0.37 UNION ALL
SELECT 8247, 'Driver, Tory Bradley', 55, 0, '11/27/09 12:00', '11/27/09 12:00', '11/27/09 12:01', 1.23 UNION ALL
SELECT 8248, 'Driver, Tory Bradley', 55, 0, '11/27/09 13:37', '11/27/09 13:37', '11/27/09 13:37', 0.48 UNION ALL
SELECT 8249, 'Driver, Tory Bradley', 55, 5, '11/27/09 13:38', '11/27/09 13:38', '11/27/09 13:38', 0.38 UNION ALL
SELECT 8250, 'Driver, Tory Bradley', 55, 5, '11/27/09 13:50', '11/27/09 13:49', '11/27/09 13:50', 0.47 UNION ALL
SELECT 8251, 'Driver, Tory Bradley', 55, 45, '11/27/09 13:51', '11/27/09 13:51', '11/27/09 13:51', 0.48 UNION ALL
SELECT 8252, 'Driver, Tory Bradley', 55, 45, '11/27/09 13:58', '11/27/09 13:58', '11/27/09 13:58', 0.4 UNION ALL
SELECT 8253, 'Driver, Tory Bradley', 55, 0, '11/27/09 13:59', '11/27/09 13:59', '11/27/09 14:01', 1.65 UNION ALL
SELECT 8254, 'Driver, Tory Bradley', 55, 0, '11/27/09 14:37', '11/27/09 14:36', '11/27/09 14:37', 0.62 UNION ALL
SELECT 8255, 'Driver, Tory Bradley', 55, 0, '11/27/09 14:57', '11/27/09 14:57', '11/27/09 14:58', 0.72 UNION ALL
SELECT 8256, 'Driver, Tory Bradley', 55, 0, '11/27/09 15:02', '11/27/09 15:02', '11/27/09 15:03', 0.68


;WITH typeCTE AS
(
  SELECT  *
        , time_punch_type = 2 - (ROW_NUMBER() OVER (PARTITION BY zi.opid, zi.RouteType ORDER BY zi.ctimestamp) % 2)
  FROM    @ZonarInspections zi
)
SELECT     zi_start.opid
        , zi_start.operator
        , zi_start.RouteType
        , zid.RouteTypeCat
        , total_nummins = SUM(zi_start.nummins) + SUM(zi_end.nummins)
        , total_duration_minutes = SUM(DATEDIFF(minute, zi_start.cstarttime, zi_end.cendtime))
FROM      typeCTE                    zi_start
JOIN      @ZonarInspectionsData      zid
ON        zi_start.RouteType       = zid.RouteType
LEFT JOIN typeCTE                    zi_end
ON        zi_start.opid            = zi_end.opid
AND       zi_start.RouteType       = zi_end.RouteType
AND       zi_start.cendtime        = (
                                       SELECT  MAX(x.cendtime)
                                       FROM    typeCTE             x
                                       WHERE   x.cendtime        < zi_end.cstarttime
                                       AND     x.opid            = zi_end.opid
                                       AND     x.RouteType       = zi_end.RouteType
                                       AND     x.time_punch_type = 1
                                     )
AND       zi_end.time_punch_type   = 2
WHERE     zi_start.time_punch_type = 1
GROUP BY  zi_start.opid
        , zi_start.operator
        , zi_start.RouteType
        , zid.RouteTypeCat
ORDER BY  zi_start.opid, zi_start.RouteType
This is the output generated:

opidoperatorRouteTypeRouteTypeCattotal_numminstotal_duration_minutes
55Driver, Tory Bradley0Regular5.38141
55Driver, Tory Bradley5Mid0.8512
55Driver, Tory Bradley45Field Trip0.887
60Manager, Michael Waters0Regular2.4523
60Manager, Michael Waters1Route Type 10.8922
60Manager, Michael Waters43Route Type 431.268
60Manager, Michael Waters45Field Trip132
60Manager, Michael Waters63Route Type 631.2658


Comments

Post a Comment

Popular posts from this blog

SSAS : A connection cannot be made to redirector. Ensure that 'SQL Browser' service is running. (Microsoft.AnalysisServices.AdomdClient). + Solution

Problem : have installed Analysis service on my server machine through remote login from my service account. I am able to open Analysis services from there. When I open SSMS from the same service account on my local machine, I am not able to connect to that Analysis service. It is giving me error by saying : "A connection cannot be made to redirector. Ensure that 'SQL Browser' service is running. (Microsoft.AnalysisServices.AdomdClient). A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connection host has failed to respond 10.245.192.111:2382(System)." Solution : Based on my research, the issue was caused by the Browser Service account was changed to the domain account. In your scenario, you can try to connect to the Analysis Service using <Servername>:2383 instead of instance name. Or you can change the Browser Service account to "Local System".
Read more

SSIS Error : Unable to infer the XSD from the XML file. The XML contains multiple namespaces

Image
Problem : When we have Multiple Namespaces in the Xml Source which we need to load the Data from   as we shown below Then we will face a issue in generating XSD for that XML Error : Unable to infer the XSD from the XML file. The XML contains multiple namespaces. (Microsoft Visual Studio) Solution : First we need to avoid multiple namespaces to transform your source file to a format that doesn’t refer to the namespaces. SSIS has an XML task that can do the transformation. 1) Add the XML task to an SSIS   Control Flow and edit it. 2) Change the OperationType property value to XSLT, the SourceType to File connection and the Source to your source file that has the problem. 3) Set the SaveOperationResult property to True and expand the OperationResult branch. Set DestinationType to File Connection and the Destination to a new XML file ( ModifiedInput.XML). 4) Add the below script to a new file and save it with an xslt file extension (Input.XSLT) <
Read more