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)
[session_date], [dispatch_number], [stop_number],
[scheduled_arrival], [route_start], [customer_id],
[distance], [travel_time], [service_time],
WHERE [dispatch_number] = [dispatch_number]
AND [session_date] = [session_date]) AS total_drive_time
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.