I’m not much expert to query sql and so I’ve a question:
I have to extract for my clients the active commissions payed, passive commissions payed, the number of closed operations and the margin. In add I have to evidence for every client the contained letter in another schedule (wctdd) in tiprem8 area. In this schedule, another, there is the area codcli also (I’m not expand this part yet).
At moment this is my work: how problems I can evidence that is possible to extract the passive commissions with a research of the same reference number of active commission and connect it to its client. I attach a excel paper with the schedule.
In add, I have to another problem in referement to this: for the calculation of operations if tipoope is different to CVC I must to add the amount that I research in “quanti” and not calculate the only operation how I’ ve done for the CVC.
I hope have been clear to explanation the problem.
select M2.CODCLI, sum( M2.CTVCNO) AS Comm_att, sum(M.CTVCAL) AS comm_pass, sum( M2.CTVCNO) - sum(M.CTVCAL) AS margine, count(M2.ctvcno) as eseguiti
from mov M JOIN mov M2 ON M.N_ORD=M2.N_ORD AND M2.CTVCNO>0 and M2.tipocp='P'
where M.datope= to_date('01-06-2017','DD-MM-YYYY') AND M.CTVCAL>0 and m.tipocp='C' and M.N_ord is not null and M2.datope= to_date('01-06-2017','DD-MM-YYYY')
GROUP BY M2.CODCLI
ORDER BY M2.CODCLI