[RESOLVED] Need help with SqlCE select query

[cicatrix]

I have three tables that are combined into one datatable using this query and I have problems with the part highlighted in red:

SELECT CONTRACTORS.ID, 
           CONTRACTORS.LASTNAME + ' ' + CONTRACTORS.NAME + ' ' + CONTRACTORS.MIDDLENAME AS CONTFULLNAME, 
                CONTRACTORS.CONTRACT, FIRMS.FIRMID, FIRMS.SHORTNAME AS FIRMSHORTNAME, FIRMS.FULLNAME AS FIRMFULLNAME, 
                DIRECTORS.LASTNAME + ' ' + DIRECTORS.NAME + ' ' + DIRECTORS.MIDDLENAME AS DIRECTORFULL, 
                DIRECTORS.LASTNAME + ' ' + SUBSTRING(DIRECTORS.NAME, 1, 1) + '. ' + SUBSTRING(DIRECTORS.MIDDLENAME, 1, 1) 
                + '.' AS DIRECTORSHORT
FROM      CONTRACTORS INNER JOIN
                FIRMS ON CONTRACTORS.FIRM = FIRMS.FIRMID INNER JOIN
                DIRECTORS ON FIRMS.FIRMID = DIRECTORS.FIRM
WHERE   (DIRECTORS.ONDUTYSINCE <= @pDate)
ORDER BY CONTFULLNAME

Basically I need to get the LATEST (active) director of the firm (some firms have history of two-three directors). The distinctive field is 'ONDUTYSINCE' which has the appointment date of a new director. Now I only check whether the appointment date is less than some date parameter I pass, but the query returns all directors who were active before, not just the latest one.
How should I modify this query to get only LAST one but ignore those who were appointed AFTER the pDate.

Example:

+------------+---------------+------+
| DIRECTOR   | ONDUTYSINCE   | FIRM |
+------------+---------------+------+
| John       |   01/01/2008  |    1 |
+------------+---------------+------+
| Peter      |   05/03/2009  |    1 |
+------------+---------------+------+
| Donald     |   01/01/2009  |    2 |
+------------+---------------+------+
| Alex       |   02/04/2010  |    1 |
+------------+---------------+------+
| David      |   01/01/2010  |    2 |
+------------+---------------+------+

Say, if I need to know who was the director of the firm #1 on June 1st, 2009 what should I write into a select query? (I need only PETER in this example)

Is it possible with a SINGLE query in SqlCE?