Query getting the users who hasn't used more then 3 months 1

[Zargo]

Hi all,

I have a table with the following info:

TableA
log_index | description | User_name | Log_date
1000 file created UserX 01-11-2005 00:00:00
4564 deleted UserB 06-05-2005 12:30:10
5489 accepted UserA 08-06-2005 12:23:00
5332 rejected UserA 08-06-2005 12:23:00
5466 returned UserC 07-11-2005 12:10:00
5001 received UserD 01-04-2005 11:00:00
5001 saved UserX 01-01-2004 12:00:00

Who can i get the records of users who hasn't used the application (log_index beginning with 5) more then 3 months?

So i only want the users and the last date he/she has used the application(all log_indexes beginning with a 5)
The result must be:
User_name | Last_date
UserA 08-06-2005 12:23:00
UserD 01-04-2005 11:00:00

I have tried

select user_name, log_date as Last_date
from TableA where log_index
like '5%' and extract(month from log_date) >= 3

But i don't get the right records returned. I have also tried with the GROUP BY clause and ROWNUM = 1 but without success.

Does anybody know how to write the Query?

A star for the golden answer.

Thanks a lot...

Zargo

 

[Zargo]

also tried something with SYSDATE - 3 months but dont know how to do this...

 

[Zargo]

I think i have found the right query. A star for my self

Select user_name, max(log_date) as Last_date
from TableA where log_index like '5%'
and log_date <= add_months(sysdate, -3)
group by user_name
order by Last_date

 

[SantaMufasa]

Cool, Zargo. Since you can't give yourself a star, I'll take care of business for you. Self appreciation is such a rare quality nowdays.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[Zargo]

Thanks SantaMufasa!!