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
1,589 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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 500 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

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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.
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.

828 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