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 another SQL query

Re: Need Help with another SQL query

From: Suresh Easwar <sje_at_sigma-inc.com>
Date: 1998/04/18
Message-ID: <3538CADE.DF6FBE0C@sigma-inc.com>#1/1

No need for subqueries or self joins. Here is a performance oriented solution:

select proj_id, sum(decode(sign(end_date - sysdate), 1, 1, 0) - decode(sign(end_date - (sysdate - 7)), 1, 1, 0)) from table_a
group by proj_id

You probably need trunc(sysdate) instead of sysdate to eliminate the time portion.

Suresh

dwarakv_at_hotmail.com wrote:

> 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 Sat Apr 18 1998 - 00:00:00 CDT

Original text of this message

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