MySQL solution for this?

[safra]

Imagine a list of different services each have their own row in a MySQL table.

these services go for a certain price in a specified time span (date from and date till). Each service can have multiple time time spans with corresponding prices.
The number of time spans vary per service and time spans are also different for each service.

How should I set up the table so I can quickly get the price for a specific service on a fixed date?

And how should the MySQL select query look like?

Thanks,
Ron

 

[piti]

so here's the table

CREATE TABLE ServicePricesTbl (
ServicePriceId INT not null AUTO_INCREMENT,
ServiceId INT not null ,
ServicePrice FLOAT not null ,
DateFrom DATE not null ,
DateTo DATE not null ,
PRIMARY KEY (ServicePriceId))

and the select (based on curent date)

SELECT ServicePrice FROM ServicePricesTbl WHERE CURDATE() BETWEEN DateFrom AND DateTo

 

[safra]

Thanks Piti,

But what if one service id has multiple time spans with for each one a different price.

How to deal with that?

should I make multiple columns for date from and date to and one fore each price?

And, if so, how can I get the correct price from the table for a certain date?

Ron

 

[piti]

well i think all that is already there

u just enter every time span with its price into the table and that's it, the ServicePriceId handles the uniquenes

INSERT INTO ServicePricesTbl (ServiceId, ServicePrice, DateFrom, DateTo) VALUES (1, 25, '2002-02-02', '2002-03-02');

INSERT INTO ServicePricesTbl (ServiceId, ServicePrice, DateFrom, DateTo) VALUES (1, 45, '2002-04-02', '2002-05-02');

so now you have 2 records in the table for one service and different time spans
where between Feb. 2 and Feb.3 2002 is the price 25 and between Feb. 4 and Feb. 5 it's 45
i'm not sure if the date format i use in the insert statement is correct :-|, look in the manual for details

and the select i posted previously is just fine

 

[safra]

Yes, sorry I missed that. I am pretty new to this. Now I see how it works.

Thanks piti!

Ron