Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
chetankelkar
Starting Member
5 Posts |
 Posted - 2005-05-09 : 07:45:54
|
| Hi SQL Gurus,I am not sure where this goes, hence posted over here. Regret if this is not the correct place for this posting.I have a scenario here wherein I am required to decrement a value of a field on specified time interval and set it to its original value at a certain time. Lets say in the morning price of a product is $200 and it has to set to decrement every 30 minutes by $1 till it reaches $180. After it reaches $180 the reduction will stop.On the next day again it should start at $200 and perform similar action.Could someone guide me on this?Thanks!ChetanChetan Kelkar |
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2005-05-09 : 20:14:48
|
| so do you have a table which defines the rate, amount and limit of reduction per item?do you have a table with each item and the current price?--I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
 |
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2005-05-09 : 23:43:45
|
Schedule this job to run every hour, from 8AM till 6AM (20 hours, 20 bucks...)UPDATE MyTable SET Price = Price - 1 Schedule this job to run every morning at 7AMUPDATE MyTable SET Price = MorningPrice |
|
|
|
chetankelkar
Starting Member
5 Posts |
Posted - 2005-05-10 : 01:33:05
|
| Yes rrb I have a table defining that. Not even that I also want the timings of such price drop to be set dynamically.Thanks SamC. Also is there any way that we can define sql jobs dynamically, ie. through code? (ASP for that matter?)Problem is if I schedule a job, I will have to set it for all products at one. But for every product there is a different price drop that is set.For example:Product A has price drop of $0.5 per hourProduct B has price drop of $0.3 every two hours.In short every product has a different price drop and different execution time to be set.Thanks,ChetanChetan Kelkar |
|
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2005-05-10 : 01:42:19
|
Probably your best option is then to define a regular job, at the limit of your timing intervals (eg every 10 minutes). This job should check to see whether any price updates are due, and update them accordingly.I've had to do this before - its OK depending on the accuracy of the timing you need. If you create a "lastupdate" field in your table, then the code for the job is trivial:update pricesset price = case when a.price - b.decrement < b.lowerlimit then b.lowerlimit else a.price - b.decrement end, lastupdate = getdate()from prices a inner join decrements bon a.itemid = b.itemidwhere datediff('n',a.lastupdate,getdate()) > b.interval(I don't have SQL server or BOL loaded here, so the syntax might be wrong - but you get the idea).Oh, and don't forget Fourier - if you need an accuracy of +/- 2 minutes, then you need to run the job every minute....In case anyone's interested, the application I did was to start Air-conditioning units in a high-rise tower in Sydney at preset times. SQL Server (6.5) kicked off an application which would in turn start the Air-con for that section of the building. Worked a treat, and as far as I know, is still working. (Anyone sitting in Chifley Tower as we speak? Is your air conditioning on at the moment!??? ) Why SQL Server I hear you ask? Because there were over 1000 controlled air con outlets in the building (>42 floors). The customers submitted requests for air-con through a web-page interface, and were automatically billed for usage (actual usage, not just based on their request). Hundreds of clients, complex timing rules, manual overrides etc, loop controllers for each. Great little system, even if I do say so! And at the heart of it all, about 20 lines of SQL!--I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
|
|
|
|
|
|
|
|
Help Needed