January 25, 2013 at 5:25 am
I have been asked to set up an archiving solution that automatically generates a series of SQL statements to run and Insert from source table(s) to destination table(s), then delete the inserted records from the source table().
The details of the tables to be archived are stored in a table, along with scheduling information in the form of Interval type ([DateTimeInterval]) as minute ("mi"), hour ("hh"), day ("dd"), week ("ww"), month ("mm") or year ("hh"), with a Interval value ([DateTimeValue]), so a type of "dd" and value of 5 will be executed every five days, while a type of "mi" and a value of 30 will be run every 30 minutes.
These SQL statments will then be executed, and the datetime of the execution will be stored as the LastRunDate. An agent job will run the SQL generation job every minute.
The SQL should only to be generated for a particular table if the date/time of the LastRunDate is earlier than Getdate() minus the type/unit combination.
My problem is that the DateAdd function (DATEADD (datepart , number , date )) allows variables for the number and date parameters, but not the datepart parameter.
I am trying to do the following:
SELECT
{generate SQL statment stuff}
FROM [dbo].[ArchiveDetail]
WHERE [LastRunDate] <= Dateadd([DateTimeInterval], -1 * [DateTimeValue], Getdate())
Unfortunately, Dateadd doesn't allow you to do this. I thought I might be able to create a function that takes the two values, creates and executes some inline sql and returns a date value, but you can't execute inline SQL in a function.
I am now stuck - any ideas?
January 25, 2013 at 5:47 am
January 25, 2013 at 5:54 am
Then I would have dynamic sql generating dynamic sql! I'm afraid it will disappear up it's own rear end! 😀
January 25, 2013 at 8:08 am
Then use CASE WHEN:
...
WHERE CASE WHEN DateTimeInterval = 'mm'
AND [LastRunDate] <= Dateadd(mm, -1 * [DateTimeValue], Getdate())
THEN 1
WHEN DateTimeInterval = 'dd'
AND [LastRunDate] <= Dateadd(dd, -1 * [DateTimeValue], Getdate())
THEN 1
WHEN DateTimeInterval = ...
ELSE 0
END = 1
January 25, 2013 at 8:11 am
Looks like a solution - I'll give it a try
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply