|
View previous topic :: View next topic
|
| Author |
Message |
guptae
Moderator
Joined: 14 Oct 2005
Posts: 1208
Location: Bangalore,India
|
|
|
|
Hi All,
I have a table ACC1 which contain primary key as ACC_number & UPDT_DT_TM.
Now I want to fetch all the latest records for Account_number where latest record timestamp is older than 60 days & they are ina ctive status
| Code: |
SELECT ACC_number, MAX(UPDT_DT_TM),status, colb,colC
FROM TB
GROUP BY ACC_number
having Status ='approved'
and Days(UPDT_DT_TM)-Days(current_timestamp)>60
WITH UR |
I dont have access to db2 currently. Would you please provide optimized query for above criteria |
|
| Back to top |
|
 |
Srihari Gonugunta
Active User
Joined: 14 Sep 2007
Posts: 295
Location: Singapore
|
|
|
|
Ekta,
You can try this.
| Code: |
SELECT ACC_number, MAX(UPDT_DT_TM)
FROM TB
WHERE Status ='approved'
and DAYS(CURRENT TIMESTAMP) - Days(UPDT_DT_TM) > 60
GROUP BY ACC_number; |
|
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
I'm not sure of the requirement, but if I understand it correctly
then both solutions are wrong.
What the previous sqls return : you're searching for statusses that are 60 days old and of those you want the most recent one.
How I understand it :
You're only interested in the last status of account_number and that one has to be 60 days old and .
some guidelines for performance :
- avoid "having"
- avoid where-clause on function(column) or on calculations involving columns.
| Code: |
Select * from TB A
where a.updt_dt_tm = (select max(b.updt_dt_tm) from TB b
where a.ACC_number = b.ACC_number)
and a.updt_dt_tm < timestamp(current_date - 60 days,'00.00.00')
and A.status = 'approved' |
|
|
| Back to top |
|
|
guptae
Moderator
Joined: 14 Oct 2005
Posts: 1208
Location: Bangalore,India
|
|
|
|
Hello Guyc,
Thanks Alot.I Have not checked the solution yet but it seems to be as per my requirement. |
|
| Back to top |
|
|
|
|
