1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25
| select v_jfd.CP1 COMPTE_PAIEMENT, v_jfd.J_date1 DATE, v_jfd.FINANCEMENT_DEBIT, v_jc.COMMISSIONS /*, v_jfc.FINANCEMENT_CREDIT*/ from
(select coh1.ACCOUNT_ID CP1, DATE_FORMAT(coh1.CREATION_DATE , "%d/%m/%Y") J_date1, SUM(coh1.EXPECTED_OPERATION_AMOUNT) FINANCEMENT_DEBIT
from CONTRACT_OPERATIONS_HISTORY coh1
where coh1.RECORD_TYPE in ('vacation-reglement-transactions')
and DATE_FORMAT(coh1.CREATION_DATE , "%d/%m/%Y") = DATE_FORMAT(sysdate(), "%d/%m/%Y")
and coh1.ACTIVITY_ID = 'crediter-reglement-sur-compte-paiement' and coh1.EXPECTED_OPERATION_AMOUNT > 0
group by coh1.ACCOUNT_ID, DATE_FORMAT(coh1.CREATION_DATE , "%d/%m/%Y")) v_jfd,
(select coh2.ACCOUNT_ID CP2, DATE_FORMAT(coh2.CREATION_DATE , "%d/%m/%Y") J_date2, SUM(coh2.EXPECTED_OPERATION_AMOUNT) COMMISSIONS
from CONTRACT_OPERATIONS_HISTORY coh2
where coh2.RECORD_TYPE in ('vacation-reglement-transactions')
and DATE_FORMAT(coh2.CREATION_DATE , "%d/%m/%Y") = DATE_FORMAT(sysdate(), "%d/%m/%Y")
and coh2.ACTIVITY_ID = 'debiter-commission-sur-compte-paiement'
group by coh2.ACCOUNT_ID, DATE_FORMAT(coh2.CREATION_DATE , "%d/%m/%Y")) v_jc ,
(select coh3.ACCOUNT_ID CP3, DATE_FORMAT(coh3.CREATION_DATE , "%d/%m/%Y") J_date3, SUM(coh3.EXPECTED_OPERATION_AMOUNT) FINANCEMENT_CREDIT
from CONTRACT_OPERATIONS_HISTORY coh3
where coh3.RECORD_TYPE in ('vacation-reglement-transactions')
and DATE_FORMAT(coh3.CREATION_DATE , "%d/%m/%Y") = DATE_FORMAT(sysdate(), "%d/%m/%Y")
and coh3.ACTIVITY_ID = 'crediter-reglement-sur-compte-paiement' and coh3.EXPECTED_OPERATION_AMOUNT < 0
group by coh3.ACCOUNT_ID, DATE_FORMAT(coh3.CREATION_DATE , "%d/%m/%Y")) v_jfc
where v_jfd.J_date1=v_jc.J_date2
and v_jfd.J_date1=v_jfc.J_date3
and v_jfd.CP1= v_jc.CP2
-- and v_jfd.CP1= v_jfc.CP3 => NE CONVIENT PAS, je n'obtiens que les résultats en commun de v_jfd et v_jfc, je veux remplacer cette condition par un RIGHT JOIN
RIGHT OUTER JOIN v_jfc ON v_jfd.CP1 = v_jfc.CP3
order by v_jfd.CP1 |
Partager