sqloracle-database

Oracle/SQL Find Date or Closest earlier date to specified date


I am trying to run a query within oracle which creates a value. The underlying table does not always contain a match for a specific date. So i have to either find the record with the date specified or find the closest date below the specified date.

SUM(CASE 
            WHEN S.VAL_SECURITY NOT IN ('GBP')
                AND E.SECURITY_ID NOT IN ('GBP')
                THEN (
                        (((amount) * - 1) / nvl(s.minor_per_unit, 1)) * (
                            SELECT EXCHANGE_RATE
                            FROM ISET.PREVIOUS_PRICES
                            WHERE ISET.PREVIOUS_PRICES.SECURITY_ID = E.SECURITY_ID
                                AND VAL_DATE = TO_DATE('05/08/2019', 'DD/MM/YYYY')
                            )
                        ) * (
                        SELECT EXCHANGE_RATE
                        FROM ISET.PREVIOUS_PRICES
                        WHERE SECURITY_ID = S.VAL_SECURITY
                            AND VAL_DATE = TO_DATE('05/08/2019', 'DD/MM/YYYY')
                        )
            WHEN S.VAL_SECURITY IN ('GBP')
                AND E.SECURITY_ID NOT IN ('GBP')
                THEN (
                        (((amount) * - 1) / nvl(s.minor_per_unit, 1)) * (
                            SELECT EXCHANGE_RATE
                            FROM ISET.PREVIOUS_PRICES
                            WHERE ISET.PREVIOUS_PRICES.SECURITY_ID = E.SECURITY_ID
                                AND VAL_DATE = TO_DATE('05/08/2019', 'DD/MM/YYYY')
                            )
                        )
            ELSE ((amount) * - 1) / nvl(s.minor_per_unit, 1)
END) GBP_VALUE

The code in question is

E.SECURITY_ID AND VAL_DATE = TO_DATE('05/08/2019', 'DD/MM/YYYY')

So assuming it doesn't find a record for the specified date then it needs to find the max date record closest to but below the specified date.

Any help would be greatly appreciated.

Thank you


Solution

  • Could you try this? I haven't verified it.

        val_date =
    (SELECT val_date
       FROM (SELECT MAX (b.val_date) val_date,
                    MIN (TO_DATE ('05/08/2019', 'DD/MM/YYYY') - b.val_date)
                       diff
               FROM PREVIOUS_PRICES b
              WHERE     SECURITY_ID = E.SECURITY_ID
                    AND status_date <= TO_DATE ('05/08/2019', 'DD/MM/YYYY')
             UNION ALL
             SELECT MIN (val_date),
                    MIN (b.val_date - TO_DATE ('05/08/2019', 'DD/MM/YYYY'))
               FROM PREVIOUS_PRICES b
              WHERE     SECURITY_ID = E.SECURITY_ID
                    AND status_date > TO_DATE ('05/08/2019', 'DD/MM/YYYY')
             ORDER BY 2)
      WHERE ROWNUM = 1)