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 urgentwhy 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.|||SELECT DISTINCT
Workday,
CASE WHEN AgentCode = 2 THEN
2
ELSE
NULL
END AS AgentCodeIfTwo
FROM OriginalTable|||or when your table is called justanotherday

SELECT DISTINCT
a.workday, b.agentcode
FROM
justanotherday a
LEFT JOIN
justanotherday b
ON
(a.workday = b.workday AND b.agentcode = 2)|||I suppose if you are trying to kill time and want to scan the table twice...|||:D

Well, that shouldn't be too hard... in five years are only 1825 date records... so...|||...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.|||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?|||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.|||ok... you are right about that... my solution is not really an optimal one :)