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 prices
set 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 b
on a.itemid = b.itemid
where 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
Posted - 05/09/2005 : 07:45:54
