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

CodePudding user response：

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