Help constructing SQL

From: Greg Akins <gakinsNOgaSPAM_at_gatewayhealthplan.com.invalid>
Date: Wed, 19 Jan 2000 06:40:44 -0800
Message-ID: <04e9bbf8.d99d3221@usw-ex0102-013.remarq.com>

Hi,

I have a table:

Emp

EmpNo
DeptNo
StartDate
TermDate
PositiveReview

I need a report which looks like

DeptNo Crnt_Employed Terminated(y/n) Had_Pos_Review

1        3                     0               1
2        4                     1               0
3        0                     2               0

So I tried:

select B.DeptNo, b.Employed, a.Terminated from (select DeptNo, count(*) as Terminated from emp
where DeptNo is not null
and TermDate is not null
group by DeptNo) a,
(select DeptNo, count(*) as Employed
from emp
where DeptNo is not null
and TermDate is null
group by DeptNo) b
where a.DeptNo(+) = b.DeptNo
order by DeptNo

The problem is: Since null values can be returned by the subqueries, I can't rely on any specific outerjoin to return the correct results.

Can somebody give me suggestions for
constructing the query. If it's obvious, please excuse my ignorance.

-greg

Sent from RemarQ http://www.remarq.com The Internet's Discussion Network * The fastest and easiest way to search and participate in Usenet - Free!

Received on Wed Jan 19 2000 - 08:40:44 CST