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 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157
|
sSQL = sSQL + " SELECT DD.PARC_CONTRACTUEL,CC.CODE_TYPECNT,"
sSQL = sSQL + " SUM(CASE"
sSQL = sSQL + " WHEN (MONTH(DD.DTE_FACT)-1) = 1 THEN (CAST(DD.KMS_PARCOURUS as NUMERIC))"
sSQL = sSQL + " ELSE 0"
sSQL = sSQL + " END) AS KMS_PARCOURUS_01,"
sSQL = sSQL + " SUM(CASE"
sSQL = sSQL + " WHEN (MONTH(DD.DTE_FACT)-1) = 2 THEN (CAST(DD.KMS_PARCOURUS as NUMERIC))"
sSQL = sSQL + " ELSE 0"
sSQL = sSQL + " END) AS KMS_PARCOURUS_02,"
sSQL = sSQL + " SUM(CASE"
sSQL = sSQL + " WHEN (MONTH(DD.DTE_FACT)-1) = 3 THEN (CAST(DD.KMS_PARCOURUS as NUMERIC))"
sSQL = sSQL + " ELSE 0"
sSQL = sSQL + " END) AS KMS_PARCOURUS_03,"
sSQL = sSQL + " SUM(CASE"
sSQL = sSQL + " WHEN (MONTH(DD.DTE_FACT)-1) = 4 THEN (CAST(DD.KMS_PARCOURUS as NUMERIC))"
sSQL = sSQL + " ELSE 0"
sSQL = sSQL + " END) AS KMS_PARCOURUS_04,"
sSQL = sSQL + " SUM(CASE"
sSQL = sSQL + " WHEN (MONTH(DD.DTE_FACT)-1) = 5 THEN (CAST(DD.KMS_PARCOURUS as NUMERIC))"
sSQL = sSQL + " ELSE 0"
sSQL = sSQL + " END) AS KMS_PARCOURUS_05,"
sSQL = sSQL + " SUM(CASE"
sSQL = sSQL + " WHEN (MONTH(DD.DTE_FACT)-1) = 6 THEN (CAST(DD.KMS_PARCOURUS as NUMERIC))"
sSQL = sSQL + " ELSE 0"
sSQL = sSQL + " END) AS KMS_PARCOURUS_06,"
sSQL = sSQL + " SUM(CASE"
sSQL = sSQL + " WHEN (MONTH(DD.DTE_FACT)-1) = 7 THEN (CAST(DD.KMS_PARCOURUS as NUMERIC))"
sSQL = sSQL + " ELSE 0"
sSQL = sSQL + " END) AS KMS_PARCOURUS_07,"
sSQL = sSQL + " SUM(CASE"
sSQL = sSQL + " WHEN (MONTH(DD.DTE_FACT)-1) = 8 THEN (CAST(DD.KMS_PARCOURUS as NUMERIC))"
sSQL = sSQL + " ELSE 0"
sSQL = sSQL + " END) AS KMS_PARCOURUS_08,"
sSQL = sSQL + " SUM(CASE"
sSQL = sSQL + " WHEN (MONTH(DD.DTE_FACT)-1) = 9 THEN (CAST(DD.KMS_PARCOURUS as NUMERIC))"
sSQL = sSQL + " ELSE 0"
sSQL = sSQL + " END) AS KMS_PARCOURUS_09,"
sSQL = sSQL + " SUM(CASE"
sSQL = sSQL + " WHEN (MONTH(DD.DTE_FACT)-1) = 10 THEN (CAST(DD.KMS_PARCOURUS as NUMERIC))"
sSQL = sSQL + " ELSE 0"
sSQL = sSQL + " END) AS KMS_PARCOURUS_10,"
sSQL = sSQL + " SUM(CASE"
sSQL = sSQL + " WHEN (MONTH(DD.DTE_FACT)-1) = 11 THEN (CAST(DD.KMS_PARCOURUS as NUMERIC))"
sSQL = sSQL + " ELSE 0"
sSQL = sSQL + " END) AS KMS_PARCOURUS_11,"
sSQL = sSQL + " SUM(CASE"
sSQL = sSQL + " WHEN (MONTH(DD.DTE_FACT)-1) = 12 THEN (CAST(DD.KMS_PARCOURUS as NUMERIC))"
sSQL = sSQL + " ELSE 0"
sSQL = sSQL + " END) AS KMS_PARCOURUS_12,"
sSQL = sSQL + " SUM(CAST(DD.KMS_FACT as NUMERIC)) AS KMS_FACT,"
sSQL = sSQL + " MAX(CAST(DD.PX_UNITAIRE as money)) AS PX_UNITAIRE,"
sSQL = sSQL + " SUM(CAST(DD.FACT_MONTANTNET as money)) AS FACT_MONTANTNET,"
sSQL = sSQL + " SUM(CAST(PP.KMINCLUS as NUMERIC)) AS KMINCLUS"
sSQL = sSQL + " FROM (SELECT AA.ID_FACT,"
sSQL = sSQL + " AA.DTE_FACT,"
sSQL = sSQL + " AA.DTEDEB,"
sSQL = sSQL + " AA.DTEFIN,"
sSQL = sSQL + " AA.ID_CLIENTFICHE,"
sSQL = sSQL + " AA.FACT_NUMPARC,"
sSQL = sSQL + " (CASE"
sSQL = sSQL + " WHEN (BB.PARC_ID IS NULL"
sSQL = sSQL + " AND AA.FACT_NUMPARC IS NULL) THEN 'PAS DE CONTRAT'"
sSQL = sSQL + " WHEN (BB.PARC_ID IS NULL"
sSQL = sSQL + " AND AA.FACT_NUMPARC IS NOT NULL) THEN AA.FACT_NUMPARC"
sSQL = sSQL + " WHEN (BB.PARC_ID IS NOT NULL) THEN BB.PARC_ID"
sSQL = sSQL + " Else: BB.PARC_ID"
sSQL = sSQL + " END) AS 'PARC_CONTRACTUEL',"
sSQL = sSQL + " BB.VERSION_CONTRAT,"
sSQL = sSQL + " AA.AAAAMM,"
sSQL = sSQL + " AA.TYPE_LIBELLEPROP,"
sSQL = sSQL + " AA.KMS_PARCOURUS,"
sSQL = sSQL + " AA.KMS_FACT,"
sSQL = sSQL + " AA.PX_UNITAIRE,"
sSQL = sSQL + " AA.FACT_MONTANTNET,"
sSQL = sSQL + " AA.TYPE_PRST,"
sSQL = sSQL + " AA.CODE_AGENCE,"
sSQL = sSQL + " AA.NO_CONTRAT,"
sSQL = sSQL + " AA.NUM_FACT"
sSQL = sSQL + " FROM (SELECT B.ID_FACT,"
sSQL = sSQL + " B.NUM_LIGNE,"
sSQL = sSQL + " H.DTE_FACT,"
sSQL = sSQL + " B.DTEDEB,"
sSQL = sSQL + " B.DTEFIN,"
sSQL = sSQL + " H.ID_CLIENTFICHE,"
sSQL = sSQL + " (CASE"
sSQL = sSQL + " WHEN B.FACT_NUMPARC IS NULL THEN (SELECT TOP 1 LL.FACT_PARCID"
sSQL = sSQL + " FROM S1SRVBDD02.IGLOO.DBO.FACT_LIGNE LL"
sSQL = sSQL + " WHERE LL.ID_FACT = B.ID_FACT"
sSQL = sSQL + " AND (LL.NUM_LIGNE < B.NUM_LIGNE)"
sSQL = sSQL + " AND TYPE_LIGNE = 'HE'"
sSQL = sSQL + " ORDER BY LL.NUM_LIGNE DESC)"
sSQL = sSQL + " Else: B.FACT_NUMPARC"
sSQL = sSQL + " END) AS 'FACT_NUMPARC',"
sSQL = sSQL + " H.AAAAMM,"
sSQL = sSQL + " B.TYPE_LIBELLEPROP,"
sSQL = sSQL + " (CASE"
sSQL = sSQL + " WHEN TYPE_LIBELLEPROP = 'lbl.ficheFactSiege.lblLigneTableau_RetourDepart' THEN FACT_QTE"
sSQL = sSQL + " Else '0'"
sSQL = sSQL + " END) AS 'KMS_Parcourus',"
sSQL = sSQL + " (CASE"
sSQL = sSQL + " WHEN TYPE_LIBELLEPROP = 'ref.typeprst.KM.aff' THEN FACT_QTE"
sSQL = sSQL + " Else '0'"
sSQL = sSQL + " END) AS 'KMS_FACT',"
sSQL = sSQL + " (COALESCE(B.PX_UNITAIRE,0)) AS 'PX_UNITAIRE',"
sSQL = sSQL + " (COALESCE(B.FACT_MONTANTNET,0)) AS 'FACT_MONTANTNET',"
sSQL = sSQL + " B.TYPE_PRST,"
sSQL = sSQL + " B.CODE_AGENCE,"
sSQL = sSQL + " (CASE"
sSQL = sSQL + " WHEN B.NO_CONTRAT IS NULL THEN (SELECT TOP 1 LL.NO_CONTRAT"
sSQL = sSQL + " FROM S1SRVBDD02.IGLOO.DBO.FACT_LIGNE LL"
sSQL = sSQL + " WHERE LL.ID_FACT = B.ID_FACT"
sSQL = sSQL + " AND (LL.NUM_LIGNE < B.NUM_LIGNE)"
sSQL = sSQL + " AND LL.CODE_TYPEPRST = 'LO')"
sSQL = sSQL + " Else: B.NO_CONTRAT"
sSQL = sSQL + " END) AS 'NO_CONTRAT',"
sSQL = sSQL + " H.NUM_FACT"
sSQL = sSQL + " FROM S1SRVBDD02.IGLOO.DBO.FACT_LIGNE B"
sSQL = sSQL + " LEFT JOIN S1SRVBDD02.IGLOO.DBO.FACT_HEADER H"
sSQL = sSQL + " ON H.ID_FACT = B.ID_FACT"
sSQL = sSQL + " WHERE B.ID_FACT IN (SELECT ID_FACT"
sSQL = sSQL + " FROM S1SRVBDD02.IGLOO.DBO.FACT_HEADER"
sSQL = sSQL + " WHERE CODE_TYPECNT = 'LD'"
sSQL = sSQL + " AND DTE_FACT BETWEEN '20100101' AND '20101231'"
sSQL = sSQL + " AND CODE_SOCPF = 'PFLO'"
sSQL = sSQL + " AND ID_CLIENTFICHE <> '5007798'"
sSQL = sSQL + " AND ID_CLIENTFICHE IN (SELECT SUBSTRING(NUM_CLIENT,3,9)"
sSQL = sSQL + " FROM S1SRVBDD02.Z_STATS.DBO.LISTEGRDSCPTES"
sSQL = sSQL + " WHERE GROUPE_PRINCIPAL LIKE '%BRAKE%'))"
sSQL = sSQL + " AND (TYPE_LIBELLEPROP = 'lbl.ficheFactSiege.lblLigneTableau_RetourDepart'"
sSQL = sSQL + " OR CODE_TYPEPRST = 'KM')"
sSQL = sSQL + " AND H.NUM_FACT IS NOT NULL"
sSQL = sSQL + " AND H.IS_COMPTA_CEGID = '1') AA"
sSQL = sSQL + " LEFT JOIN S1SRVBDD02.IGLOO.DBO.CONTRAT_VAR BB"
sSQL = sSQL + " ON AA.NO_CONTRAT = BB.NO_CONTRAT"
sSQL = sSQL + " AND (CASE"
sSQL = sSQL + " WHEN (SELECT TOP 1 VERSION_CONTRAT"
sSQL = sSQL + " FROM S1SRVBDD02.IGLOO.DBO.CONTRAT_VAR CCC"
sSQL = sSQL + " WHERE AA.NO_CONTRAT = CCC.NO_CONTRAT"
sSQL = sSQL + " AND AA.DTE_FACT BETWEEN CCC.DTE_DEBUT AND (COALESCE(CCC.DTE_FIN,'31/12/2099'))) IS NOT NULL THEN (SELECT TOP 1 VERSION_CONTRAT"
sSQL = sSQL + " FROM S1SRVBDD02.IGLOO.DBO.CONTRAT_VAR CCC"
sSQL = sSQL + " WHERE AA.NO_CONTRAT = CCC.NO_CONTRAT"
sSQL = sSQL + " AND AA.DTE_FACT BETWEEN CCC.DTE_DEBUT AND (COALESCE(CCC.DTE_FIN,'31/12/2099')))"
sSQL = sSQL + " ELSE (SELECT MAX(VERSION_CONTRAT)"
sSQL = sSQL + " FROM S1SRVBDD02.IGLOO.DBO.CONTRAT_VAR CCC"
sSQL = sSQL + " WHERE AA.NO_CONTRAT = CCC.NO_CONTRAT)"
sSQL = sSQL + " END) = BB.VERSION_CONTRAT) DD"
sSQL = sSQL + " "
sSQL = sSQL + " LEFT JOIN S1SRVBDD02.IGLOO.DBO.CNT_PRST PP"
sSQL = sSQL + " ON PP.NO_CONTRAT = DD.NO_CONTRAT"
sSQL = sSQL + " AND PP.VERSION_CONTRAT = DD.VERSION_CONTRAT"
sSQL = sSQL + " AND PP.CODE_TYPEPRST = 'KM'"
sSQL = sSQL + " LEFT JOIN S1SRVBDD02.IGLOO.DBO.CONTRAT CC"
sSQL = sSQL + " ON PP.NO_CONTRAT = CC.NO_CONTRAT"
sSQL = sSQL + " "
sSQL = sSQL + " GROUP BY DD.PARC_CONTRACTUEL, CC.CODE_TYPECNT"
sSQL = sSQL + " ORDER BY DD.PARC_CONTRACTUEL" |
Partager