Unfortunately just re-posting the same "table" is not much help. We need to able to run queries against this stuff to help. It seems you did not read the article that was previously suggested.
your ddl and dml might
look like this:
create table #Rates
create table #Records
select 'USA', '2012-10-10 00:00:00.000', '2012-10-14 23:59:59.000', 0.01 union all
select 'USA', '2012-10-15 00:00:00.000', NULL, 0.02 union all
select 'UK', '2012-10-13 00:00:00.000', NULL, 0.02
select 'USA', '2012-10-14 10:00:00.000', 10 union all
select 'USA', '2012-10-15 20:00:00.000', 10 union all
select 'UK', '2012-10-13 20:00:00.000', 10
select * from #Rates
select * from #Records
drop table #Rates
drop table #Records
The bigger challenge is that it is totally unclear what you want for desired output.
I want to calculate the TotalCost between '2012-10-10 00:00:00.000' and '2012-10-16 00:00:00.000 '
So that picks up 0.01 rate between 10th and 14th and form 15th it should pick 0.02 and calculate the total.
What does that mean? Is the total cost supposed to be the sum of all CostPerSec values that are valid during any of the Date values in #Records?
You do seem to have some major issues in your tables. You have nothing that can be a primary key and your naming convention is a bit too vague. A table called Records or Rates is unclear. Hopefully your real table names have better names. Also, you should avoid reserved words as object names. Date is not a good name for a column. Not only is it a reserved word it gives no indication what it is.
If you can answer the question behind the logic this should be pretty simple.
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/
for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter
.Cross Tabs and Pivots, Part 1 – Converting Rows to Columns Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs Understanding and Using APPLY (Part 1)Understanding and Using APPLY (Part 2)