| Author |
Topic |
|
regwood79
Starting Member
6 Posts |
 Posted - 2007-10-15 : 11:10:58
|
| Hi everybody:I am developing an asp.net application that will log in a complaint record and keep track of the amount of time it takes for the complaint to be resolved. I need to have a column that keeps track of the number of days that the record as been active. The record should only be active 30 days. What I want to know is if when the record enters the database is there a way in SQL SERVER to have the column count down everyday the record is still open without depending on the asp.net script to do it. I am using sql server 2000.example:on 1/1/2007Case name daysleft status 1 tim 30 Opennext day Case name daysleft status 1 tim 29 Open4 days laterCase name daysleft status 1 tim 25 closedi would like to know how I can get sql server to handle taking care of the countdown automatically instead of having to use asp.net to do it. I hope I made this clear enough to understand. Thanks for any assistance you can give |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-10-15 : 11:13:44
|
why don't you calculate the remaining days when you retrieve the records ?
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-10-15 : 11:15:03
|
Yes. Depending on which edition of Microsoft SQL Server you use, you can create a JOB that does this for you.
E 12°55'05.25"N 56°04'39.16" |
|
|
|
regwood79
Starting Member
6 Posts |
Posted - 2007-10-15 : 11:28:55
|
| thanks khatan and peso for you responses:Can you guys explain to me how i would go about doing this. |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-10-15 : 12:29:33
|
do you have a case open date in the table ? You should if you don't have it.SELECT [case], name, status, daysleft = DATEDIFF(DAY, open_date getdate(), DATEADD(DAY, 30, open_date))FROM yourtable EDIT : bug
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
regwood79
Starting Member
6 Posts |
Posted - 2007-10-15 : 12:39:56
|
| yes i have a case open column that will be get the date the case is opened. what exactly is you query doing? I seed it is get the date difference from the day the case opened to 30 days later. could you explain. I want to have the database program keep up with the value of days left on its own |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-10-15 : 13:02:04
|
DATEADD(DAY, 30, open_date) gives you the expiry datethe DATEDIFF(DAY . . . calculate the number of days left from current day to the expiry date
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
regwood79
Starting Member
6 Posts |
Posted - 2007-10-15 : 13:30:51
|
| How do you run this query? I know this is a newbie question. But how would i run this? |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-10-15 : 21:33:24
|
| Run the query in query analyzer or you can call it in your asp code. |
|
|
|
regwood79
Starting Member
6 Posts |
Posted - 2007-10-16 : 09:54:31
|
| thnx rmiao for your response.but I would like for this to be done before my code starts to work on the database. Can I use the "formula" property in the table designer to accomplish this same task. |
|
|
|
regwood79
Starting Member
6 Posts |
Posted - 2007-10-18 : 10:09:26
|
| Does anyone know? |
|
|
|
KenW
Constraint Violating Yak Guru
391 Posts |
Posted - 2007-10-19 : 15:25:49
|
quote:
Originally posted by regwood79
thnx rmiao for your response.but I would like for this to be done before my code starts to work on the database. Can I use the "formula" property in the table designer to accomplish this same task.
You're retrieving the data to show to someone, right? (Otherwise, you don't need to calculate the number of days left.) Just use the posted SQL in the query that's displaying the data as part of that retrieval.The advantage of doing it that way (calculating it on the fly as you need it) is that it's always up-to-date. |
|
|
|
|
countdown in sql server...