Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
delete records from table if record is > (1year + 1month)

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
alluri12

New User


Joined: 15 Aug 2006
Posts: 44
Location: Virginia, USA

PostPosted: Wed Dec 02, 2009 7:54 am    Post subject: delete records from table if record is > (1year + 1month)
Reply with quote

Hi,
I have a table

Code:
Table1
 col1         char(2)
 col2         decima(2)
 col3         char(2)
 Active_dt    date
 col5         char(2)
 col6         char(2)


The active_dt is the date when we insert the record.
I want to delete the records from this table if the Active_dt older than (1year + 1month).

I request if somebody can help me in writing the Sql for this.
Back to top
View user's profile Send private message

Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Wed Dec 02, 2009 8:06 am    Post subject:
Reply with quote

What have you tried so far and what results have you gotten
Back to top
View user's profile Send private message
alluri12

New User


Joined: 15 Aug 2006
Posts: 44
Location: Virginia, USA

PostPosted: Wed Dec 02, 2009 8:15 am    Post subject:
Reply with quote

I tried
delete from table1
where days(current date) - days(active_dt) > 395

I thought 1 year = 365 days
1 month = 30 days
I know this is not correct b.c days for the month changes to 30 or 31 and for the year it will 365 or 366
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6970
Location: porcelain throne

PostPosted: Wed Dec 02, 2009 8:46 am    Post subject:
Reply with quote

why don't you try something along the line of current-date - 13 months > active_dt
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Wed Dec 02, 2009 8:47 am    Post subject:
Reply with quote

Hello,

You need to define exactly what 1 year is to your organization. Same with 1 month. Diferent organizations have different date rules.
Back to top
View user's profile Send private message
alluri12

New User


Joined: 15 Aug 2006
Posts: 44
Location: Virginia, USA

PostPosted: Wed Dec 02, 2009 9:00 am    Post subject:
Reply with quote

Thank you Dick Brenholtz for a fast respnose. It works....
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6970
Location: porcelain throne

PostPosted: Wed Dec 02, 2009 9:02 am    Post subject:
Reply with quote

Quote:
You need to define exactly what 1 year is to your organization. Same with 1 month. Diferent organizations have different date rules.


when we are talking about financial calculations, I agree.

but this is an archival/history thing and 13 months from the current date, is always going to be 13 months from the current date. and because it is the current date, db2 will always calculate the 'correct date' based on today's date.

but, I agree, also, that the TS should be aware of the organization's date definitions.

and I wanted to be the last poster of this thread
Back to top
View user's profile Send private message
alluri12

New User


Joined: 15 Aug 2006
Posts: 44
Location: Virginia, USA

PostPosted: Wed Dec 02, 2009 10:07 am    Post subject:
Reply with quote

The requirements that I got for this code is from the current date. So this code works.... I too agree differnt org has different date rules
Back to top
View user's profile Send private message
alluri12

New User


Joined: 15 Aug 2006
Posts: 44
Location: Virginia, USA

PostPosted: Wed Dec 02, 2009 7:45 pm    Post subject:
Reply with quote

I found another solution also for this requriement. It is almost same as what dick gave... but just want to share...

Code:
Delete  From table1
WHERE DAYS(Active_dt) < DAYS((CURRENT DATE - 1 YEAR) - 1 MONTH)
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Wed Dec 02, 2009 8:03 pm    Post subject:
Reply with quote

alluri12 wrote:
I found another solution also for this requriement. It is almost same as what dick gave... but just want to share...

Code:
Delete  From table1
WHERE DAYS(Active_dt) < DAYS((CURRENT DATE - 1 YEAR) - 1 MONTH)

days(date1) < days(date2)
is like
Col1 + 1000 < Col2 +1000

it is correct but more sense and - far more important - much more performant is

date1 < date2
col1 < col2
Back to top
View user's profile Send private message
Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Wed Dec 02, 2009 9:09 pm    Post subject:
Reply with quote

Instead of
Code:
WHERE DAYS(Active_dt) < DAYS((CURRENT DATE - 1 YEAR) - 1 MONTH)


I think
Code:
WHERE Active_dt < (CURRENT DATE - 1 YEAR) - 1 MONTH


would be more efficient, evaluating (current date - 1 year) - 1 month would only have to be done once, but evaluating date(active_dt) would have to be done for every row before it could be tested.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Inefficient BUILD - VB records paddin... Daniel Prosser SYNCSORT 6 Thu Dec 14, 2017 3:52 pm
No new posts ISSUE IN copying Sequential file reco... thesumitk JCL & VSAM 2 Wed Dec 13, 2017 3:07 pm
No new posts query to fetch record which has only ... maxsubrat DB2 12 Mon Dec 11, 2017 5:03 pm
No new posts Merging 2 records at multiple rows wi... Bijesh DFSORT/ICETOOL 2 Wed Dec 06, 2017 1:50 am
No new posts Extract record for change in combinat... Trinadh DFSORT/ICETOOL 6 Thu Nov 23, 2017 3:32 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us