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
| PARAMETERS mYear long;
SELECT tt.RPG, Max(tt.LD) as LastDec, Max(tt.m1) as Jan, Max(tt.m2) as Fev, Max(tt.m3) as Mar, Max(tt.m4) as Avr, Max(tt.m5) as Mai, Max(tt.m6) as Jui, Max(tt.m7) as Jul, Max(tt.m8) as Aou, Max(tt.m9) as Sep, Max(tt.m10) as Oct, Max(tt.m11) as Nov, Max(tt.m12) as Dec FROM
(
SELECT t0.RPG,
(select nz(sum(nz(t1.[MntAcompte],0)-nz(t1.[MontantRetrait],0)),0) from tbl_CompteBanque t1 where (format(t1.Dte,"yyyymm")<mYear & "01") and t1.RPG=t0.RPG group by t1.RPG,0) as LD,
(select nz(sum(nz(t1.[MntAcompte],0)-nz(t1.[MontantRetrait],0)),0) from tbl_CompteBanque t1 where (format(t1.Dte,"yyyymm")<mYear & "02") and t1.RPG=t0.RPG group by t1.RPG,0) as m1,
(select nz(sum(nz(t1.[MntAcompte],0)-nz(t1.[MontantRetrait],0)),0) from tbl_CompteBanque t1 where (format(t1.Dte,"yyyymm")<mYear & "03") and t1.RPG=t0.RPG group by t1.RPG,0) as m2,
(select nz(sum(nz(t1.[MntAcompte],0)-nz(t1.[MontantRetrait],0)),0) from tbl_CompteBanque t1 where (format(t1.Dte,"yyyymm")<mYear & "04") and t1.RPG=t0.RPG group by t1.RPG,0) as m3,
(select nz(sum(nz(t1.[MntAcompte],0)-nz(t1.[MontantRetrait],0)),0) from tbl_CompteBanque t1 where (format(t1.Dte,"yyyymm")<mYear & "05") and t1.RPG=t0.RPG group by t1.RPG,0) as m4,
(select nz(sum(nz(t1.[MntAcompte],0)-nz(t1.[MontantRetrait],0)),0) from tbl_CompteBanque t1 where (format(t1.Dte,"yyyymm")<mYear & "06") and t1.RPG=t0.RPG group by t1.RPG,0) as m5,
(select nz(sum(nz(t1.[MntAcompte],0)-nz(t1.[MontantRetrait],0)),0) from tbl_CompteBanque t1 where (format(t1.Dte,"yyyymm")<mYear & "07") and t1.RPG=t0.RPG group by t1.RPG,0) as m6,
(select nz(sum(nz(t1.[MntAcompte],0)-nz(t1.[MontantRetrait],0)),0) from tbl_CompteBanque t1 where (format(t1.Dte,"yyyymm")<mYear & "08") and t1.RPG=t0.RPG group by t1.RPG,0) as m7,
(select nz(sum(nz(t1.[MntAcompte],0)-nz(t1.[MontantRetrait],0)),0) from tbl_CompteBanque t1 where (format(t1.Dte,"yyyymm")<mYear & "09") and t1.RPG=t0.RPG group by t1.RPG,0) as m8,
(select nz(sum(nz(t1.[MntAcompte],0)-nz(t1.[MontantRetrait],0)),0) from tbl_CompteBanque t1 where (format(t1.Dte,"yyyymm")<mYear & "10") and t1.RPG=t0.RPG group by t1.RPG,0) as m9,
(select nz(sum(nz(t1.[MntAcompte],0)-nz(t1.[MontantRetrait],0)),0) from tbl_CompteBanque t1 where (format(t1.Dte,"yyyymm")<mYear & "11") and t1.RPG=t0.RPG group by t1.RPG,0) as m10,
(select nz(sum(nz(t1.[MntAcompte],0)-nz(t1.[MontantRetrait],0)),0) from tbl_CompteBanque t1 where (format(t1.Dte,"yyyymm")<mYear & "12") and t1.RPG=t0.RPG group by t1.RPG,0) as m11,
(select nz(sum(nz(t1.[MntAcompte],0)-nz(t1.[MontantRetrait],0)),0) from tbl_CompteBanque t1 where (format(t1.Dte,"yyyymm")<mYear+1 & "01") and t1.RPG=t0.RPG group by t1.RPG,0) as m12
FROM tbl_CompteBanque t0
) tt
GROUP BY tt.RPG |
Partager