Need Help with a Query

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