I have a table that store loan records and I need to compare today's data with yesterday's. The problem is, my company didn't process and store any data on Sunday or public holiday, so every Monday or the day after public holiday data always be NULL.

My table looks like this

LOAN_DATE  | YSTR_DATE | ACC_NO  | BALANCE       | LOAN_ID
2022-01-07 | null      | 5000093X| -619294125.98 |  LO1
2022-01-07 | null      | 1079653X| -650000000.00 |  LO2
2022-01-08 | 2022-01-07| 5000093X| -619294125.98 |  LO1
2022-01-08 | 2022-01-07| 1079653X| -650000000.00 |  LO2
2022-01-10 | null      | 5000093X| -619294125.98 |  LO1
2022-01-10 | null      | 1079653X| -650000000.00 |  LO2
2022-01-11 | 2022-01-10| 5000093X| -619294125.98 |  LO1
2022-01-11 | 2022-01-10| 1079653X| -650000000.00 |  LO2
2022-01-12 | 2022-01-11| 5000093X| -596544416.08 |  LO1
2022-01-12 | 2022-01-11| 1079653X| -650000000.00 |  LO2

How can I get and join the data from Saturday or day before public holiday?

This is my dbfiddle: https://dbfiddle.uk/9NLAopij

CodePudding user response：

Try this as is.

WITH LOAN (LOAN_DATE, ACC_NO, LOAN_ID, BALANCE) AS(
VALUES
(DATE ('2022-01-07'), '5000093X', 'LO1', -619294125.98),
(DATE ('2022-01-07'), '1079653X', 'LO2', -650000000),
(DATE ('2022-01-08'), '5000093X', 'LO1', -619294125.98),
(DATE ('2022-01-08'), '1079653X', 'LO2', -650000000),
(DATE ('2022-01-10'), '5000093X', 'LO1', -619294125.98),
(DATE ('2022-01-10'), '1079653X', 'LO2', -650000000),
(DATE ('2022-01-11'), '5000093X', 'LO1', -619294125.98),
(DATE ('2022-01-11'), '1079653X', 'LO2', -650000000),
(DATE ('2022-01-12'), '5000093X', 'LO1', -596544416.08),
(DATE ('2022-01-12'), '1079653X', 'LO2', -650000000)
)
  
    SELECT
          LOAN_DATE
        , LAG (LOAN_DATE) OVER (PARTITION BY ACC_NO ORDER BY LOAN_DATE) as YSTR_DATE
        , ACC_NO
        , LOAN_ID
        , BALANCE
        , LAG (BALANCE) OVER (PARTITION BY ACC_NO ORDER BY LOAN_DATE) as YSTR_BAL
    FROM
        LOAN
    ORDER BY 
        LOAN_DATE