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 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41
| With VENTE
AS
(
select '01/01/2009' as DateV, 10 as MontantV, 'ESPECE' as Typ from dual union all
select '01/01/2009' , 15 , 'ESPECE' from dual union all
select '02/01/2009' , 20 , 'CHEQUE' from dual union all
select '04/01/2009' , 5 , 'CHEQUE' from dual
),
REGLEMENT
AS
(
select '01/01/2009' as DateR, 20 as MontantR from dual union all
select '02/01/2009' , 10 from dual union all
select '03/01/2009' , 15 from dual
)
SELECT
coalesce(VAgg.DateV, RAgg.DateR) AS DATE_PAY,
coalesce(VAgg.ESPECE, 0) AS ESPECE,
coalesce(VAgg.CHEQUE, 0) AS CHEQUE,
coalesce(RAgg.REGLEMENT, 0) AS REGLEMENT
FROM
( SELECT V.DateV,
sum(case V.Typ when 'ESPECE' then V.MontantV else 0 end) AS ESPECE,
sum(case V.Typ when 'CHEQUE' then V.MontantV else 0 end) AS CHEQUE
FROM VENTE V
GROUP BY V.DateV ) VAgg
FULL OUTER JOIN
( SELECT R.DateR,
sum(R.MontantR) AS REGLEMENT
FROM REGLEMENT R
GROUP BY R.DateR ) RAgg
ON RAgg.DateR = VAgg.DateV
ORDER BY
coalesce(VAgg.DateV, RAgg.DateR) ASC;
DATE_PAY ESPECE CHEQUE REGLEMENT
---------- ----------- ----------- -----------
01/01/2009 25 0 20
02/01/2009 0 20 10
03/01/2009 0 0 15
04/01/2009 0 5 0 |
Partager