0

I am experiencing quite difficult problem and hope for your feedback. In my table I have a "Services"column in "MyData" table which contains names of my services. I have created SQL statement which should be executed via maintenance plan and get the minimum id value from one table and update "Value" column in "MyData" table:

declare @id int
select 
    @id = min(id) 
from 
    MyTable
where 
    MyTime >= dateadd(MM, -12, DATEFROMPARTS(YEAR(getdate()),MONTH(getdate()),1))

update 
    MyData
set 
    Value = @id
where 
    ColumnName= 'MyServiceName'

The statement works correctly, but I need to automate the things. Problem is, I have several Services and it will be inefficient to manually change "MyServicename" column in where clause each time it is needed to be updated. I ask for your advice, how can I automate the process of checking the "MyServiceName" in order to execute the statement for appropriate service? Sorry if it is written in too complicated way, but I hope could express the trouble I am expressing.

  • do you mean get all service names that need to be updated and then update all of them in one sentence? – McNets Nov 14 '16 at 13:51
  • Actually, I mean that service names should be updated by rotation. But if you have other ideas that can be useful I will be thankful) – Nizami Khalilbayli Nov 14 '16 at 14:07
  • is it possible to determine a maintenance period for each service? for example "every 6 month" – McNets Nov 14 '16 at 14:11
  • For now, it should be executed once a month. Don`t pay attention to "MM, -12" part of the query, it is just for testing – Nizami Khalilbayli Nov 14 '16 at 14:15
  • I't a bit confusing the mean of 'MyTable' and 'MyData'. In MyData (services table) you could set a 'last_maintenance_date' field. Just to know what services need to be updated. Then (I don't know the mean of MyTable.id), launch a cursor that updates record by record all affected rows and set last_maintenance_date again. – McNets Nov 14 '16 at 14:31
0

Question is not clear to me but

update 
    MyData
set 
    Value = @id
where 
    ColumnName in ('MyServiceName', 'MyServiceName2')

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.