Automatically Change the Attribute

[ashishsmith]

I have designed a Data Base on SQL Server Database. I want to change an attribute in that Data Base according to the date.

There are 2 things, Difference of Date and Flag.

I want to change the value of Flag from 0 to 1 when Difference of Date is between 1 <= x <= 30.

If the Difference of Date goes beyond the range means it may be 0 or 31 than the Flag should turn automatically to 0.

Is it possible to do that in SQL Server Database.

I don't know if I am wrong, but I think it can be done through "Triggers" in DB, that would check Date of Diff and Flag everyday. Whenever the Date of Diff goes beyondthe range, It updates the Flag.

If anybody has done this before please reply to me. It is kind of urgent, because I am going on vacaton and I need to automate the process.

Thank you,

Ashish Smith

 

[SQLSister]

Triggers only happen when the data changes or is inserted or deleted. It doesn't sound like that is what you want. You need to write a stored procedure and then schedule it as a job to run nightly.

update table1
Set field1 = case when datediff(dd,date1, date2)< 1 then 0
when datediff(dd,date1, date2) > 30 then 0
else 1 end

Note that this will update every record.


Questions about posting. See faq183-874

 

[ashishsmith]

Thank you very much.I guess this would resolve my issue. Let me check it and I would getback to you if I have any problems. That is correct, I will have to update all the records so that every record has up-to-date flags and the operations depending on this flag runs smoothely. Just a question out of curiosity, is there any in-built function named Datediff() to get the difference between date1 and date2 in days? If yes oh that would be great to deal with.
Neways thank you very much for your answer.

Let me go back and check.


Thank you,

Ashish Smith

 

[SQLSister]

DAtediff is a built in function, that's why I put it inthe code.

Questions about posting. See faq183-874

 

[ashishsmith]

oh great. Thank you very much for your help.

I will try it tonight and if it works, it's good to go else i would keep researching on how to get
the stored procedure run automatically every night, is it possible through DOS prompt same as DTS package can be executed remotely through a DOS prompt with the command DTSRUN and then servername, username, password, DatabaseName and FileName.
Stored Procedure should also have some kind of command, I guess, which can be called or executed every night remotely through a DOS Prompt.

But hey thank you very much for your help and I guess this much would solve my problem.

 

[SQLSister]

Read about jobs in BOL.

Questions about posting. See faq183-874

 

[SQLBill]

And BOL stands for:

BOL=Books OnLine=Microsoft SQL Server's HELP
Installed as part of the Client Tools
Found at Start>Programs>Microsoft SQL Server>Books OnLine

-SQLBill

Posting advice: FAQ481-4875

 

[ashishsmith]

thank u guy u havebeen gret help.


ASA i test it tonight will let u know.



thank u,

Ashish Smith