Programatic Maintenance of Rolling History

[MCuthill]

Hi Everyone,

I have an SQL question regarding the maintenance of a "rolling" database. At any given time, I would like to thave 1 year of transaction history held in my database. Data is downloaded from the system at monthend and placed into SQL. The month end updates only have 2.5 months of transaction data in them at a time. What I want to be able to do at month end is use a DTS package to upload the month's transaction data and remove that same month from the previous year from the database. (I know, clear as mud)

Is it possible to generate an SQL statement where I will not have to change the date manually each month? There is a table in the DB that holds the date of month end, if I can drive it off of that.

For example:

DELETE * from TranData WHERE TranDate <= (table.CurrentMonthEnd-367)

This is in simple form, I would imagine there will need to be a JOIN statement, and possibly some other code to acheive the desired result.

Any guidance you can provide is greatly appreciated.

Regards,

Mike
______________________________________________________________
"It Seems All My Problems Exist Between Keyboard and Chair"

 

[SQLDenis]

look at this

select dateadd(yy,-1,getdate())

now your delete statement would be

DELETE  TranData WHERE TranDate <= dateadd(yy,-1,getdate())

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[MCuthill]

Thanks Denis,

I would assume that this same philosphy can be used on my insertion of the new month as well.

Something along the lines of:

INSERT TranData WHERE TranDate >= dateadd(mm,-1,getdate())

Thanks Again!


Mike
______________________________________________________________
"It Seems All My Problems Exist Between Keyboard and Chair"

 

[SQLDenis]

insert what? where is your data???


this should work, this will insert into the hist_TranData all rows from the TranData table that are older than 1 year

INSERT hist_TranData 
SELECT *
FROM TranData
WHERE TranDate >= dateadd(mm,-1,getdate())

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[MCuthill]

Ooops. Sorry Denis.

The other question I guess I have is that GetDate() will give me the system date.... can I reference a field value in place of this? I do not receive the month end data until the 5th or so of the month...

Example:

DateAdd(mm,-1,table.field)

Thanks Again for your assistance.


Mike
______________________________________________________________
"It Seems All My Problems Exist Between Keyboard and Chair"

 

[SQLDenis]

dump it into a variable

example

declare @v datetime
select @v = '20070505'

select @v =dateadd(yy,-1,@v)
select @v

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions