Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1611
  • Last Modified:

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.

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
dm98101
Asked:
dm98101
  • 5
  • 2
  • 2
  • +1
1 Solution
 
AustinSevenCommented:
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
 
imitchieCommented:
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
 
imitchieCommented:
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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

Open in new window

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

Open in new window

0
 
LowfatspreadCommented:
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
 
dm98101Author Commented:
can you make more usefull solution I can program myself
0
 
AustinSevenCommented:
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
 
imitchieCommented:
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
 
dm98101Author Commented:
delete  Salesorders
where creationTimeStamp <
(select dateadd(wk, -90/5, convert(varchar,getdate(),102)))
0

Featured Post

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!

  • 5
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now