How get subtotals of rows and final value of debt

I'm using Delphi 10.4 and Firebird 3. I have a database with separate tables of incomes, sales, income's payments, sale's payments.

Partners table has 3 fields for storing starting debt of partner at the moment application was installed :

• idt (initial financial debt) if the owe me (for example, 10000)
• myidt if I owe them (for example, -10000).
• idt_date starting date of debt, for example debt on 01/01/2020.

Lets suppose I have to calculate final debt on specific date, for example, on 20/05/2023.

I must:

• take starting debt value (idt/myidt)
• minus summas of incomes in interval (01/01/2020 - 20/05/2023)
• plus summas of sales in interval
• minus summas of his payments
• plus summas of my payments

and I get final debt on 20/05/2023.

My final aim is get data like this:

starting debt (01/01/2020): 10 000

How may I get such subtotals (debt at specific time) and final debt on 20/05/2023?

It's query what I can write and result table however I know it's not what I expected:

 select
     Partner_id,
     Docid,
     DocDate,
     Incomes,
     Sales,
     Payments,
     MyPayments,
     -coalesce(Incomes,0)+coalesce(Sales,0)-coalesce(Payments,0)+ coalesce(MyPayments,0) as 
                                                                                         debt
   from
     (
     Select                                       --incomes
      Partner.partner_id,
      Incomes.Docid,
      Incomes.DocDate,
      Incomes.summa as Incomes,
      0 as Sales,
      0 as Payments,
      0 as MyPayments
      from Incomes, partners
      where 
        Incomes.partner_id= partners.partner_id and 
        Partners.partner_id= :partner_id and
        Incomes.DocDate>=partners.ifd_date and Incomes.DocDate<=:d 
        
        
      union all
    
     Select                                      --Sales 
      Partners.partner_id,
      Sales.Docid,
      Sales.DocDate,
      0,
      Sales.summa,
      0,
      0
      from Sales, Partners
      where 
        Sales.partner_id=partners.partner_id and 
        Partners.partner_id= :partner_id and
        Sales.Docdate>=partners.ifd_date and Sales.DocDate<=:d 
        
        
      union all
     
      
     Select                                      --Payments 
      Partners.partner_id,        
      Payments.Docid,
      Payments.DocDate,
      0,
      0,
      Payments.summa,
      0
     from Payments, partners
     where 
       Payments.partner_id=partners.partner_id and 
       Partners.partner_id= :partner_id and
       Payments.DocDate>=partners.ifd_dt and Payments.DocDate<=:d 
       

     union all
     
      
     Select                                      --MyPayments 
      Partners.partner_id,        
      MyPayments.Docid,
      MyPayments.DocDate,
      0,
      0,
      0,
      MyPayments.summa
     from MyPayments, partners
     where 
       MyPayments.partner_id=partners.partner_id and
       Partners.partner_id= :partner_id and
       MyPayments.DocDate>=partners.ifd_dt and 
       MyPayments.DocDate<=:d 
       
      
  ) DerivedTable1

Try this example, with your test data

select
      p.Partner_id,partner
     ,ifd,myifd,ifd_date
     ,d.Docid,d.DocDate
     ,d.Oper
     ,case when oper='Income' then -d.summa end Incomes
     ,case when oper='Sale' then d.summa end Sales
     ,case when oper='Payment' then -d.summa end Payments
     ,case when oper='MyPayment' then d.summa end MyPayments
     ,d.summa
     ,(p.ifd+p.myifd)+sum(d.summa)
           over(partition by d.partner_id order by d.docdate,d.docid) as tot
   from Partners p left join
     (                         --incomes
     Select 'Income' as oper,partner_id,Docid,DocDate,-summa as summa  from Incomes
     union all
     Select 'Sale' as oper,partner_id,Docid,DocDate,summa  from Sales
     union all
     Select 'MyPayment' as oper,partner_id,Docid,DocDate,summa  from MyPayments
     union all
     Select 'Payment' as oper,partner_id,Docid,DocDate,-summa as summa from Payments
     ) d on d.partner_id=p.partner_id
    where d.docdate>=p.ifd_date and d.docdate<=DATE'2023-05-20'

Query result as draft

> why does you use over(partition by d.partner_id order by...) analytical function

To calculate accumulated amount from first doc to current. initial financial debt + accumulated amount = current financial debt

Windows aggregate function sum(d.summa)over() - calculate sum for column d.summa for current row and all rows before current row in order by docdate and docid - accumulated amount.
For example, with doc (205,2021-04-10) sum -500+200+400=100. with ifd=10000 current balance=10000+100=10100.
Another doc (1025,2022-05-15) - sum calculated as -500+200+400-2000-2000=-3900. with ifd=10000-3900=6100 final debt.
Additional ordering by DocId is necessary for order doc 351 and 1025, when they have same date.
Partitioning by partner_id is necessary to calculate sum for each partner separately.

You can calculate totals for type operation "Sales"

sum(d.sales)over(partition by d.partner_id) as total_sales
this calculates total Sales for partner(partner_id). We do not use in this case order clause in over() - calculate total Sales (200+400=600) for partner.

count(*)over(partition by d.partner_id) as total_operations (=5) this calculate rows count for partner_id.

Fiddle