Home » SQL & PL/SQL » SQL & PL/SQL » Query for group by period
Query for group by period [message #412478] Thu, 09 July 2009 11:59 Go to next message
Earthlink
Messages: 2
Registered: July 2009
Junior Member
Hello all,

I am trying to write a query that selects the records which are not within a 90-day period of each other begining with the first date. That is only those records are selected which are more than 90 day apart from each other. So for the following set

CREATE TABLE tbl ( 
  class      VARCHAR2(3200), 
  actiondate DATE); 

INSERT INTO tbl 
VALUES     ('C1', 
            '2/12/2007'); 

INSERT INTO tbl 
VALUES     ('C1', 
            '6/1/2007'); 

INSERT INTO tbl 
VALUES     ('C1', 
            '5/1/2009'); 

INSERT INTO tbl 
VALUES     ('C1', 
            '7/1/2009'); 

INSERT INTO tbl 
VALUES     ('C1', 
            '9/1/2009'); 

INSERT INTO tbl 
VALUES     ('C1', 
            '10/1/2009'); 

INSERT INTO tbl 
VALUES     ('C1', 
            '1/1/2010'); 

INSERT INTO tbl 
VALUES     ('C1', 
            '2/1/2010'); 

INSERT INTO tbl 
VALUES     ('C1', 
            '3/1/2010'); 

INSERT INTO tbl 
VALUES     ('C1', 
            '5/1/2010'); 

INSERT INTO tbl 
VALUES     ('C1', 
            '7/30/2009'); 

INSERT INTO tbl 
VALUES     ('C1', 
            '7/29/2009'); 

INSERT INTO tbl 
VALUES     ('C2', 
            '2/1/2008'); 

INSERT INTO tbl 
VALUES     ('C2', 
            '5/2/2008'); 

INSERT INTO tbl 
VALUES     ('C2', 
            '6/1/2008'); 

INSERT INTO tbl 
VALUES     ('C2', 
            '10/15/2008'); 

INSERT INTO tbl 
VALUES     ('C2', 
            '1/1/2009'); 

INSERT INTO tbl 
VALUES     ('C2', 
            '2/15/2009'); 

INSERT INTO tbl 
VALUES     ('C2', 
            '5/30/2009'); 

INSERT INTO tbl 
VALUES     ('C2', 
            '10/1/2009'); 


I need to return the following records

CLASS, ACTIONDATE
C1, 2/12/2007
C1, 6/1/2007
C1, 5/1/2009
C1, 9/1/2009
C1, 1/1/2010
C1, 5/1/2010
C2, 2/1/2008
C2, 5/2/2008
C2, 10/15/2008
C2, 2/15/2009
C2, 5/30/2009
C2, 10/1/2009


It tried the following query which does not work
SELECT actiondate, 
       Min(actiondate) 
         OVER(ORDER BY actiondate RANGE BETWEEN INTERVAL '90' DAY PRECEDING AND CURRENT ROW) dt 
FROM   tbl 


I also tried the following method but that also does not work.

SELECT   Ceil((actiondate - DATE '-4712-01-01') / 90) * 90 + DATE '-4712-01-01', 
         Min(actiondate) 
           KEEP ( DENSE_RANK LAST ORDER BY actiondate, rowid ) 
FROM     tbl 
GROUP BY Ceil((actiondate - DATE '-4712-01-01') / 90) * 90 + DATE '-4712-01-01' 



Is there a SQL to achieve this or is PL/SQL the only way to achieve this? Any pointers will be much appreciated.

Kindly consider that the table has more than 3 million rows.

Rgds
Thanks.

[Updated on: Thu, 09 July 2009 12:18]

Report message to a moderator

Re: Query for group by period [message #412483 is a reply to message #412478] Thu, 09 July 2009 12:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post a working Test case: create table and insert statements along with the result you want with these data.
Also always post your Oracle version (4 decimals).

Regards
Michel
Re: Query for group by period [message #412486 is a reply to message #412478] Thu, 09 July 2009 12:37 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
SQL> 
SQL> INSERT INTO tbl
  2  VALUES	('C2',
  3  		 '5/30/2009');
	    '5/30/2009')
            *
ERROR at line 3:
ORA-01843: not a valid month


SQL> 
SQL> INSERT INTO tbl
  2  VALUES	('C2',
  3  		 '10/1/2009');

1 row created.



in Oracle STRING datatype are delimited by single quote marks at each end.
'this is a string, 2009-07-09, not a DATE'

when a DATE data type is required, use TO_DATE() function
Re: Query for group by period [message #412487 is a reply to message #412478] Thu, 09 July 2009 12:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
the records which are not within a 90-day period of each other

In format DD/MM/YYYY, 01/01/2007, 01/03/2007, 01/05/2007 and 01/07/2007.
1 and 2 are within 90 days, so do not display.
1 and 3 are not, so display.
2 and 4 are not, so display,
3 and 4 are, so do not display
1 and 4 ara not, so display.

So display 1,2,3 and 4 and do not display 1,2,3 and 4.

Regards
Michel


Re: Query for group by period [message #412497 is a reply to message #412487] Thu, 09 July 2009 13:48 Go to previous messageGo to next message
Earthlink
Messages: 2
Registered: July 2009
Junior Member
Let me try to elaborate the algo for what I am trying to achieve.

1. For each class, select Date1= the date of the first record.
2. Go to next record
3. Check if the date (Date2) of the next record is greater than 90 days of the Date1.
4. If No move to next record.
5. If yes, display Date1 and set Date1 = Date2, move to next record
6. Repeat for all the records.

So with my values this is what will happen for class C1
Select 2/12/2007
Select 6/1/2007 as it is >2/12/2007+90
Select 5/1/2009 as it is >6/1/2007+90
Reject 7/1/2009 as it is <5/1/2009 +90
Select 9/1/2009 as it is >5/1/2009 +90
Reject 10/1/2009 as it is <9/1/2009 +90
Select 1/1/2010 as it is >9/1/2009 +90

and so on....


Do you think it is achievable without using a PL/SQL?
Re: Query for group by period [message #412676 is a reply to message #412497] Fri, 10 July 2009 09:50 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
By the looks of it, you need some sort of iterative process - if a date is rejected, then you keep comparing to the date before the last rejected date)

I think this will require a Model clause, so hopefully @Michel will oblige us.....
Previous Topic: Regarding Invalid object
Next Topic: Procedure
Goto Forum:
  


Current Time: Fri Dec 02 14:22:15 CST 2016

Total time taken to generate the page: 0.05357 seconds