Luis Cazares (10/15/2012)
Sean, for what I understood, he might need to use the original query and filter the rows with a where clause.
DECLARE @FromDate datetime = '20121013', @ToDate datetime = '20121015'SELECT rc.Country, SUM(r.CostPerSec*rc.Duration)
FROM #Rates r
JOIN #Records rc ON rc.Date >= r.FromDate
AND rc.Date <= isnull(r.ToDate, rc.Date)
AND r.Country = rc.Country
WHERE rc.Date >= @FromDate
AND rc.Date <=@ToDate
GROUP BY r.Country
Unless I am misreading your code, that would actually return almost the same thing, except it won't handle the null for r.ToDate. It is the same because the date checks are in the join condition.
_______________________________________________________________
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/