Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Need Help with a Query
Dwarak,
I was able to come up with a query very close to you want. But I could not split the termed between P and T. Anyway it may give you an idea.
select t1.proj, nvl(t2.init,0) init, nvl(t3.termed,0) termed,
nvl(t3.termed,0) * 100 / nvl(t2.init,1) "%"
from
(select distinct proj from proj_assgn) t1,
(select proj, count(ssn) init from proj_assgn
where end_date >= trunc(v_req_date, 'MONTH') and
status <> 'Termed'
group by proj
) t2,
(select proj, count(ssn) termed from proj_assgn
where end_date >= trunc(v_req_date, 'MONTH') and
end_date <= last_day(v_req_date) and
status = 'Termed'
group by proj) t3
where t1.proj = t2.proj(+) and
t1.proj = t3.proj(+)
/
raghuvir
dwarakv_at_hotmail.com wrote in article <6kd3mg$gvu$1_at_nnrp1.dejanews.com>...
> Hi,
>
> I have a table with columns and values as follows
>
> ssn proj start end status
> 12345 101 01-OCT-97 22-FEB-98 P
> 12345 102 23-FEB-98 01-MAY-98 P
> 12345 102 02-MAY-98 02-MAY-98 Termed
> 23456 103 01-APR-98 10-APR-98 P
> 23456 103 11-APR-98 10-MAY-98 T
> 23456 103 11-MAY-98 11-MAY-98 Termed
> 34567 104 01-FEB-98 01-FEB-98 Termed
> 45678 101 01-JAN-98 31-DEC-99 T
> 56789 102 01-JAN-98 31-DEC-99 P
>
> ssn, proj and start are the primary keys. I am developing a report using
> reports 2.5 which needs to look like the one below using the data given
above.
>
> I need to write a query which takes in a date(e.g., 15-MAY-98) and
> calculates the number of people working on each project on the 1st of the
> month(MAY in this case) and the number of people termed by the last day
of
> the month(this one split as Temp(T) and Perm(P)) and if possible also the
%
> of people termed for the month. For e.g. I need the query on the table
given
> to return
>
>
>
> proj Init P T %
> 101 1 0 0 0
> 102 2 1 0 50
> 103 1 0 1 100
> 104 0 0 0 0
>
> The P or T in the answer denotes whether they were temp or Perm the day
> before being termed. For e.g 23456 was termed on 11 may. On the 10th of
may
> he was Temp(T). So the 1 under the T column in the answer for proj 103.
Any
> help regarding writing of the query is greatly appreciated. Thanks
>
> Dwarak.
>
> -----== Posted via Deja News, The Leader in Internet Discussion ==-----
> http://www.dejanews.com/ Now offering spam-free web-based newsreading
>
Received on Tue May 26 1998 - 00:00:00 CDT