Re: Help constructing SQL

From: Emmanuel <zouzou_at_yahoo.com>
Date: 19 Jan 2000 15:29:22 GMT
Message-ID: <01bf629a$c532f540$3601017e@EHM.cirra.fr>

Try this (1)

select

	DeptNo, 
	sum(	decode(
			nvl(TermDate,to_date('19000101','YYYYMMDD')),
			to_date('19000101','YYYYMMDD'),
			1,
			0)
	    ) Employed,
	sum(	decode(
			nvl(TermDate,to_date('19000101','YYYYMMDD')),
			to_date('19000101','YYYYMMDD'),
			0,
			1)
	    ) Terminated
from
	emp
where
	DeptNo is not null
group by
	DeptNo
order by
	DeptNo

;         

or this (2)

select c.DeptNo, nvl(b.Employed,0), nvl(a.Terminated,0) 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,
(select distinct DeptNo from emp where DeptNo is not null) c where a.DeptNo(+) = c.DeptNo
and b.DeptNo(+) = c.DeptNo
order by c.DeptNo;

or this (3)

select c.DeptNo, nvl(b.Employed,0), nvl(a.Terminated,0) 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,
Dept c
where a.DeptNo(+) = c.DeptNo
and b.DeptNo(+) = c.DeptNo
order by c.DeptNo;

(1) is probably the best in terms of performance (3) is better then (2) if you have a Dept table

Hope this will help

Regards

Greg Akins <gakinsNOgaSPAM_at_gatewayhealthplan.com.invalid> a écrit dans l'article <04e9bbf8.d99d3221_at_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 - 09:29:22 CST