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
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
| WITH VALUE AS(
SELECT *
FROM (VALUES (DATE('2012-01-01'), DATE('2012-02-01'))
) AS VALUE(DATE_INCLUSIVE, DATE_EXCLUSIVE)
)
SELECT DATESOMMAIRE,
FIID,
TYPECOMPTE,
CODEJURIDICTION,
NOMBRETRANSACTION,
MONTANTNET,
-- Somme Interchange Net / Somme du montant net
DECIMAL(INTERCHANGENET / NULLIF(MONTANTNET, DECIMAL(0, 25, 12)), 15, 2) AS MONTANTINTERCHANGENET
FROM
(
SELECT DATESOMMAIRE,
FIID,
TYPECOMPTE,
CODEJURIDICTION,
-- Somme du nombre de transaction
SUM(COUNT_OF_IDTRANSACTION) AS NOMBRETRANSACTION,
-- Somme du montant net
DECIMAL(SUM(SUM_MONTANT_NET), 15, 2) AS MONTANTNET,
DECIMAL(SUM(SUM_INTERCHANGE_NET), 25, 12) AS INTERCHANGENET
FROM
(
SELECT
--DATE (DCALENDRIER.ANNEE || '-' || DCALENDRIER.MOIS || '-01') AS DATESOMMAIRE,
--DATEDEPOT - DAY(DATEDEPOT - 1 DAY) DAYS AS DATESOMMAIRE,
DATESQL - DAY(DATESQL - 1 DAY) DAYS AS DATESOMMAIRE,
LEFT(FTRANSACTION.NOCPTE, 6) AS FIID,
FCOMPTES.TYPECOMPTE,
FTRANSACTION.CODEJURIDICTION,
-- Compte total du nombre de transaction / criètre de la clause GROUP BY
COUNT(FTRANSACTION.IDTRANSACTION) AS COUNT_OF_IDTRANSACTION,
-- Somme du montant * facteur négatif / criètre de la clause GROUP BY
-- facture négative = (-1 ou 1)
DECIMAL(SUM(MONTANT * FACTEURNEGATIF), 25, 12) AS SUM_MONTANT_NET,
-- Taux Interchange / 100 * somme du montant net
DECIMAL(FTRANSACTION.TAUXINTERCHANGE / DECIMAL(100, 5, 2) * NULLIF(SUM(FTRANSACTION.MONTANT * FTRANSACTION.FACTEURNEGATIF), 0), 25, 12) AS SUM_INTERCHANGE_NET
FROM (
SELECT DATESQL,
CHAR(LEFT(NUMCARTE, 13), 13) AS NOCPTE,
FTRANSACTION.IDTRANSACTION,
FTRANSACTION.MONTANT,
DTYPETRANSACTION.FACTEURNEGATIF,
DCODEINTERCHANGE.TAUXINTERCHANGE,
DCODEINTERCHANGE.CODEJURIDICTION
--DATE (DCALENDRIER.ANNEE || '-' || DCALENDRIER.MOIS || '-01') AS DATESOMMAIRE
FROM DTM.FTRANSACTION AS FTRANSACTION
INNER JOIN DTM.DCODEINTERCHANGE AS DCODEINTERCHANGE
ON DCODEINTERCHANGE.IDCODEINTERCHANGE = FTRANSACTION.IDCODEINTERCHANGE
INNER JOIN DTM.DTYPETRANSACTION AS DTYPETRANSACTION
ON DTYPETRANSACTION.TYPETRANSACTION = FTRANSACTION.TYPETRANSACTION
INNER JOIN DTM.DCALENDRIER AS DCALENDRIER
ON DCALENDRIER.IDDATE = FTRANSACTION.IDDATE
INNER JOIN VALUE AS VALUE
ON 1=1
WHERE INDCARTESCD = 'O'
AND TYPECARTE = 'V'
AND FTRANSACTION.TYPETRANSACTION IN (SELECT TYPETRANSACTION FROM DTM.DTYPETRANSACTION WHERE TYPETRANSACTION NOT IN ('25', '26', '27', '28'))
AND DATEDEPOT BETWEEN VALUE.DATE_INCLUSIVE - 1 MONTH AND VALUE.DATE_EXCLUSIVE + 1 MONTH - 1 DAY
AND FTRANSACTION.IDDATE IN (SELECT IDDATE FROM DTM.DCALENDRIER INNER JOIN VALUE AS VALUE ON 1=1 WHERE DATESQL BETWEEN VALUE.DATE_INCLUSIVE AND VALUE.DATE_EXCLUSIVE - 1 DAY)
AND DATESQL BETWEEN VALUE.DATE_INCLUSIVE AND VALUE.DATE_EXCLUSIVE - 1 DAY
ORDER BY 2
) AS FTRANSACTION
INNER JOIN DTM.FCOMPTES AS FCOMPTES
ON FCOMPTES.NOCPTE = FTRANSACTION.NOCPTE
GROUP BY DATESQL, FTRANSACTION.NOCPTE, TYPECOMPTE, CODEJURIDICTION, TAUXINTERCHANGE
)
GROUP BY DATESOMMAIRE, FIID, TYPECOMPTE, CODEJURIDICTION
)
ORDER BY DATESOMMAIRE, FIID, TYPECOMPTE, CODEJURIDICTION
FOR READ ONLY
WITH UR |
