I'm looking for some idea how to calculate user debt in efficient way.
I have got 4 tables.
user_tab -- definitions of users item_tab -- definitions of items that we can pay for (internet, phone, electricity, etc.) payment_tab -- past and future payments that user have to pay before deadline income_tab -- registered user incomes for specific item
I'm looking for efficient way for calculating user debt in specific date group by item_id.
For example for user_id = 1 and date = '2023-01-15' (this date is some variable) specific view should take all data from income_tab and sum all incomes for same item_id or null item_id together so we should have:
item_id | sum 1 720.00 2 390.00 3 145.00
in similar way should sum data from payment_tab but should consider only payment_tab items where deadline_date is <= '2023-01-15'
item_id | sum 1 460.00 2 290.00 3 357.00
As you can see there is a debt for item_id = 3. User should pay 357.00 however paid 145.00
I'm looking for some efficient query (view?) that will return information in that way:
user_id | item_id | debt | debt_days 1 3 212.00 90
dept is calculated as sum of payments - incomes debt_days should present amount of days from first payment that wasn't covered by incomes.
It's quite complicated for me ;) and I'm not sure if this is possible to calculate in some one advance query.
Thanks for advice