Ryiun Ryiun - 2 months ago 8
SQL Question

SQL: Summing time when the time field is char type

I'm trying to move data from a table in one db to another. Everything is going fine except getting a total travel time from the travel time field which is set as a char(6) in H:MM format. I need to take the travel time for each stop in the route (given by dispatch number) for the given day (given by session date) and total it in a new field for that route.

Below is what I'm trying to do (Summing the char field of course wont work)

SELECT
[session_date], [dispatch_number], [stop_number],
[scheduled_arrival], [route_start], [customer_id],
[distance], [travel_time], [service_time],
[driver],
(SELECT SUM([travel_time])
FROM [exampleDB]
WHERE [dispatch_number] = [dispatch_number]
AND [session_date] = [session_date]) AS total_drive_time
FROM
[exampleDB]


I would greatly appreciate any help, thank you for your time.

Answer

First, you can write this without the correlated subquery using window functions:

select e.*,
       sum(travel_time) over (partition by dispatch_number, session_date) as total_drivetime
from exampleDB;

Of course, this doesn't fix the problem -- which is adding up varchars. You can convert it to a time and then to minutes:

select e.*,
       sum(datediff(minute, '00:00', convert(time, travel_time))) over
           (partition by dispatch_number, session_date
           ) as total_drivetime_min
from exampleDB;

If minutes is sufficient, then this solves your problem.

Comments