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,601 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
Business Impact of IT Communications

What are the business impacts of how well businesses communicate during an IT incident? Targeting, speed, and transparency all matter. Find out more in this infographic.

 
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

Why You Need a DevOps Toolchain

IT needs to deliver services with more agility and velocity. IT must roll out application features and innovations faster to keep up with customer demands, which is where a DevOps toolchain steps in. View the infographic to see why you need a DevOps toolchain.

Question has a verified solution.

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

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

729 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