create a table with data capture for next 3 years
-
Hello, I would like to build a table capturing data starting from 1st day of current year till next 3 years. Once the table reaches 3 full years, first day of data should fall off as the next day data adds to the table after 3 years. Which means after completion of 3 years, any given day table should only hold exact previous 3 years of data. I need help coming up with this logic. Thank you in advance!
-
By fall off, do you mean get deleted?
Why not keep all the data and simply have a view which select the last three years?
Otherwise, create a daily SQL Agent job to perform the trimming.
-
Thank you Phil for responding on this.
Yes, delete the oldest rows. I am creating a stored procedure which creates this table and a job that runs everyday for daily inserts.
Don't want to pull everything into the table as the data will be in millions after 3 years.
-
You can add to your SP after records are created or execute another SP that deletes the records over 3 years old. You need to have a date for when the records are loaded, e.g. "LoadDate"
-- Delete records older than three years from today.
DELETE from YourTableName
WHERE LoadDate< DATEADD(YEAR, -3, GETDATE());
-
yes, this works. Thank you @tav29!
- Tav29 wrote:
You can add to your SP after records are created or execute another SP that deletes the records over 3 years old. You need to have a date for when the records are loaded, e.g. "LoadDate"
-- Delete records older than three years from today.
DELETE from YourTableName WHERE LoadDate< DATEADD(YEAR, -3, GETDATE());
If you decide to use this code, please make sure that you are happy with how it handles leap years.
DECLARE @Day1 DATE = '20240228'
,@Day2 DATE = '20240229';
SELECT D28Feb = @Day1
,D28FebLess3 = DATEADD (YEAR, -3, @Day1)
,Day29Feb = @Day2
,D29FebLess3 = DATEADD (YEAR, -3, @Day2); -
Good point Phil. I do believe DATEADD(year, -3, GETDATE()) Takes leap year into account, I'm too lazy to load test data. also I would not delete data from a table I would move the over 3 year data to an Archive Table just in case.
-
Also keep lock escalation in mind, so you don't lock the full table during these deletes ! (and e.g. prevent inserts being able to be processed )
a while loop handling "delete top(n) .." will help out if needed
I hope you have an index that helps with this process
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
- Tav29 wrote:
Good point Phil. I do believe DATEADD(year, -3, GETDATE()) Takes leap year into account, I'm too lazy to load test data. also I would not delete data from a table I would move the over 3 year data to an Archive Table just in case.
You don't need test data, just run the T-SQL example I provided and you will see what happens with leap years (nothing gets archived on 29 Feb, because data on 28 Feb three years before has already been removed).
And also, consider what happens three years after a leap year.
On 2027-02-28, data for 2024-02-28 gets archived.
On 2027-03-01, data for 2024-03-01 gets archived.
Data for 2024-02-29 is left in place until 2027-03-01.
-
Your are correct regarding "Data for 2024-02-29 is left in place until 2027-03-01."
-
This sounds like a good place for monthly partitions. The monthly switch out takes nearly zero time or resources and then the switched out part can just be dropped as a single unit.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Jeff Moden wrote:
This sounds like a good place for monthly partitions. The monthly switch out takes nearly zero time or resources and then the switched out part can just be dropped as a single unit.
And got so simple over the different sql server versions.
Nowadays it starts with truncate table at partition level.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply