So I have a table that record loan data and another table that record past due date for each account number. I need to calculate how many days that someone didn't pay the loan from their past due date. I already did a query and now my result is something like this
LOAN_DATE ACC_NO PASTDUE_DATE PASTDUE_DAYS
2022-08-11 1079696087 null 0
2022-08-12 1079696087 null 0
2022-08-13 1079696087 null 0
2022-08-14 1079696087 null 0
2022-08-15 1079696087 null 0
2022-08-16 1079696087 2022-08-16 1
2022-08-18 1079696087 2022-08-18 2
2022-08-19 1079696087 2022-08-19 3
2022-08-20 1079696087 2022-08-20 4
2022-08-22 1079696087 2022-08-22 5
2022-08-23 1079696087 2022-08-23 6
2022-08-24 1079696087 2022-08-24 7
2022-08-25 1079696087 2022-08-25 8
2022-08-26 1079696087 2022-08-26 9
2022-08-27 1079696087 2022-08-27 10
2022-08-29 1079696087 2022-08-29 11
2022-08-30 1079696087 2022-08-30 12
2022-09-01 1079696087 null 0
2022-09-02 1079696087 2022-09-02 1
Notice that my LOAN_DATE data has skipped some days and I need the PASTDUE_DAYS value still counting even the date isn't there. I want my result to be something like this
LOAN_DATE ACC_NO PASTDUE_DATE PASTDUE_DAYS
2022-08-11 1079696087 null 0
2022-08-12 1079696087 null 0
2022-08-13 1079696087 null 0
2022-08-14 1079696087 null 0
2022-08-15 1079696087 null 0
2022-08-16 1079696087 2022-08-16 1
2022-08-18 1079696087 2022-08-18 3
2022-08-19 1079696087 2022-08-19 4
2022-08-20 1079696087 2022-08-20 5
2022-08-22 1079696087 2022-08-22 7
2022-08-23 1079696087 2022-08-23 8
2022-08-24 1079696087 2022-08-24 9
2022-08-25 1079696087 2022-08-25 10
2022-08-26 1079696087 2022-08-26 11
2022-08-27 1079696087 2022-08-27 12
2022-08-29 1079696087 2022-08-29 14
2022-08-30 1079696087 2022-08-30 15
2022-09-01 1079696087 null 0
2022-09-02 1079696087 2022-09-02 1
Is there something that I can try? Sorry if my explanation is a bit awkward
This is my dbfiddle: https://dbfiddle.uk/AdcGy89l
WITH LOAN (LOAN_DATE, ACC_NO) AS (
VALUES
(DATE('2022-08-11'), '1079696087'),
(DATE('2022-08-12'), '1079696087'),
(DATE('2022-08-13'), '1079696087'),
(DATE('2022-08-14'), '1079696087'),
(DATE('2022-08-15'), '1079696087'),
(DATE('2022-08-16'), '1079696087'),
(DATE('2022-08-18'), '1079696087'),
(DATE('2022-08-19'), '1079696087'),
(DATE('2022-08-20'), '1079696087'),
(DATE('2022-08-22'), '1079696087'),
(DATE('2022-08-23'), '1079696087'),
(DATE('2022-08-24'), '1079696087'),
(DATE('2022-08-25'), '1079696087'),
(DATE('2022-08-26'), '1079696087'),
(DATE('2022-08-27'), '1079696087'),
(DATE('2022-08-29'), '1079696087'),
(DATE('2022-08-30'), '1079696087'),
(DATE('2022-09-01'), '1079696087'),
(DATE('2022-09-02'), '1079696087')
),
LOAN_PASTDUE (PASTDUE_DATE, ACC_NO) AS (
VALUES
(DATE('2022-08-16'), '1079696087'),
(DATE('2022-08-18'), '1079696087'),
(DATE('2022-08-19'), '1079696087'),
(DATE('2022-08-20'), '1079696087'),
(DATE('2022-08-22'), '1079696087'),
(DATE('2022-08-23'), '1079696087'),
(DATE('2022-08-24'), '1079696087'),
(DATE('2022-08-25'), '1079696087'),
(DATE('2022-08-26'), '1079696087'),
(DATE('2022-08-27'), '1079696087'),
(DATE('2022-08-29'), '1079696087'),
(DATE('2022-08-30'), '1079696087'),
(DATE('2022-09-02'), '1079696087')
),
G AS (
-- group number generation
-- it increases when
-- PASTDUE_DATE IS NULL OR "PASTDUE_DATE_PREVIOUS" (LOAN_DATE sort) IS NULL
SELECT
A.*
, B.PASTDUE_DATE
, SUM
(
CASE
WHEN
B.PASTDUE_DATE IS NULL
OR LAG (B.PASTDUE_DATE) OVER (PARTITION BY A.ACC_NO ORDER BY A.LOAN_DATE) IS NULL
THEN 1
ELSE 0
END
) OVER (PARTITION BY A.ACC_NO ORDER BY A.LOAN_DATE)
AS GRP
FROM LOAN A
LEFT JOIN LOAN_PASTDUE B ON B.PASTDUE_DATE = A.LOAN_DATE AND B.ACC_NO = A.ACC_NO
)
SELECT
LOAN_DATE, ACC_NO, PASTDUE_DATE
, CASE
WHEN PASTDUE_DATE IS NOT NULL
THEN DAYS (LOAN_DATE) - DAYS (MIN (LOAN_DATE) OVER (PARTITION BY ACC_NO, GRP)) + 1
ELSE 0
END
AS PASTDUE_DAYS
FROM G
ORDER BY ACC_NO, LOAN_DATE
| LOAN_DATE | ACC_NO | PASTDUE_DATE | PASTDUE_DAYS |
|---|---|---|---|
| 2022-08-11 | 1079696087 | 0 | |
| 2022-08-12 | 1079696087 | 0 | |
| 2022-08-13 | 1079696087 | 0 | |
| 2022-08-14 | 1079696087 | 0 | |
| 2022-08-15 | 1079696087 | 0 | |
| 2022-08-16 | 1079696087 | 2022-08-16 | 1 |
| 2022-08-18 | 1079696087 | 2022-08-18 | 3 |
| 2022-08-19 | 1079696087 | 2022-08-19 | 4 |
| 2022-08-20 | 1079696087 | 2022-08-20 | 5 |
| 2022-08-22 | 1079696087 | 2022-08-22 | 7 |
| 2022-08-23 | 1079696087 | 2022-08-23 | 8 |
| 2022-08-24 | 1079696087 | 2022-08-24 | 9 |
| 2022-08-25 | 1079696087 | 2022-08-25 | 10 |
| 2022-08-26 | 1079696087 | 2022-08-26 | 11 |
| 2022-08-27 | 1079696087 | 2022-08-27 | 12 |
| 2022-08-29 | 1079696087 | 2022-08-29 | 14 |
| 2022-08-30 | 1079696087 | 2022-08-30 | 15 |
| 2022-09-01 | 1079696087 | 0 | |
| 2022-09-02 | 1079696087 | 2022-09-02 | 1 |