Results 1 to 10 of 10
  1. #1
    Join Date
    Jun 2003
    Posts
    4

    Angry Unanswered: Is it possible in single SQL statement

    Does anyone know how should I write the sql for getting the following result?

    Original Table like below.
    -------------------------------
    [WorkDay] [AgentCode]
    06/12/01 3
    06/12/02 2
    06/12/02 3
    06/12/03 2
    06/12/03 3
    -------------------------------

    Curernt SQL:

    When I put an "agentcode=2" in 'WHERE' clause, the result does not have '06/12/01' row.

    Example,
    SELECT DISTINCT WorkDay, AgentCode FROM MasterScheduleTransaction WHERE AgentCode=2
    -------------------------------
    [WorkDay] [AgentCode]
    06/12/02 2
    06/12/03 2
    -------------------------------

    I would like to know the agent is in the specified date.
    The expected result like below.
    -------------------------------
    [WorkDay] [AgentCode]
    06/12/01 NULL
    06/12/02 2
    06/12/03 2
    -------------------------------

    Please help its urgent

  2. #2
    Join Date
    Nov 2003
    Posts
    9
    why would the result have a row for 06/12/01 when you are telling it to get the rows for agentcode=2... it will discard all the rows having agentcode <> 2.
    I think you need to use a self outer join to get the expected result.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Code:
    SELECT DISTINCT 
     Workday, 
     CASE WHEN AgentCode = 2 THEN 
      2 
     ELSE 
      NULL 
     END AS AgentCodeIfTwo
    FROM  OriginalTable
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    May 2006
    Posts
    12
    or when your table is called justanotherday

    Code:
    SELECT DISTINCT
               a.workday, b.agentcode
    FROM
               justanotherday a 
    LEFT JOIN
               justanotherday b
    ON
               (a.workday = b.workday AND b.agentcode = 2)
    Last edited by Edwardvb; 06-01-06 at 06:26.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I suppose if you are trying to kill time and want to scan the table twice...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    May 2006
    Posts
    12


    Well, that shouldn't be too hard... in five years are only 1825 date records... so....

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    ...assuming only 1 record per day, in which case why select DISTINCT? There is no way to tell how many records are in the table.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    May 2006
    Posts
    12
    no... i mean.. it's 'only' 1825 records against x records in 5 years.... if x is not extremely large, no SQL interpreter should have problems with it in my opinion... or am i totally wrong here?

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    The issue isn't that there would be a noticable difference in performance against small record sets. The issue is that the solution you proposed is sub-optimal, and might encourage someone to use the same method against larger databases. We try to propose (and frequently argue about) "best practices" on this forum. If you think that the algorithm you proposed is more efficient than Pootle's method, then take the opportunity to justify your opinion.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    Join Date
    May 2006
    Posts
    12
    ok... you are right about that... my solution is not really an optimal one

Posting Permissions

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