Sum amounts based off of date query

[speedman_2001]

So I'm not sure that is the best title, but couldn't think of anything much better. Here's what I'm trying to accomplish.

I have a table that stores all of my units and their rent amount for the month:

RentAmounts (
UnitNum, -- IE (1A,2A,3A,4A)
RentMonth, -- IE(1,2,3,4)
RentYear, -- IE(2011,2012)
RentAmount) -- MONEY

I have another table that stores the unit location:

ResTracking (
UnitNum,
LocationID,
ActiveDate)

As a unit is moved to a new location, a record is inserted noting the new location and the date it was moved.

What I am trying to do is create a query that will accept a date range and it will return the total rent amount for the specified unit for that date range for each location. It would be relatively easy to get the total number of days in the month and do the basic math to find it out, but the unit may not be at the same location the entire month, and may actually start at location A, move to B, and then go back to A all within the same month.

When it comes down to it, the query would have to calculate where the unit was at for each DAY in the date range, and then do a sum grouping by each location.

Any thoughts as I'm just stumped on the best way to accomplish this one.

 

[SQL_Hell]

Hi, I don't know if you found a solution for this? But I suspect no-one answered because they couldn't fully understand what you were trying to achieve.
Post back if you still need help and I will try to figure it out for you