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
| WITH w_Enc AS (
SELECT C.FACT_ID,
SUM(ECRIT_D_C * ECRIT_VALEUR) AS vEnc
FROM ENCAISSER C
LEFT JOIN ECRITURE E ON E.ECRIT_COMPT = 1 AND C.ECRIT_ID = E.ECRIT_ID
GROUP BY C.FACT_ID
),
w_Solde AS (
SELECT F.FACT_ID, FACT_TTC - COALESCE(vEnc, 0) AS vSolde
FROM FACTURE F
LEFT JOIN w_Enc R ON F.FACT_ID = R.FACT_ID
)
SELECT CLIENT_NUM, CLIENT_CHANT, FACT_NUM, FACT_ECH, FACT_TTC, COMMENT_LIB,
CASE WHEN FACT_ECH <= :pDate1 THEN vSolde END AS vMont_0,
CASE WHEN FACT_ECH BETWEEN :pDate1 AND :pDate_01 THEN vSolde END AS vMont_1,
CASE WHEN FACT_ECH BETWEEN :pDate_01 + 1 AND :pDate_02 THEN vSolde END AS vMont_2,
CASE WHEN FACT_ECH BETWEEN :pDate_02 + 1 AND :pDate_03 THEN vSolde END AS vMont_3,
CASE WHEN FACT_ECH BETWEEN :pDate_03 + 1 AND :pDate_04 THEN vSolde END AS vMont_4,
CASE WHEN FACT_ECH BETWEEN :pDate_04 + 1 AND :pDate_05 THEN vSolde END AS vMont_5,
CASE WHEN FACT_ECH BETWEEN :pDate_05 + 1 AND :pDate_06 THEN vSolde END AS vMont_6,
CASE WHEN FACT_ECH BETWEEN :pDate_06 + 1 AND :pDate_07 THEN vSolde END AS vMont_7,
CASE WHEN FACT_ECH BETWEEN :pDate_07 + 1 AND :pDate_08 THEN vSolde END AS vMont_8
FROM FACTURE F
INNER JOIN CLIENT C ON F.CLIENT_ID = C.CLIENT_ID
INNER JOIN w_Solde S ON F.FACT_ID = S.FACT_ID
LEFT JOIN COMMENT T ON F.FACT_ID = T.FACT_ID
WHERE F.FACT_ARCH = 0 AND vSolde <> 0 AND F.FACT_ECH <= :pDate_08 ORDER BY CLIENT_NUM |
Partager