Home » SQL & PL/SQL » SQL & PL/SQL » Get Max Value of Date Column With Condition (11g)
Get Max Value of Date Column With Condition [message #642865] Tue, 22 September 2015 03:30 Go to next message
elmousa68
Messages: 20
Registered: September 2012
Junior Member
I have a table (socmanbas) with 3 fields (socregno number(5),socregyear varchar2(4), admcomto date)

The data in the table is structured so that there differing values for admcomto for the same socregno and socregyear. Like so:

soregno socregyear admcomto
-------- ------------- ---------
905 '1990' 11-21-2006
905 '1990' 22-10-2015
905 '1990' 24-10-2015
120 '1996' 1-1-2014
120 '1996' 10-9-2015
. . .
. . .
. . .
. . .


What I want is a query that will return socregno and socregyear for the value of admcomto that is
1. is the maximum date value for that socregno and socregyear
2. and, is equal to the system date + 30 days

I am trying the following query

select socregno,socregyear,admcomto from socmanbas where
to_char(admcomto,'yyyy-dd-mm')=to_char(sysdate+30,'yyyy-dd-mm')
group by socregno,socregyear having admcomto=max(admcomto)

but this query is not working correctly.

Any help appreciated.

Report message to a moderator

Re: Get Max Value of Date Column With Condition [message #642866 is a reply to message #642865] Tue, 22 September 2015 03:38 Go to previous messageGo to next message
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
Senior Member
You need to provide the CREATE TABLE and the 5 INSERT statements needed to set up the problem. You know that from your previous posts. Without them, I can't test your query.

Report message to a moderator

Re: Get Max Value of Date Column With Condition [message #642867 is a reply to message #642866] Tue, 22 September 2015 03:46 Go to previous messageGo to next message
elmousa68
Messages: 20
Registered: September 2012
Junior Member
create table socmanbas (socregno number(5), socregyear varchar2(4), admcomto date);

insert into socmanbas (socregno,socregyear,admcomto) values (905,'1990','11-21-2006');
insert into socmanbas (socregno,socregyear,admcomto) values (905,'1990','22-10-2015');
insert into socmanbas (socregno,socregyear,admcomto) values (905,'1990','24-10-2015');
insert into socmanbas (socregno,socregyear,admcomto) values (120,'1996','1-1-2014');
insert into socmanbas (socregno,socregyear,admcomto) values (120,'1996','10-9-2015');

commit;

Report message to a moderator

Re: Get Max Value of Date Column With Condition [message #642869 is a reply to message #642867] Tue, 22 September 2015 03:52 Go to previous messageGo to next message
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
Senior Member
dates in insert scripts need to wrapped in a to_Date call with the correct format mask, not every has the same date format as you

Report message to a moderator

Re: Get Max Value of Date Column With Condition [message #642870 is a reply to message #642869] Tue, 22 September 2015 03:54 Go to previous messageGo to next message
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
Senior Member
And the 1st and 2nd are in different formats, not a good idea.

Report message to a moderator

Re: Get Max Value of Date Column With Condition [message #642871 is a reply to message #642869] Tue, 22 September 2015 03:59 Go to previous messageGo to next message
elmousa68
Messages: 20
Registered: September 2012
Junior Member


insert into socmanbas (socregno,socregyear,admcomto) values (905,'1990',to_date('11-21-2006','mm-dd-yyyy'));
insert into socmanbas (socregno,socregyear,admcomto) values (905,'1990',to_date('22-10-2015','mm-dd-yyyy'));
insert into socmanbas (socregno,socregyear,admcomto) values (905,'1990',to_date('24-10-2015','mm-dd-yyyy'));
insert into socmanbas (socregno,socregyear,admcomto) values (120,'1996',to_date('1-1-2014','mm-dd-yyyy'));
insert into socmanbas (socregno,socregyear,admcomto) values (120,'1996',to_date('10-9-2015','mm-dd-yyyy'));

like so?

Report message to a moderator

Re: Get Max Value of Date Column With Condition [message #642872 is a reply to message #642871] Tue, 22 September 2015 04:01 Go to previous messageGo to next message
elmousa68
Messages: 20
Registered: September 2012
Junior Member
I'm not sure if it matters how a date is INSERTED, the important thing is how you deal with it later...? or am I mistaken?

Report message to a moderator

Re: Get Max Value of Date Column With Condition [message #642873 is a reply to message #642872] Tue, 22 September 2015 04:08 Go to previous messageGo to next message
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
Senior Member
come on, man, test your code. It doesn't work:
orclz>
orclz> insert into socmanbas (socregno,socregyear,admcomto) values (905,'1990',to_date('11-21-2006','mm-dd-yyyy'));

1 row created.

orclz> insert into socmanbas (socregno,socregyear,admcomto) values (905,'1990',to_date('22-10-2015','mm-dd-yyyy'));
insert into socmanbas (socregno,socregyear,admcomto) values (905,'1990',to_date('22-10-2015','mm-dd-yyyy'))
                                                                                *
ERROR at line 1:
ORA-01843: not a valid month


orclz> insert into socmanbas (socregno,socregyear,admcomto) values (905,'1990',to_date('24-10-2015','mm-dd-yyyy'));
insert into socmanbas (socregno,socregyear,admcomto) values (905,'1990',to_date('24-10-2015','mm-dd-yyyy'))
                                                                                *
ERROR at line 1:
ORA-01843: not a valid month


orclz> insert into socmanbas (socregno,socregyear,admcomto) values (120,'1996',to_date('1-1-2014','mm-dd-yyyy'));

1 row created.

orclz> insert into socmanbas (socregno,socregyear,admcomto) values (120,'1996',to_date('10-9-2015','mm-dd-yyyy'));

1 row created.

orclz>
you will never get antwhere if you ca'nt write a simple INSERT that runs.

Report message to a moderator

Re: Get Max Value of Date Column With Condition [message #642874 is a reply to message #642873] Tue, 22 September 2015 04:13 Go to previous messageGo to next message
elmousa68
Messages: 20
Registered: September 2012
Junior Member
Please excuse the oversight....

insert into socmanbas (socregno,socregyear,admcomto) values (905,'1990',to_date('10-22-2015','mm-dd-yyyy'));
insert into socmanbas (socregno,socregyear,admcomto) values (905,'1990',to_date('10-24-2015','mm-dd-yyyy'));

I am not sure I will get somewhere even if am able to write inserts correctly. Smile

Report message to a moderator

Re: Get Max Value of Date Column With Condition [message #642875 is a reply to message #642874] Tue, 22 September 2015 04:15 Go to previous messageGo to next message
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
Senior Member
More than 2 rows would be a good idea

Report message to a moderator

Re: Get Max Value of Date Column With Condition [message #642876 is a reply to message #642875] Tue, 22 September 2015 04:17 Go to previous messageGo to next message
elmousa68
Messages: 20
Registered: September 2012
Junior Member
insert into socmanbas (socregno,socregyear,admcomto) values (905,'1990',to_date('11-21-2006','mm-dd-yyyy'));
insert into socmanbas (socregno,socregyear,admcomto) values (120,'1996',to_date('1-1-2014','mm-dd-yyyy'));
insert into socmanbas (socregno,socregyear,admcomto) values (120,'1996',to_date('10-9-2015','mm-dd-yyyy'));

Report message to a moderator

Re: Get Max Value of Date Column With Condition [message #642878 is a reply to message #642874] Tue, 22 September 2015 04:20 Go to previous messageGo to next message
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
Senior Member
build up the query slowly. To start with, do the aggregation. Like this:
 
  1  select socregno,socregyear,max(admcomto) from socmanbas
  2* group by socregno,socregyear
orclz> /

  SOCREGNO SOCR MAX(ADMCO
---------- ---- ---------
       905 1990 24-OCT-15
       120 1996 09-OCT-15

orclz>
now try to include the predicate, perhaps by wrapping the above query in another query.

Report message to a moderator

Re: Get Max Value of Date Column With Condition [message #642879 is a reply to message #642878] Tue, 22 September 2015 04:35 Go to previous messageGo to next message
elmousa68
Messages: 20
Registered: September 2012
Junior Member
select socregno,socregyear from socmanbas where
to_char(admcomto,'yyyy-dd-mm')=to_char(sysdate+30,'yyyy-dd-mm')
and (socregno,socregyear,admcomto) in (select socregno,socregyear,max(admcomto) from
socmanbas group by socregno,socregyear)


I tried the above query, with the correct results....?

but I had already tried a similar query like so:

select socregno,socregyear from socmanbas where
to_char(admcomto,'yyyy-dd-mm')=to_char(sysdate+30,'yyyy-dd-mm')
and admcomto in (select max(admcomto) from
socmanbas group by socregno,socregyear)

but this did not work?

Report message to a moderator

Re: Get Max Value of Date Column With Condition [message #642881 is a reply to message #642879] Tue, 22 September 2015 04:42 Go to previous message
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
Senior Member
Well with your sample data both give the correct results of no rows. Which says a lot about your sample data.

The first query returns rows where admcomto is the max for that combination of socregno,socregyear.
The first query returns rows where admcomto is the max for some combination of socregno,socregyear.

And always use code tags for code.

Report message to a moderator

Previous Topic: How to create named DEFAULT constraint
Next Topic: PLSQL_OPTIMIZE_LEVEL
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Apr 25 17:40:53 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.