How to read each row until get to the point to determine if it is paid or overpaid or partially paid

Database

How to read each row until get to the point to determine if it is paid or overpaid or partially paid

How do I read each line to determine if the customer has fully paid or partially paid or never paid?

I would like the code to read every line of each customer's bill until I find the TOTAL_PAYMENT_AMT = TOTAL_DUE. If found, then mark it as paid, if not found, read the next line until I found the TOTAL_PAYMENT_AMT <> 0 and TOTAL_PAYMENT_AMT + TOTAL_ADJ_AMT < TOTAL_DUE then mark it as partially paid or if the TOTAL_PAYMENT_AMT +TOTAL_ADJ_AMT > TOTAL_DUE, then mark it as paid.

For customer 111, the bill is fully paid -13129.54 from reading the first line. But for customer 222, the bill has not paid until the 2nd month for the amount of -27000.00 and for cumster 333, the bill is paid partially the 2nd and the 3rd month.  For customer 444, the bill has never paid.(negative # means paid, positive # means extra amount charged)

This is the result I got from joining tables using rank over()

SELECT CUSTOMER_ID, BILL_DATE , TOTAL_DUE   TOTAL_ADJ_AMT  TOTAL_PAYMENT_AMT

FROM TABLE1

QUALIFY RANK() OVER(PARTITION BY CUSTOMER_ID ORDER BY BILL_DATE ASC) =1

LEFT JOIN

SELECT CUSTOMER_ID, BILL_DATE , TOTAL_DUE   TOTAL_ADJ_AMT  TOTAL_PAYMENT_AMT

FROM TABLE1

QUALIFY RANK() OVER(PARTITION BY CUSTOMER_ID ORDER BY BILL_DATE ASC) > 1


CUSTOMER_ID  BILL_DATE    TOTAL_DUE   TOTAL_ADJ_AMT  TOTAL_PAYMENT_AMT

111                  3/19/2015     13129.54      0                        -13129.54

111                  4/20/2015     13129.54      0                         0

222                  3/25/2015     26334.12      384.00                 0

222                  4/24/2015     26334.12                                 -27000.00

333                  2/25/2015     12720.21      625                    0

333                  3/25/2015     12720.21      407                   -1000.00

333                  4/24/2015     12720.21      0                       -1071.15

444                  2/26/2015      12266.6        0                         0

444                  3/26/2015      12266.6        0                         0


Tags (1)