I have a query which produces a table of duplicate records from an larger dataset, and I want to be able to do further analysis on this output data.

Below is an example of what would be produced to work from.

I want to be able to identify, for the individual properties, instances where an oldest rating for that property was less than 50, but the newest rating is now greater than 70.

On the example below, only the ratings related to property 17 would meet this criteria, and so the row with Reference jygh would be extracted.

This is specifically analysis of a change over time so just a max/min comparison of Rating would not be sufficient.

EDIT: I've edited the data example to show a drop in Rating value which would not be meet the criteria.

CodePudding user response：

This one is working only if not considering the dates :

select PROPERTYADDRESS
from TABLE1
where RATING <= 50
and PROPERTYADDRESS in (
 select PROPERTYADDRESS
 from TABLE1
 where RATING >= 70
);

check it here : https://dbfiddle.uk/6yoRNP74

CodePudding user response：

This will take in consideration the oldest and the newest rates :

SELECT mt.PROPERTYADDRESS    
FROM TABLE1 mt 
INNER JOIN
    (
        SELECT PROPERTYADDRESS, MIN(Date) AS MinDate
        FROM TABLE1
        GROUP BY PROPERTYADDRESS
    ) t ON mt.PROPERTYADDRESS = t.PROPERTYADDRESS AND mt.DATE = t.MinDate
 WHERE RATING <= 50
 AND mt.PROPERTYADDRESS in (
     SELECT mt.PROPERTYADDRESS 
     FROM TABLE1 mt 
     INNER JOIN
        (
            SELECT PROPERTYADDRESS, MAX(Date) AS MaxDate
            FROM TABLE1
            GROUP BY PROPERTYADDRESS
        ) t ON mt.PROPERTYADDRESS = t.PROPERTYADDRESS AND mt.DATE = t.MaxDate
     WHERE RATING >= 70
);

Check it here : https://dbfiddle.uk/XzEIlvKc