HI Sean Lange
Sorry - I didnt check the post before. Thanks for your advise. I will follow it hereafter and these are not real table names and are with fake values.
I need to join the 2 tables on country field and calculate the total cost.
im using this below query which is wrong, I need to pass 2 dates from and to date in the query, so it will calculate the actual cost based on the date range.
On the RatesTable the when the ToDate field has NULL entry - its refers till date.
select y.country , SUM(x.costpersec * y.duration) as TotalCost
from rates x join records y on x.country = y.country
and y.date between x.FromDate and y.todate
GROUP BY y.country
I need to calculate the total cost grouping by countries. Eg, If i want to know the total cost for USA between Oct 12 and Oct 16 then It should process (10*0.01) (14th) + (10 *0.02 ) (15th) = 0.3
I hope i made it clear now, Please let me
Thanks