Reeya Oberoi Reeya Oberoi - 6 months ago 26
SQL Question

Compare date values between 3 columns in SQL server and pull asset information in SQL SERVER

I have a table with three date columns and some asset information.

ASSET (APPR_DATE_1, APPR_DATE_2, APPR_DATE_3, ID, NAME, ADDRESS,...)

SAMPLE DATA -

'1-1-2010','1-1-2012','1-1-2015',1,'Peter','USA'....
'1-1-2015','1-10-2014','1-19-2013',2,1,'John','USA'....
'1-1-2008','1-25-2010','4-29-2011',3,'Edward','UK'....
'1-25-2012','10-25-2011','9-11-2013',4,'Nancy','Kenya'....


RESULT -


'1-1-2010','1-1-2012','1-1-2015',1,'Peter','USA'....
'1-1-2008','1-25-2010','4-29-2011',3,'Edward','UK'....
'1-25-2012','10-25-2011','9-11-2013',4,'Nancy','Kenya'....

/* If you see the result set, it contains 1 row less than sample data. The result set contains only those rows where the difference between most recent and second recent date is greater than 365 OR difference between second recent date and oldest date is greater than 365 */


They have random dates. For each row, I need to find the recent, second recent and oldest date and once I get that, I need to compare -


  1. List out the assets information where Difference between recent date and second recent date is greater than 1 year.

  2. List out the assets information where Difference between second recent date and oldest date is greater than 1 year.

  3. Point 2 should NOT include the assets which are already included in point 1



Col
ID
is unique. Is this possible in SQL?

Answer

Use CASE expressions to determine the recent, second, and oldest date. Put them in a CTE and use the column names in the WHERE clause

WITH CTE AS(
    SELECT *,
        RecentDate =
            CASE
                WHEN APPR_DATE_1 >= APPR_DATE_2 AND APPR_DATE_1 >= APPR_DATE_3 THEN APPR_DATE_1
                WHEN APPR_DATE_2 >= APPR_DATE_3 AND APPR_DATE_2 >= APPR_DATE_1 THEN APPR_DATE_2
                WHEN APPR_DATE_3 >= APPR_DATE_2 AND APPR_DATE_3 >= APPR_DATE_1 THEN APPR_DATE_3
            END,
        SecondDate =
            CASE
                WHEN APPR_DATE_1 BETWEEN APPR_DATE_2 AND APPR_DATE_3 OR APPR_DATE_1 BETWEEN APPR_DATE_3 AND APPR_DATE_2 THEN APPR_DATE_1
                WHEN APPR_DATE_2 BETWEEN APPR_DATE_1 AND APPR_DATE_3 OR APPR_DATE_1 BETWEEN APPR_DATE_3 AND APPR_DATE_1 THEN APPR_DATE_2
                WHEN APPR_DATE_3 BETWEEN APPR_DATE_1 AND APPR_DATE_2 OR APPR_DATE_1 BETWEEN APPR_DATE_2 AND APPR_DATE_1 THEN APPR_DATE_3
            END,
        OldestDate =
            CASE
                WHEN APPR_DATE_1 <= APPR_DATE_2 AND APPR_DATE_1 <= APPR_DATE_3 THEN APPR_DATE_1
                WHEN APPR_DATE_2 <= APPR_DATE_3 AND APPR_DATE_2 <= APPR_DATE_1 THEN APPR_DATE_2
                WHEN APPR_DATE_3 <= APPR_DATE_2 AND APPR_DATE_3 <= APPR_DATE_1 THEN APPR_DATE_3             
            END
    FROM #ASSET
)
SELECT
    APPR_DATE_1, APPR_DATE_2, APPR_DATE_3, ID, NAME, ADDRESS 
FROM CTE
WHERE
    DATEDIFF(DAY, SecondDate, RecentDate) > 365
    OR DATEDIFF(DAY, OldestDate, SecondDate) > 365

ONLINE DEMO