Hi, I have a database of data about properties, when they were sold and how much they went for, which we are using to predict the current price of that property. In order to do this I have constructed the following querySELECT [Postcode],c.Postout,[House Type], Year, Avg_monthly_increase, ID, decPrice,Application_Date, NewBuild,LeaseStatus,SAON,PAON,Thoroughfare, Locality, Town, District, County, Easting, Northing, strAddress FROM(SELECT Postout,[House Type] , Year, AVG(Percentage_Increase) AS Avg_Monthly_Increase FROM (SELECT a.Postout, a.[House Type], a.Year, a.Month, a.Average_Price, a.Min_Price, a.Max_Price, ((a.Average_Price-b.Average_Price)/b.Average_Price)*100 AS Percentage_Increase FROM [HouseData] AS a INNER JOIN [HouseData] AS b ON a.Postout = b.Postout WHERE a.[House Type] = b.[House Type] AND((a.YEAR = b.YEAR AND a.Month = b.Month+1) OR (a.year = b.Year+1 AND a.Month = 1 AND b.MONTH = 12))) AS Query GROUP BY Postout, [House Type], Year) AS c INNER JOIN Postcode_DateSplit ON Postcode_DateSplit.Postout = c.Postout WHERE c.[House Type] = Postcode_Datesplit.[HouseType] AND c.Year=YEAR(Postcode_DateSplit.Application_Date)
which provides details of each property along with the increase in value per month for a house of that type, in that postal region in a specific year.From this I now need to calculate, for each property, its current value, and the difference between that and the value when it was sold, by multiplying the original sale value by the appropriate percentage for each month since the original sale. For example, if a house was sold in November 2009 and the average value increase per month was 2.76% then I would multiply the sale price by 2.76% twice. If in 2010 the average value increase per month became 3.2% then I would multiply the value by 3.2% 12 times and so on until reaching the estimated current value.The problem I am having is the best way to turn this into a query, or otherwise calculate the results for a large number of records. Any suggestions would be appreciated.Thanks.
Confusing Calculation Query
Posted - 2011-03-01 : 10:31:55
