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
| SELECT CAST(ipi."IDENTITY" AS VARCHAR2(30)) AS CODE_CLIENT
, CAST(ipi."BALANCE" AS DECIMAL(13,2)) AS CLI_SOLD
, CAST(lid."INV_AMOUNT" AS DECIMAL(13,2)) AS CLI_ENCOURS
, <ID_CLIENT> AS ID_CLIENT
, sum(case
when lid."LEDGER_DATE" >= trunc(sysdate, 'yyyy')
then <PRIX_FACTURE>
else 0
end) as SIT_CA_N
, sum(case
when lid."LEDGER_DATE" >= trunc(sysdate, 'yyyy') - interval '1' YEAR
and lid."LEDGER_DATE" < trunc(sysdate, 'yyyy')
then <PRIX_FACTURE>
else 0
end) as SIT_CA_N1
, sum(case
when lid."LEDGER_DATE" >= trunc(sysdate, 'yyyy') - interval '2' YEAR
and lid."LEDGER_DATE" < trunc(sysdate, 'yyyy') - interval '1' YEAR
then <PRIX_FACTURE>
else 0
end) as SIT_CA_N2
FROM "IFSAPP"."IDENTITY_PAY_INFO_CU_QRY" ipi
JOIN "IFSAPP"."LEDGER_ITEM_CU_DET_QRY" lid
ON lid."COMPANY" = ipi."COMPANY"
AND lid."IDENTITY" = ipi."IDENTITY"
AND lid."PARTY_TYPE" = ipi."PARTY_TYPE"
AND lid."PARTY_TYPE_DB" = ipi."PARTY_TYPE_DB"
WHERE lid."LEDGER_DATE" >= trunc(sysdate, 'yyyy') - interval '2' YEAR
AND lid."LEDGER_DATE" < trunc(sysdate, 'mm')
group by CAST(ipi."IDENTITY" AS VARCHAR2(30))
, CAST(ipi."BALANCE" AS DECIMAL(13,2))
, CAST(lid."INV_AMOUNT" AS DECIMAL(13,2))
, <ID_CLIENT>; |
Partager