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:
idt/myidt)and I get final debt on 20/05/2023.
My final aim is get data like this:
starting debt (01/01/2020): 10 000
| Partner_id | DocId | DocDate | incomes | sales | payments | MyPayments | final debt |
|---|---|---|---|---|---|---|---|
| 11 | 21 | 05.05.2020 | 500 | 9500 | |||
| 11 | 144 | 08.08.2020 | 200 | 970 | |||
| 11 | 205 | 10.04.2021 | 400 | 10100 | |||
| 11 | 351 | 15.05.2022 | 2000 | 8100 | |||
| 11 | 1025 | 20.05.2023 | 500 | 7600 |
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:
| Partner_id | DocId | DocDate | incomes | sales | payments | MyPayments | final debt |
|---|---|---|---|---|---|---|---|
| 11 | 21 | 05.05.2020 | 500 | -500 | |||
| 11 | 144 | 08.08.2020 | 200 | 200 | |||
| 11 | 205 | 10.04.2021 | 400 | 400 | |||
| 11 | 351 | 15.05.2022 | 2000 | -2000 | |||
| 11 | 1025 | 20.05.2023 | 500 | -500 |
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
| P.ID | Name | IFD | MYIFD | IFD_DATE | DOCID | DOCDATE | OPER | INCOMES | SALES | PAYMENTS | MYPAYMENTS | SUMMA | TOT |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 11 | Firm1 | 10000.00 | 0.00 | 2020-01-01 | 21 | 2020-05-05 | Income | 500.00 | null | null | null | -500.00 | 9500.00 |
| 11 | Firm1 | 10000.00 | 0.00 | 2020-01-01 | 144 | 2020-08-08 | Sale | null | 200.00 | null | null | 200.00 | 9700.00 |
| 11 | Firm1 | 10000.00 | 0.00 | 2020-01-01 | 205 | 2021-04-10 | Sale | null | 400.00 | null | null | 400.00 | 10100.00 |
| 11 | Firm1 | 10000.00 | 0.00 | 2020-01-01 | 351 | 2022-05-15 | Income | 2000.00 | null | null | null | -2000.00 | 8100.00 |
| 11 | Firm1 | 10000.00 | 0.00 | 2020-01-01 | 1025 | 2022-05-15 | Payment | null | null | 2000.00 | null | -2000.00 | 6100.00 |
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.