Conditional Sql Query

I am displaying a property from the following table given below. Now what i have to do is, find the property in the same location(suppose my property is located in sec-19, match for sec-19 and if none of them found there then search for the entire city) with the following condi开发者_如何学JAVAtion that it should be posted 10 days back or if none is posted 10 days back than take result on the basis of 30 days back.

I have the following table(Properties) mentioned below:

Now what i want to retrieve from this table is the 'Propertyid' and 'Average Price' of those properties whose listing time is less than 10 days if none of them are less than 10 days then return the result for less than 30 days.

Can anybody help me solving this problem. Thanks in advance.

Or just Any body can answer me without the Location match.

I need to calculate 'Average Price' from the properties posted 10 days ago, if there is no property posted 10 days ago then take it as 30 days ago. Something like this:

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

But here i am getting only one Field 'Average Price' and here also i am not putting the check to filter whether it has been posted 10 days ago or 30 days ago. Knidly Recheck and try to solve my problem. Thanks in Advance.

I spent some time on this and I believe I addressed all of your concerns. I wasn't completely sure of the datatypes of city or location so I used varchar(100) This should address all of your concerns. Please comment if there is a situation that you described that this doesn't solve.

   CREATE PROCEDURE [dbo].[GetRecentlyListedProperties]
(@location varchar(100), @city varchar(100),@propertyID int)
As
Begin
DECLARE @numberOfDays int,
        @propertyCount int, 
        @IsLocation bit -- looking for a location and not a city    
SET @Propertycount = 0
SET @numberOfDays= 10 
-- CHECK TO SEE IF THERE ARE LISTINGS IN THE LAST 10 DAYS IN THE SAME LOCATION
SELECT  @PropertyCount = 
 Count(*) FROM properties where location = @location and DATEDIFF(day,listingtime,GETDATE()) < @numberOFDays
 and PropertyID != @propertyID
If(@PropertyCount = 0)
Begin
-- CHECK TO SEE IF THERE ARE LISTINGS IN THE LAST 10 DAYS IN THE SAME CITY
SELECT  @PropertyCount = Count(*) from properties where city = @city 
        AND DATEDIFF(day,listingtime,GETDATE()) < @numberOFDays
        AND PropertyID != @propertyID   
    IF(@PropertyCount = 0 )
    BEGIN
    SET @NumberOfDays = 30
    -- CHECK TO SEE IF THERE ARE LISTINGS IN THE LAST 30 DAYS IN THE SAME LOCATION
    SELECT  @PropertyCount = COUNT(*) from properties where location = @location 
            AND DATEDIFF(day,listingtime,GETDATE()) < @numberOFDays
            AND PropertyID != @propertyID
        IF(@PropertyCount = 0 )
        BEGIN
        -- CHECK TO SEE IF THERE ARE LISTINGS IN THE LAST 30 DAYS IN THE SAME CITY
        SELECT @PropertyCount = Count(*) from properties where city = @city 
                AND DATEDIFF(day,listingtime,GETDATE()) < @numberOFDays
                AND PropertyID != @propertyID
        END
        ELSE
        SET @IsLocation = 1 --There are properties in the same location in the last 30 days
    END
    ELSE
    SET @IsLocation  = 0 -- There are properties listed int he city in the last 10 days
End
Else
SET @IsLocation = 1
-- This is where the appropriate results are returned. 
IF(@IsLocation = 1)
Begin
SELECT * ,(SELECT AVG(PRICE) as AveragePrice
       FROM PROPERTIES 
       WHERE DATEDIFF(day,listingtime,GETDATE()) < @numberOFDays
         AND Location = @Location
         AND PropertyID != @propertyID)
FROM Properties 
WHERE DATEDIFF(day,listingtime,GETDATE()) < @numberOFDays
      AND Location = @Location
      AND PropertyID != @propertyID
End
ElSE
SELECT * ,(SELECT AVG(PRICE) as AveragePrice
      FROM PROPERTIES 
          WHERE DATEDIFF(day,listingtime,GETDATE()) < @numberOFDays
          AND City = @City
          AND PropertyID != @propertyID)
FROM Properties 
WHERE DATEDIFF(day,listingtime,GETDATE()) < @numberOFDays
      AND City = @City 
      AND PropertyID != @propertyID
End

You may have to change some datatypes for foreign keys for location and city as I used them as varchars.

if you want a list of property IDs, but only a single average price for all of them, life will be simpler if you do more than one query. this sounds like a good case for using a stored procedure. you could do it like this:

1. pass location into the stored procedure and select from properties where the location matches
2. insert the results into a temp table with propertyid, listingtime and avgprice (currently empty)
3. select rows with a DATEDIFF of 10 days. if 0 results, select rows with a DATEDIFF of 30 days
4. select AVG price from the temp table, filtering on 10 days if there is data. insert that value into each row of the table

you may want to play with changing the order of items 1 and 3 to see which yields better performance.

It should be possible to use something like this:

select section, coalesce(select avg(Price) from table iq1 where Listingdate > dateadd(day,-10,getdate()) and oq.section =iq1.section, select avg(price) from table iq2 where Listingdate > dateadd(day,-30,getdate()) and oq.section =iq2.section,0) as averageprice from table oq group by section

A couple of notes: sorry I'm not in front of my pc so I couldn't proof read this and the brackets may be out, but that's the idea in general-use coalesce to either the first criteria and providing there are no records the average should return null and use the second criteria and if that has none finally 0.

The other thing is to use the date add function as above to make the expression SARGable and there able to use indexes.

Hope this helps, Steve

EDIT

Here is the code which I have done basic testing on. It is simple, and will give you first the average for that section for the last 10 days, failing that, the last 30 days for the section, failing that the last 10 days for all sections and failing that the last 30 days for all sections. It will also give an avergageType of 1,2,3 or 4 respectively.

I apologise in advance for the state of the SQL display - I am yet to work out how to format it nicely-and its really late :)

select sectionID,
coalesce (
(select avg(Price) from Prices iq1 where Listingdate > dateadd(day,-10,getdate())
and > oq.sectionID = iq1.sectionID),
(select avg(price) from Prices iq2 where Listingdate > dateadd(day,-30,getdate())
and > oq.sectionID =iq2.sectionID),
(select avg(Price) from Prices iq1 where Listingdate > dateadd(day,-10,getdate())),
(select avg(Price) from Prices iq1 where Listingdate > dateadd(day,-30,getdate())),
0 ) as averageprice,
CASE WHEN (select > avg(Price) from Prices iq1
where Listingdate > dateadd(day,-10,getdate()) and
oq.sectionID = iq1.sectionID) IS NOT NULL THEN 1 ELSE
CASE WHEN (select > avg(price) from Prices iq2
where Listingdate > dateadd(day,-30,getdate()) and
oq.sectionID =iq2.sectionID) IS NOT NULL THEN 2 ELSE
CASE WHEN (select > avg(Price) from Prices iq1
where Listingdate > dateadd(day,-10,getdate())) IS NOT NULL THEN 3 ELSE
CASE WHEN (select > avg(Price) from Prices iq1
where Listingdate > dateadd(day,-30,getdate())) IS NOT NULL THEN 4 ELSE
5 END END END END AS AverageType from Prices oq
where sectionID = @SectionID group by sectionID

What you described above means, that if there is 1 property in the last 10 days, then you want to list that single one. Try this: (I use P.age is 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))