Hi,

>I thought this SQL was working, but I found out that it returns the same
> number hired (actioncode = 1 = Hired) regardless of the date range I enter
> for "actiondate").

<snip>

That's because there's no "date"-relation between the sub-selects and your main table.

Anyway, you could speed up this query if you use Firebird 1.5 by using the CASE expression

SELECT>
a.gender,
SUM(CASE WHEN a.actioncode = 1 THEN 1 ELSE 0 END) as hired,
SUM(CASE WHEN a.actioncode = 2 THEN 1 ELSE 0 END) as interviewed_declinedoffer,
SUM(CASE WHEN a.actioncode = 3 THEN 1 ELSE 0 END) as interviewed_nooffer,
SUM(CASE WHEN a.actioncode = 4 THEN 1 ELSE 0 END) as not_interviewed,
....
FROM
hr_applicants a
WHERE
a.actiondate between ('6/26/2005') and ('7/26/2005')
GROUP BY
a.gender


Regards,
Arno Brinkman
ABVisie

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
General database development support:
http://www.databasedevelopmentforum.com

Firebird open source database (based on IB-OE) with many SQL-99 features :
http://www.firebirdsql.org
http://www.firebirdsql.info
http://www.fingerbird.de/
http://www.comunidade-firebird.org/

Support list for Interbase and Firebird users :
firebird-support@yahoogroups.com

Nederlandse firebird nieuwsgroep :
news://newsgroups.firebirdsql.info