SQLTeam.com | Weblogs | Forums

Selecting multiple criterias

Hi. I was wondering if I could get some assistance with the question below.

Find all the male employees born between 1962 to 1970 and with hire date greater than 2001

and female employees born between 1972 and 1975 and hire date between 2001 and 2002.

I have attempted the question with the query below but it only provides me with information on the Male employees and when I try using AND instead of OR I get blank results. Thank you

SELECT

[BusinessEntityID],[BirthDate],[Gender],[HireDate]

FROM [HumanResources].[Employee]

WHERE

(

[Gender] = 'M' AND

year([BirthDate]) BETWEEN 1962 AND 1970 AND

year([HireDate]) > 2001

)

OR

(

[Gender] = 'F' AND

year([BirthDate]) BETWEEN 1972 AND 1975 AND

year([HireDate]) IN (2001, 2002)

)

Hi,
Hope this may help,

declare @employee table
(
[BusinessEntityID] int,
[BirthDate] int,
[Gender] char(1),
[HireDate] int
)
insert into @employee values(1,1961,'M',2000)
insert into @employee values(2,1963,'M',2002)
insert into @employee values(3,1962,'M',2002)
insert into @employee values(4,1972,'F',2001)
insert into @employee values(5,1973,'F',2002)
insert into @employee values(6,1974,'F',2000)

select [BusinessEntityID] , [BirthDate] , [Gender] , [HireDate] from @employee where [BirthDate] between '1962' and '1970' and Gender = 'M' and [HireDate] > 2001
union
select [BusinessEntityID] , [BirthDate] , [Gender] , [HireDate] from @employee where [BirthDate] between '1972' and '1975' and Gender = 'F' and [HireDate] in (2001,2002)

1 Like

The query WHERE conditions look right to me as you have them. There must not be any female employees that match those conditions.

1 Like

Thank you so much for your help. Much appreciated!