Help with query

[youmak]

Hello everyone :)

Sorry for bothering you, but I have a problem with solving one query, in general query is much more compllicated, but I will try to simplilfy it

Lets asume that we have table named info and it looks like this:

id , name,price, from_date, to_date,
1 , name1, 10, 2007-10-01, 2007-11-01
2 , name2, 12, 2007-10-01, 2007-11-04
3 , name3, 40, 2007-10-01, 2007-12-01
4 , name1, 12, 2007-11-02, 2008-01-01
5 , name2, 15, 2007-11-05, 2008-01-31
6 , name3, 45, 2007-12-02, 2008-03-15
7 , name2, 14, 2008-02-01, 2008-05-13
8 , name2, 17, 2008-05-14, 2008-08-30
9 , name1, 13, 2008-01-02, 2008-03-31
10 , name1, 14, 2008-04-01, 2008-10-01

Well, now we have given time period as two dates for which we want to make a summry. We want to calculate how much do we have to pay for each name between given dates assuming that we have to pay every day. and for each day the price is taken from row in which given date falls between from_date and to_date (from_date and to_date are just the boundary in which this price is actual). And my problem arises when I want to calculate the price for which a given date is higher than those in table.
For example I want to calculate 2007-10-05 to 2009-01-01 and the assumption is, that for the dates which are not in the table the last price should be taken into consideration.
Calculation for the name2 should be like this:
2007-10-01, 2007-11-04 - number of days * 12
2007-11-05, 2008-01-31 - number of days * 15
2008-02-01, 2008-05-13 - number of days * 14
2008-05-14, 2008-08-30 - number of days * 17
additionaly
2008-09-01, 2009-01-01 - number of days * the last used price. in this case 17

I don't know how to wirte an query which calculates it. I have tried many ways, but I failed.

Even small help would be appreciated

[dickersonka]

i would suggest to use a stored procedure to do all the calculations if you haven't already

declare num_days, price, temp_price int;

loop through your current time spans you have, and set price each time

set temp_price = select price from info where .....;

if your last select had a record then update price

set price = temp_price;

when you run into the date range you don't have, you don't update price and maintain the previous value

I'm not sure whether you are having a problem with the days portion or price portion, let me know if you need more assistance

[youmak]

Hi :)

Thank you very much, I will try your solution, It seems quite reasonable

If I will have problem, I will surly let you know :)