Get Yesterday's Data Dynamically in DB2

Hilman Dhannysworo 0
Viewed 40

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

db2 sql
1 Answers

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
LOAN_DATE YSTR_DATE ACC_NO LOAN_ID BALANCE YSTR_BAL
2022-01-07 1079653X LO2 -650000000.00
2022-01-07 5000093X LO1 -619294125.98
2022-01-08 2022-01-07 1079653X LO2 -650000000.00 -650000000.00
2022-01-08 2022-01-07 5000093X LO1 -619294125.98 -619294125.98
2022-01-10 2022-01-08 1079653X LO2 -650000000.00 -650000000.00
2022-01-10 2022-01-08 5000093X LO1 -619294125.98 -619294125.98
2022-01-11 2022-01-10 1079653X LO2 -650000000.00 -650000000.00
2022-01-11 2022-01-10 5000093X LO1 -619294125.98 -619294125.98
2022-01-12 2022-01-11 1079653X LO2 -650000000.00 -650000000.00
2022-01-12 2022-01-11 5000093X LO1 -596544416.08 -619294125.98
Mark Barinstein0
Related