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").



select distinct

a.gender,

(select count(*) from HR_APPLICANTS where actioncode = 1 and

gender=a.gender) as hired,

(select count(*) from HR_APPLICANTS where actioncode = 2 and

gender=a.gender) as interviewed_declinedoffer,

(select count(*) from HR_APPLICANTS where actioncode = 3 and

gender=a.gender) as interviewed_nooffer,

(select count(*) from HR_APPLICANTS where actioncode = 4 and

gender=a.gender) as not_interviewed,

(select count(*) from HR_APPLICANTS where actioncode = 5 and

gender=a.gender) as phone_interview,

(select count(*) from HR_APPLICANTS where actioncode = 6 and

gender=a.gender) as declined_interview,

(select count(*) from HR_APPLICANTS where actioncode = 7 and

gender=a.gender) as temp_placement,

(select count(*) from HR_APPLICANTS where actioncode = 8 and

gender=a.gender) as leftmsg_noreturn,

(select count(*) from HR_APPLICANTS where actioncode = 9 and

gender=a.gender) as did_not_meet_quals,

(select count(*) from HR_APPLICANTS where actioncode = 10 and

gender=a.gender) as no_call_no_show,

(select count(*) from HR_APPLICANTS where actioncode = 11 and

gender=a.gender) as letter_sent

from hr_applicants a

where actiondate between ('6/26/2005') and ('7/26/2005')

group by a.gender



The SQL above returns the same number hired as:



. . .

where actiondate between ('4/1/2005') and ('7/26/2005')

. . .



even though the following:



select count(*) from hr_applicants

where actioncode = 1

and actiondate between ('6/26/2005') and ('7/26/2005')



does work as expected (giving a result of 3 records). if no date range is
used, 24 records are found; 13 if I use "between ('4/1/2005') and
('6/30/2005')".



Why is the SQL not working? And how do I need to change it so that the date
range is respected?



Clay Shannon,

Dimension 4 Software





[Non-text portions of this message have been removed]