Need Help with another SQL query

From: <dwarakv_at_hotmail.com>
Date: 1998/04/17
Message-ID: <6h86ru$jgm$1@nnrp1.dejanews.com>#1/1

Hi,

   Let me first thank all the people who responded to my earlier query. I have another problem. I have a table with the following columns

                proj_id  end_date status

  I need to write a query which lists all the proj_id, and the difference in number of records with end_date > sysdate and end_date> (sysdate-7). I do have other fields like agent_ssn, etc which are not relevant to this query. For e.g if the values in the table were

proj_id   end_date       status
 P101     31-dec-2099    F/T/P
 P101     31-dec-2099    F/T/P
 P101     31-dec-2099    F/T/T
 P101     15-apr-1998    Terminated
 P101     01-mar-1998    Terminated
 P102     31-dec-2099    F/T/P
 P102     31-dec-2099    P/T/P
 P102     31-dec-2099    F/T/T
 P102     16-apr-1998    Terminated
 P103     31-dec-2099    P/T/P
 P103     31-dec-2099    F/T/T

sysdate = 17-apr-1998

I need the query to return
proj_id count(status where end_date>sysdate)-count(status where

                                                   end_date > sysdate-7)
P101       -1
P102       -1
P103        0

  The query that I had written was something like

select a.proj_id, count(a.status)- count(b.status)  from Table_A a,

      Table_A b
 where

       b.end_date > sysdate-7 and
       a.end_date > sysdate
group by
       a.proj_id

I get the difference to be zeros. Can someone please help me with the query?

Thanks
Dwarak

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/ Now offering spam-free web-based newsreading Received on Fri Apr 17 1998 - 00:00:00 CDT