Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2009
    Posts
    5

    Question 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)
    Attached Thumbnails Attached Thumbnails sample.JPG  

  2. #2
    Join Date
    Jun 2009
    Posts
    5
    uh, and this should probably be on the SQL Server section my bad - if a mod could move...

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Moved to SQL Server topic
    George
    Home | Blog

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Well your first step is to change the data type of your column to datetime - once you've done this the problem is probably trivial.

    And no excuses, you can change the column

    Also, what do you mean by the "medium"? Median/mean?
    George
    Home | Blog

  5. #5
    Join Date
    Jun 2009
    Posts
    5
    Quote Originally Posted by gvee
    Well your first step is to change the data type of your column to datetime - once you've done this the problem is probably trivial.

    And no excuses, you can change the column

    Also, what do you mean by the "medium"? Median/mean?
    Median!
    And thanks for the move =)

  6. #6
    Join Date
    Jun 2009
    Posts
    26
    Hi vafo,
    As gvee said - if you can change your column type to a datetime it's best - otherwise you're going to have to do coversions from chars to datetime which is nastier.

    Since you've only got two date values the median is going to be the mean value in this case (see Median - Wikipedia, the free encyclopedia)
    Which means your algorithm is going to look like:

    medium task completion = start_date + (end_date-start_date)/2

    The SQL is going to look something like this:
    median_completion=dateadd(dd, (datediff(dd,convert(start_date),convert(end_date) )/2 ) convert(start_date,datetime) )

  7. #7
    Join Date
    Jun 2009
    Posts
    5
    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
    Last edited by vafo; 06-22-09 at 10:56.

  8. #8
    Join Date
    Jun 2009
    Posts
    5
    Updates:
    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
    FROM dbo.SinfRegister
    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..)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •