Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2003
    Posts
    3

    Unanswered: SQL Select statement

    Below is a query for pulling data from the activity_table to ascertain, by jurisdiction, how long an actual activity takes an ACO . I am trying to find a way to TOTAL the column, AsTimePerCall, created by the second line in the query. The usual (sum) or (total) is not working.

    select activity_no, working_date, complete_date,
    complete_date - working_date AS TImePerCall
    from sysadm.activity where
    (jurisdiction = 'phoenix') and
    (act_result_1 <> 'TRNSF' and act_result_1 <> 'CNCLD' and act_result_1 <> 'NTIME')
    and (officer_id is not null)
    and (COMPLETE_date between '01-01-2003' and '01-10-2003')
    and (activity_stat <> 'CANCELLED')

    Thank you,

    Timothy

  2. #2
    Join Date
    Jan 2003
    Location
    London, England
    Posts
    106
    Wouldn't this do the trick:

    select activity_no, working_date, complete_date,
    SUM(DATEDIFF(mm, working_date, complete_date)) AS TImePerCall
    from sysadm.activity where
    Frettmaestro
    "Real programmers don't document, if it was hard to write it should be hard to understand!"

  3. #3
    Join Date
    Oct 2003
    Posts
    3
    Originally posted by Frettmaestro
    Wouldn't this do the trick:

    select activity_no, working_date, complete_date,
    SUM(DATEDIFF(mm, working_date, complete_date)) AS TImePerCall
    from sysadm.activity where
    Thank you for the quick response. After making the modificaiton I receive the following error:

    Server: Msg 8118, Level 16, State 1, Line 1
    Column 'sysadm.activity.complete_date' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause

  4. #4
    Join Date
    Jan 2003
    Location
    London, England
    Posts
    106
    select activity_no, SUMDATEDIFF(mm, working_date, complete_date)) AS TImePerCall
    from sysadm.activity where
    (jurisdiction = 'phoenix') and
    (act_result_1 <> 'TRNSF' and act_result_1 <> 'CNCLD' and act_result_1 <> 'NTIME')
    and (officer_id is not null)
    and (COMPLETE_date between '01-01-2003' and '01-10-2003')
    and (activity_stat <> 'CANCELLED')
    group by activity_no, working_date, complete_date

    Be aware that when using the SUM like we are doing here you will get the total amount of minutes spent on each activity.
    Frettmaestro
    "Real programmers don't document, if it was hard to write it should be hard to understand!"

  5. #5
    Join Date
    Oct 2003
    Posts
    3
    Originally posted by Frettmaestro
    select activity_no, SUMDATEDIFF(mm, working_date, complete_date)) AS TImePerCall
    from sysadm.activity where
    (jurisdiction = 'phoenix') and
    (act_result_1 <> 'TRNSF' and act_result_1 <> 'CNCLD' and act_result_1 <> 'NTIME')
    and (officer_id is not null)
    and (COMPLETE_date between '01-01-2003' and '01-10-2003')
    and (activity_stat <> 'CANCELLED')
    group by activity_no, working_date, complete_date

    Be aware that when using the SUM like we are doing here you will get the total amount of minutes spent on each activity.
    Thank you so much Frettmaestro!

Posting Permissions

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