Sql Conditional Query

Question

I show the property from the following table below. Now, what I need to do is find the property in the same place (suppose my property is in second 19, match with second 19 and, if none of them are found there, then look for the whole city) with the following condition that it should be sent 10 days ago, or if none was published 10 days ago, then the result will be received based on 30 days ago.

I have the following table (Properties) mentioned below:

Now, what I want to extract 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 in less than 30 days.

Can anyone help me solve this problem. Thank you in advance.

Or just any body can answer me without matching the location.

I need to calculate the "average price" for properties published 10 days ago, if there is no property published 10 days ago, then take it as 30 days ago. Something like that:

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

But here I get only one field "Average price", and here I also do not put a check to filter whether it was published 10 days ago or 30 days ago. Double-check and try to solve my problem. Thank you in advance.

Answer 1

, , . , varchar (100). . , , , .

   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

, , varchars.

Answer 2

, , , 10 1 , . : ( P.age .)

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))  

Answer 3

, , , . . :

• ,
• temp , avgprice ( )
• DATEDIFF 10 . 0 , DATEDIFF 30
• AVG temp, 10 , . .

1 3, , .

Answer 4

- :

, ( avg () iq1, Listdate > dateadd (, -10, getdate()) oq.section = iq1.section, avg () iq2, Listdate > dateadd (, -30, getdate()) oq.section = iq2.section, 0) oq

: , , , , , null , 0.

- , , SARGable .

, ,

, . 10 , 30 , 10 30 , avergageType 1,2,3 4 .

SQL - , - :)

sectionID,
(
( avg () iq1, : Datedate > dateadd (, -10, getdate())
> oq.sectionID = iq1.sectionID),
( avg () iq2, : , (, -30, getdate())
> oq.sectionID = iq2.sectionID),
( avg () iq1, : Datedate > dateadd (, -10, getdate())),
( avg () iq1, : Datedate > dateadd (, -30, getdate ( ))),
0) ,
CASE WHEN ( > avg () iq1
Listdate > dateadd (, -10, getdate())
oq.sectionID = iq1.sectionID) 1 ELSE
CASE WHEN ( > avg () iq2
Listdate > dateadd (, -30, getdate())
oq.sectionID = iq2.sectionID) 2 ELSE
CASE WHEN ( > avg () iq1
Listdate > dateadd (, -10, getdate())) 3 ELSE
CASE WHEN ( > avg () iq1
Listdate > dateadd (, -30, getdate())) 4 ELSE
5 END END END END AS AverageType from Prices oq
sectionID = @SectionID group byIDID