Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Location
    Ft. Lauderdale, FL
    Posts
    2

    Red face Unanswered: SELECT MAX(Time)

    I thought this would be an easy query - but it isn't.

    This is a support call tracking app. that we have running here. We have support calls and notes that are continually added to them throughout the day. I want to be able to query the list of open support calls along with the information on the last support call note. I can do it by using my web code (ColdFusion in case anyone cares) to loop queries, but that means running tons and tons of queries. I'd really like to return the results in a single query. Here's what I have so far:

    Tables:

    tblSupportCalls
    SupportCallID
    EmployeeID
    SupportCallDesc (Not needed in this query.)
    SupportCallStatus

    tblSupportCallNotes
    SupportCallNoteID
    SupportCallID
    EmployeeID
    SupportCallNoteTime
    SupportCallNoteDesc (Not needed in this query.)

    Code:
    SELECT    tblSupportCalls.SupportCallID,
              tblSupportCalls.EmployeeID,
              tblSupportCallNotes.EmployeeID AS LastEmployeeId,
              tblSupportCallNotes.SupportCallNoteTime AS LastSupportCallNoteTime
    FROM      tblSupportCalls LEFT OUTER JOIN
              tblSupportCallNotes ON
              tblSupportCalls.SupportCallID = tblSupportCallNotes.SupportCallID
    WHERE     (tblSupportCalls.SupportCallStatus = 0)
              AND
              (
              tblSupportCallNotes.SupportCallNoteTime IN
                   (SELECT     MAX(SupportCallNoteTime)
                   FROM          tblSupportCallNotes
                   GROUP BY SupportCallID)
              OR
              tblSupportCallNotes.SupportCallNoteTime IS NULL
              )
    ORDER BY  tblSupportCalls.SupportCallID DESC
    The problem with this query is that it is returning multiple records and I can't figure out why. I don't know if this is important, but we are using SQL Server 2000.

    Thanks in advance for anyone who can shed a little light on my problem.

    —Supes

  2. #2
    Join Date
    Dec 2002
    Posts
    134

    Re: SELECT MAX(Time)

    Try something like below (I have no MS SQL Server to try, but it should work):

    SELECT
    *
    FROM
    tblSupportCalls calls
    left outer join
    (
    SELECT notes.*
    FROM
    tblSupportCallNotes notes,
    (SELECT SupportCallID, MAX(SupportCallNoteTime) SupportCallNoteTime
    FROM tblSupportCallNotes
    GROUP BY SupportCallID) lastNote
    WHERE
    notes.SupportCallID = lastNote.SupportCallID
    AND notes.SupportCallNoteTime = lastNote.SupportCallNoteTime
    ) notes
    ON notes.SupportCallID = calls.SupportCallID
    WHERE
    calls.SupportCallStatus = 0
    ORDER BY calls.SupportCallID DESC


    In general I would suggest to add a column to tblSupportCalls and track the last tblSupportCallNotes

  3. #3
    Join Date
    Feb 2004
    Location
    Ft. Lauderdale, FL
    Posts
    2

    Talking Re: SELECT MAX(Time)

    Simply amazing! It worked perfectly. I can't thank you enough.

    I'll be honest, though: I can't quite follow just *what* you did. Is there possibly some resource (book, web site) that can explain it to me?

    My most humble thanks.

    —Supes

  4. #4
    Join Date
    Dec 2002
    Posts
    134

    Re: SELECT MAX(Time)

    Originally posted by Supes1491
    Simply amazing! It worked perfectly. I can't thank you enough.

    I'll be honest, though: I can't quite follow just *what* you did. Is there possibly some resource (book, web site) that can explain it to me?

    My most humble thanks.

    —Supes
    The idea is pretty basic

  5. #5
    Join Date
    Dec 2002
    Posts
    134

    Re: SELECT MAX(Time)

    1. "create table" that represents last notice time per call (lastNote)
    2. Join the result back to notice table to get last notice information, effectively selecting only one notice per call
    3. left outer join between calls and last notices (call may have no notices)

Posting Permissions

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