Forum Discussion
sum columns between 2 dates
Hi ryan_mayu ,
Thanks for reply and interest in my post.
Here a sample of my data and what I need to have:
Customer code | Customer name | DARE | AVERE | SALDO | Accounting date |
01 | CUSTOMER01 | 10 | 100 | 90 | 15/12/2022 |
01 | CUSTOMER01 | 10 | 30 | 20 | 01/01/2023 |
01 | CUSTOMER01 | 10 | 30 | 20 | 15/01/2023 |
02 | CUSTOMER02 | 50 | 100 | 50 | 15/12/2022 |
02 | CUSTOMER02 | 50 | 0 | -50 | 01/01/2023 |
02 | CUSTOMER02 | 10 | 110 | 100 | 15/01/2023 |
03 | CUSTOMER03 | 20 | 20 | 0 | 15/12/2022 |
03 | CUSTOMER03 | 30 | 100 | 70 | 01/01/2023 |
03 | CUSTOMER03 | 20 | 0 | -20 | 15/01/2023 |
Field SALDO is the difference between AVERE – DARE.
I need to know the sum amount of DARE-AVERE-SALDO between a period, for example from 01/01/2023 to 31/01/2023 and SALDO after 01/01/2023 (the 1st date entered).
This is the result that I need:
Customer code | Customer name | SALDO after | DARE between | AVERE between | SALDO between |
01 | CUSTOMER01 | 90 | 20 | 60 | 40 |
02 | CUSTOMER02 | 50 | 60 | 110 | 50 |
03 | CUSTOMER03 | 0 | 50 | 100 | 50 |
I hope to be clearer
how to get the SALDO after?
why it is 90 for customer 1? the date for that is before 2023/1/1
Hi ryan_mayu
I've written too fast and I've make some error.
You must take the sum of fields SALDO after date 01/01/2023 and that is the "SALDO before" (I've wrong written "SALDO after").
"SALDO between" is the sum of all fileds "SALDO" between dates selected.Hi ryan_mayu
yes this is, now I need also the column "SALDO before".
I've do it but I find a way only using the same query 2 times and I would find a way to have the new column with only a query, because it contains more than 700.000 records and the weight of file is very important 🙂 .
As you can see in picture below, I need also another column that contain the "same" of SALDO column (AVERE - DARE), but for all the records before the first date in selection....