Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

How to make sql script for MSSQL which will run as job every day and delete obly records that are more than 90 working days old from day it run.

Posted on 2007-11-21
10
Medium Priority
?
1,607 Views
Last Modified: 2012-05-05
How to make sql script for MSSQL which will run as job every day and delete obly records that are more than 90 working days old from day it run.

Explanation. I have a table in my MSSQL which generates Salesorders and each order has a field "creationTimeStamp" I want to have a scheduled job in MSSQL to run every day but it should only select those records where CreationTimeStamp is older more then 90 working days (monday-freday) and it should run every day.  Please help me
0
Comment
Question by:dm98101
  • 5
  • 2
  • 2
  • +1
10 Comments
 
LVL 10

Expert Comment

by:AustinSeven
ID: 20326567
I assume you know how to create a job and schedule it to run either every day or Monday to Friday?  If not, let me know and I will assist.   The real issue is calculating elapsed working days.   Here is a link that will have all the info you need to solve that problem.    

http://www.sqlmag.com/Article/ArticleID/95675/sql_server_95675.html

Regards,
AustinSeven
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20326686
i don't think it's really friendly to post a link that leads to

very short non-stuff... etc...
[
Want to Read More of This Article?
Get Full Online Access to SQL Server Magazine Now!
Subscribe Here to save 30% & Receive a FREE GIFT!
]
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20326696
that sounds like an archiving function, so fuzzy logic should be ok?

delete  Salesorders
where creationTimeStamp < select dateadd(wk, 90/5, convert(varchar,getdate(),102))

90/5 assumes 5 normal working days a week, ignoring holidays
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 25

Expert Comment

by:imitchie
ID: 20326697
sorry you need brackets around subqueries
delete  Salesorders
where creationTimeStamp <
(select dateadd(wk, 90/5, convert(varchar,getdate(),102)))

Open in new window

0
 
LVL 25

Expert Comment

by:imitchie
ID: 20326699
.. and it's minus
delete  Salesorders
where creationTimeStamp <
(select dateadd(wk, -90/5, convert(varchar,getdate(),102)))

Open in new window

0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 20326778
create a script like this ...
and place it in a step of a job that you schedule to run every day at say 1am...

the main problem is determining what you mean by 90 working days...
does it have to be exact?  will 4 months do instead? (replace d,-90 with m,-4)
if it needs to be exact then you'll need to set up a calendar table which details
what is and isn't a working day... and then work out the date of the 91st working day ago...

hth
 



declare @deletedate datetime
-- reduce now to just the date then calc "90" days prior
set @deletedate = dateadd(d,-90,convert(char(8),getdate(),112))
Print ' '
Print 'Deleting Salesorder older than '+convert(varchar(26),@deletedate)
Print 'Print Start Time '+convert(varchar(26),getdate())
Print ' '
Declare @rows
Delete from Salesorders
Where CreationTimestamp < @deletedate
set @rows = @@Rowcount
Print ' '
Print 'Finished at '+convert(varchar(26),getdate())
Print 'Deleted '+convert(varchar(10),@rows)+' rows'
Print ' '

 
0
 

Author Comment

by:dm98101
ID: 20364054
can you make more usefull solution I can program myself
0
 
LVL 10

Expert Comment

by:AustinSeven
ID: 20364090
imitchie:

"i don't think it's really friendly to post a link that leads to..."

 I forgot that I was registerd to www.sqlmag.com - so I don't get the signup message.    Wasn't on purpose!   Sorry dm98101.   No big deal to register though and it might be useful.

AustinSeven
0
 
LVL 25

Accepted Solution

by:
imitchie earned 1500 total points
ID: 20364699
just use enterprise manager, and add a Job, scheduled daily, to and make the first step's SQL:

delete  Salesorders
where creationTimeStamp <
(select dateadd(wk, -90/5, convert(varchar,getdate(),102)))
0
 

Author Comment

by:dm98101
ID: 20625693
delete  Salesorders
where creationTimeStamp <
(select dateadd(wk, -90/5, convert(varchar,getdate(),102)))
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question