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 payment_tab.

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 charge_line?

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.

Please advice.

EDIT: Example result is view on charge_line_tab which could look like:

Exptected result on charge_line_tab would look like:
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)
  • Could you add the expected result? – McNets Jan 4 at 10:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.