Unanswered: Time difference medium in a stored procedure
Hello dbforums community!
For my first post I have a rather interesting and possibly challenging question.
I have a task database with start and end time columns formatted as nvarchar (50)
The data on these columns is formatted in the following format: "dd/mm/yyyy hh:mm"
What I am trying to achieve is a stored procedure that will return the medium task completion for each user (several tasks per user are registered on the database)
I was going to do this in vbscript on my asp page but my boss tells me it is achievable simply through sql on the stored procedure. Any ideia how?
I see three challenges/problems here:
-Extracting a "readable" time from the start and end time columns (I cant simply subtract and divide due to the format)
-dealing with tasks where end time is <NULL> (not yet ended - not to be considered)
-calculating the medium per user itself
I would welcome any example or relevating tutorial for solving this!
(database table view sample attached)
Thanks for the usefull reply.
I am running into some problems however converting the database colums to datetime (in my case smalldatetime)
- Unable to modify table.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Arithmetic overflow error converting expression to data type smalldatetime.
[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated.
I am simply changing the database columns to small date time. the text inside the column is formatted as dd/mm/yyyy hh:mm. Should this be enough?
Eventually deleted the test records on the database
I now have a stored procedure that returns me the completion time for each row:
SELECT responsavel, DATEDIFF(hour, hora, data_conclusao) AS tempo_medio
WHERE (pendente = 0)
ORDER BY responsavel
How do I add times from rows with the same "responsavel" and then divide by the count? (I assume a COUNT function is in order..)