Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups 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: 6966
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: 6966
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: 1280
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 Record Not found in AlX but record re... mukun264 COBOL Programming 5 Fri Mar 24, 2017 9:28 am
No new posts Count the length of the record & ... sreekusr DFSORT/ICETOOL 4 Thu Mar 23, 2017 7:52 pm
No new posts IMS DB-How to update a record (a sing... Nic Clouston IMS DB/DC 9 Thu Mar 09, 2017 4:38 pm
No new posts Join giving more records than expected Danielle.Filteau SYNCSORT 2 Thu Mar 09, 2017 3:28 am
No new posts TSO or command line utility to genera... kishpra JCL & VSAM 3 Thu Mar 09, 2017 1:11 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us