Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

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,604 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
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

Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

721 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