Need Help with a Query

Hi,

[Quoted]   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.

[Quoted]  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 [Quoted] the month(this one split as Temp(T) and Perm(P)) and if possible also the % [Quoted] of people termed for the month. For e.g. I need the query on the table given to return

[Quoted] 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 [Quoted] he was Temp(T). So the 1 under the T column in the answer for proj 103. Any [Quoted] 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 CEST