SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Help. Automated purge of tables by date/name		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

smithy
Starting Member

Australia
2 Posts
Posted - 07/09/2012 :  21:28:16  Show Profile  Reply with Quote
Im trying to figure out a way to purge data from tables in an automated way instead of having to run the stored proc manually. Basically anything not including the current month and the 2 months prior.
..
declare curs cursor for (
select name from sysobjects
where name like '%ACC_DT1_DBF%' and (name not like'%FEB2012%' and name not like'%JAN2012%' and name not like '%DEC2011%'))
..

Table Created Date
ACCOUNT#DEC2011#ACC_DT1_DBF 01/12/2011 5:51:47.566 AM
ACCOUNT#JAN2012#ACC_DT1_DBF 31/12/2011 5:57:21.260 AM
ACCOUNT#FEB2012#ACC_DT1_DBF 01/02/2012 6:01:41.253 AM
ACCOUNT#MAR2012#ACC_DT1_DBF 01/03/2012 5:58:18.046 AM
ACCOUNT#APR2012#ACC_DT1_DBF 31/03/2012 4:51:59.193 AM
ACCOUNT#MAY2012#ACC_DT1_DBF 01/05/2012 4:22:08.370 AM
ACCOUNT#JUN2012#ACC_DT1_DBF 01/06/2012 4:10:04.246 AM
ACCOUNT#JUL2012#ACC_DT1_DBF 30/06/2012 4:51:16.870 AM

For example I ran the above in February and excluded Feb(current) Jan and Dec (2months)

I tried using current date vs current month and subtracting but run into the issue of if its month 1 then it will look for 0 and -1. Any ideas??

visakh16
Very Important crosS Applying yaK Herder

India
47157 Posts
Posted - 07/09/2012 :  21:31:13  Show Profile  Reply with Quote 

delete from table
where createddate < dateadd(mm,datediff(mm,0,getdate())-2,0)
and .... (other condition)


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
16746 Posts
Posted - 07/09/2012 :  21:32:33  Show Profile  Reply with Quote
quote:
I tried using current date vs current month and subtracting but run into the issue of if its month 1 then it will look for 0 and -1. Any ideas??


use DATEADD(month, -1, <date> )


KH
Time is always against us
Go to Top of Page

smithy
Starting Member

Australia
2 Posts
Posted - 07/09/2012 :  22:09:23  Show Profile  Reply with Quote
Thanks guys worked perfectly :)
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47157 Posts
Posted - 07/09/2012 :  23:12:44  Show Profile  Reply with Quote
wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000