Need Help with another SQL query
From: <dwarakv_at_hotmail.com>
Date: 1998/04/17
Message-ID: <6h86ru$jgm$1_at_nnrp1.dejanews.com>#1/1
Date: 1998/04/17
Message-ID: <6h86ru$jgm$1_at_nnrp1.dejanews.com>#1/1
Hi,
[Quoted] 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 statusI 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
[Quoted] -----== 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 CEST