I'm exhibiting a house in the following table given below. Ok now what i must do is, discover the property in the same position(suppose my rentals are situated in sec-19, match for sec-19 and when not one of them found there then search for the whole city) using the following condition that it ought to be published ten days back or maybe none is published ten days back than take result based on thirty days back.

I've the next table(Qualities) pointed out below:

IDPropertyidUseridProjectid..Cost...Listing time...

Ok now what i wish to retrieve out of this table may be the 'Propertyid' and 'Average Price' of individuals qualities whose listing time is under ten days if not one of them are under ten days then return the end result for under thirty days.

Can anybody assist me to fixing this issue. Thanks ahead of time.

Or simply Any body can answer me with no Location match.

I have to calculate 'Average Price' in the qualities published ten days ago, if there's no property published ten days ago then go as thirty days ago. Something similar to this:

Select AVG(Price) As Average_Price from Properties where (DATEDIFF(day,listingtime,getdate())<30 or DATEDIFF(day,listingtime,getdate())<10)

But here i get just one Area 'Average Price' and here also i'm not putting the check to filter whether it's been published ten days ago or thirty days ago. Knidly Recheck and then try to solve my problem. Thanks ahead of time.

That which you referred to above means, when there's 1 property within the last ten days, then you need to list that single one. Do this: (I personally use P.age is perfect for simplicity.)

SELECT * FROM properties P  
WHERE  
-- 10 days old records, if any:  
(((select count(1) from properties p1 where p1.age > 10) > 0) AND (P.age > 10))  
OR  
-- 30 days old records, if zero 10-day-old records found:  
(((select count(1) from properties p1 where p1.age > 10) = 0) AND (P.age > 30))  

if you would like a listing of property IDs, only just one average cost its them, existence is going to be simpler should you choose several query. this seems like a great situation for implementing a saved procedure. you could do this it such as this:

  1. pass location in to the saved procedure and choose from qualities in which the location matches
  2. place the outcomes right into a temp table with propertyid, listingtime and avgprice (presently empty)
  3. choose rows having a DATEDIFF of ten days. if results, choose rows having a DATEDIFF of thirty days
  4. choose AVG cost in the temp table, blocking on ten days if there's data. place that value into each row on the table

you might want to have fun with altering an order of products one and three to determine what yields better performance.