I have go DB structure like here: db-fiddle
I'm looking for some solution that in View for
charge_line_tab table will add information if charge line is paid or not, based on previous records in
Not sure but what I think what should be done is calculate sum of previous charge_lines in previous charge_tab and if sum is smaller then sum of total lines in payment_tab for specific user that
payment_line should be marked as paid.
Not sure if this is possible at all but it is possible to add also information which payment from payment_tab paid specific
What I have in my mind the corner case is that one line was partially covered by one payment so for some
payment_line there should be returned to payments.
I think for that solution subselect isn't good idea ;). Maybe windows function but I'm not sure.
Example result is view on
charge_line_tab which could look like:
Exptected result on charge_line_tab would look like: charge_line_view: charge_line_id, charge_id, description, amount, paid (0 - not paid, 1 - paid, 2 - partially paid), payments_id (this is array of ids of payment_tab that conver that payment, that should be an array because in some cases two or more paymentns can cover one line)