Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Need Help with a Query

Re: Need Help with a Query

From: rok <rok_at_MCI2000.com>
Date: 1998/05/26
Message-ID: <01bd884d$738b24c0$a70837a6@raghus-computer>#1/1

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US