Select max date that is less than a specific date from other table but maximum among remaining dates-CodePudding

I am facing a challenge in Selecting the max date from a table that is less than a specific date from another table but is maximum among remaining dates in the snowflake database. The description is a bit confusing, so let me try to explain:

E.g: Table X

ID1	Date1
1	07-17-2022
2	05-22-2022

Table Y

ID1	Date2
1	07-25-2022
1	06-04-2022
1	05-07-2022
1	04-01-2022
2	05-20-2022
2	08-01-2022
2	05-02-2022
2	04-16-2022

So I want to achieve the below logic:
for ID1: DATE2 < DATE1 but DATE2= max(remaining Dates in DATE2 where ID=1)

The final result should be

ID1	Date2
1	06-04-2022
1	05-20-2022

Note: Date format = mm-dd-yyyy

CodePudding user response：

I have figured it out. This can be done using correlated subquery as given below

SELECT
    Y.ID1,
    Y.DATE2
FROM
    Y
INNER JOIN X ON
    Y.ID1 = Y.ID1
WHERE
    Y.DATE2 = (
    SELECT
        MAX(DATE2)
    FROM
        Y
    WHERE
        Y.ID1 = X.ID1
        AND Y.DATE2 < X.DATE1)

CodePudding user response：

It is possible to achieve similar result using QUALIFY and DATEDIFF(used for finding the lowest difference between two dates):

SELECT Y.ID1, Y.DATE2
FROM  X
INNER JOIN Y
  ON  X.ID1 = Y.ID1
QUALIFY ROW_NUMBER() OVER(PARTITION BY Y.ID1 
                          ORDER BY DATEDIFF('DAY', Y.Date2, X.Date1) DESC) = 1