SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Help Needed
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

chetankelkar
Starting Member

India
5 Posts

Posted - 05/09/2005 :  07:45:54  Show Profile  Reply with Quote
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!

Chetan

Chetan Kelkar

Edited by - chetankelkar on 05/09/2005 07:48:57

rrb
SQLTeam Poet Laureate

Australia
1479 Posts

Posted - 05/09/2005 :  20:14:48  Show Profile  Reply with Quote
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"
Go to Top of Page

SamC
White Water Yakist

USA
3464 Posts

Posted - 05/09/2005 :  23:43:45  Show Profile  Reply with Quote
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 7AM

UPDATE MyTable
  SET Price = MorningPrice



Go to Top of Page

chetankelkar
Starting Member

India
5 Posts

Posted - 05/10/2005 :  01:33:05  Show Profile  Reply with Quote
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 hour
Product 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,
Chetan

Chetan Kelkar
Go to Top of Page

rrb
SQLTeam Poet Laureate

Australia
1479 Posts

Posted - 05/10/2005 :  01:42:19  Show Profile  Reply with Quote
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"

Edited by - rrb on 05/10/2005 02:13:56
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.24 seconds. Powered By: Snitz Forums 2000