Home » SQL & PL/SQL » SQL & PL/SQL » Get records with positive values within 10y
Get records with positive values within 10y [message #237674] Tue, 15 May 2007 07:51 Go to next message
steffeli
Messages: 112
Registered: July 2006
Senior Member
Hello,

I have a table DATA (monthly date) with the columns date (xdnew), CompanyName and the parameter P1. How can I get the Companies (for each month), which have only positive values P1 within the last 10 years?

I tried the code, but I don't know how I can filter only values with positive P1 in the last 10 years.


create table test
select a.*,
       count(a.*)  over (partition by a.CompanyName
                   order by a.xdnew
		   range between numtoyminterval(119,'month') preceding and current row)
	        as x_test	 
from ( select b.*,
              to_date(DNEW, 'yyyymm') as xdnew
       from DATA b ) a

[Updated on: Tue, 15 May 2007 07:52]

Report message to a moderator

Re: Get records with positive values within 10y [message #237677 is a reply to message #237674] Tue, 15 May 2007 07:58 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Only positive values means there exist no negative (or zero??) values.
So, a where not exists might do the trick!
Re: Get records with positive values within 10y [message #237681 is a reply to message #237674] Tue, 15 May 2007 08:09 Go to previous messageGo to next message
steffeli
Messages: 112
Registered: July 2006
Senior Member
There ARE negative values! A simple where clause is not what I need, because I only want those companies (each month), which had no negative P1 within the last 10 years. So, the where clause should be in the partition clause, but how can I do that?
Re: Get records with positive values within 10y [message #237691 is a reply to message #237681] Tue, 15 May 2007 08:32 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
And how many companies with negative values are selected by this?
select company
from   your_table t1
where  not exists (select 1
                   from   your_table t2
                   where  t2.company = t1.company
                   and    t2.value < 0
                   and    t2.date > add_months(sysdate, -120)
                  )
Re: Get records with positive values within 10y [message #237700 is a reply to message #237674] Tue, 15 May 2007 08:47 Go to previous messageGo to next message
steffeli
Messages: 112
Registered: July 2006
Senior Member
Yes, ok that works, but I think it's very slow because of t2.company = t1.company. Is there no where clause possibility in the partition by clause?
Re: Get records with positive values within 10y [message #237941 is a reply to message #237700] Wed, 16 May 2007 03:10 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
There is no way of including a WHERE in the partition by, as the Docs clearly show.

You could do something like
select distinct company_name
from  (select company_name
             ,min(p1) over (partition by company_name order by p1) min_p1
       from   company_data
       where  xdnew > add_months(sysdate,-120))
where min_p1 >=0;
or you could try a setwise operation
select company_name 
from   company_data
minus
select company_name
from   company_data
where  p1 < 0
and    xdnew > add_months(sysdate,-120);
Re: Get records with positive values within 10y [message #238101 is a reply to message #237941] Wed, 16 May 2007 13:17 Go to previous messageGo to next message
tarmenel
Messages: 63
Registered: February 2007
Location: Israel
Member

Could you please post the link to the place where this is documented. I've been trying to understand the partition by for quite some time without success and the searches I come up with in Google are rather vague. Any other articles on the partition by will be appreciated.
Re: Get records with positive values within 10y [message #238110 is a reply to message #238101] Wed, 16 May 2007 13:54 Go to previous message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Interesting that Google didn't find the Oracle SQL reference... But here it is:
"Analytic functions are the last set of operations performed in a query except for the final ORDER BY clause. All joins and all WHERE, GROUP BY, and HAVING clauses are completed before the analytic functions are processed. Therefore, analytic functions can appear only in the select list or ORDER BY clause."

Source: http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14200/functions001.htm#sthref968
Previous Topic: setting session timezone in 9i/10g
Next Topic: could u plz clear my doubts???
Goto Forum:
  


Current Time: Thu Dec 08 14:07:46 CST 2016

Total time taken to generate the page: 0.07403 seconds