Selecting min date in a group

Hello
I want to be able to group data but having the ability to be able to return data subject to: Col B > the min date + 1 month in each grouping.
Example data set.....
ColA         ColB
A              01/01/2001
A              01/02/2001
A              01/03/2001
A              01/04/2001
A              01/05/2001
B              31/05/2006
B              30/06/2006
B              31/07/2006

so, if I group on column A and the above logic applied where in each group set the min date in each group + 1 month would now return the following...

A              01/02/2001
A              01/03/2001
A              01/04/2001
A              01/05/2001
B              30/06/2006
B              31/07/2006

Regards
philsivyerAsked:
Who is Participating?
 
magadesign_sviluppoConnect With a Mentor Commented:
try:
select tbl.* FROM Table_1 tbl
INNER JOIN (SELECT MIN(ColB) md, ColA gr FROM Table_1  group by ColA) temp on tbl.ColB>=DateAdd(Month, 1, temp.md)and tbl.ColA=temp.gr

Open in new window

\
this is sql server code, but think is the same in oracle
maybe will change something, but the concept is the same

hope it helps
0
 
magadesign_sviluppoCommented:
hi would you clarify:
the example table has: A              01/01/2001
but out put doesn't,
0
 
philsivyerAuthor Commented:
The min date for each group is
A 01/01/2001
B 31/05/2006

The data returned for each grouping should be anything greater of the min date by one month for each group
0
 
philsivyerAuthor Commented:
Thanks
0
 
flow01Commented:
select table11.col1, table11.col2
from table11,
(select  col1 , add_months(min(col2),1) col2
 from table11
 group by col1 ) min
where  table11.col1 = min.col1
and table11.col2 >= min.col2
order by table11.col1, table11.col2
/

check if the add_months does what you want
0
All Courses

From novice to tech pro — start learning today.