DECLARE @Date1 DATE DECLARE @Date2 DATE SET @Date1 = CONVERT(DATE, '24/01/1901') SET @Date2 = CONVERT(DATE, '24/04/2013') SELECT DISTINCT RO.num_ins AS NumInscrit ,P.nompers AS Nom ,Lower(P.prnmpers) AS Prenom ,Lower(CASE WHEN C.ind_statut_ctrat = - 1 THEN ' EN PRÉPA' WHEN C.ind_statut_ctrat = 0 THEN ' A ENVOYER' WHEN C.ind_statut_ctrat = 1 THEN ' ENVOYÉ' WHEN C.ind_statut_ctrat = 2 THEN ' REÇU' WHEN C.ind_statut_ctrat = 3 THEN ' A RETRAITER' WHEN C.ind_statut_ctrat = 4 THEN ' A VISER' WHEN C.ind_statut_ctrat = 5 THEN ' VALIDE' WHEN C.ind_statut_ctrat = 6 THEN ' ANNULÉ' WHEN C.ind_statut_ctrat = 7 THEN CASE WHEN C.id_cause_solde_ctrat = 1 THEN ' SOLDÉ ' ELSE ' RÉSILIÉ ' END ELSE '' END) AS StatutContrat ,Isnull(( SELECT TOP 1 SF2.n_statut FROM statut_fin SF2 WHERE SF2.num_ins = RO.num_ins AND n_statut NOT IN ( 'BL' ,'DB' ) ORDER BY SF2.dtrel_statut DESC ), 'AJ') AS StatutFinancier ,Isnull(( SELECT Sum(sssold) AS SOLDEADMIN FROM ( SELECT Sum(CASE ind_credit_lcpt WHEN 0 THEN - 1 * montant_ttc_lcpt ELSE montant_ttc_lcpt END) AS SSSOLD FROM ligne_comptable WHERE Isnull(ind_annule_lcpt, 0) <> 1 AND Isnull(ind_comptable_lcpt, 0) = 1 AND Datediff(d, Isnull(reg_date_encaissement_lcpt, date_creat_lcpt), Getdate()) >= 0 AND ( id_nat_don_cpt = 2 OR id_nat_don_cpt = 5 OR id_nat_don_cpt = 7 OR id_nat_don_cpt = 4 ) AND id_ctrat = C.id_ctrat UNION SELECT - 1 * Sum(montant_ttc_ech) AS SSSOLD FROM echeance ,echeancier WHERE echeancier.id_echier = echeance.id_echier AND echeancier.etat_echier = 1 AND echeancier.id_ctrat = C.id_ctrat AND Datediff(d, echeance.date_ech, Getdate()) >= 0 ) AS LIGNES_ADMIN ), 0) AS SoldeCourantNet ,Isnull(( SELECT Sum(CASE ind_credit_lcpt WHEN 0 THEN - 1 * montant_ttc_lcpt ELSE montant_ttc_lcpt END) AS SOLDECPTABLE FROM ligne_comptable WHERE Isnull(ind_annule_lcpt, 0) <> 1 AND Isnull(ind_comptable_lcpt, 0) = 1 AND Datediff(d, Isnull(reg_date_encaissement_lcpt, date_creat_lcpt), Getdate()) >= 0 AND id_ctrat = C.id_ctrat ), 0) AS SoldeComptable ,isnull(( SELECT ISNULL(SUM(TOTALRECOUVERTSTATUT.SOLDERECOUVERT), 0) FROM ( SELECT SF1.num_ins ,CASE SF1.a_statut WHEN 'AJ' THEN 0 ELSE ( SELECT Isnull(Sum(CASE WHEN Isnull(LC.ind_credit_lcpt, 0) = 1 THEN LC.montant_ttc_lcpt ELSE NULL END), 0) - Isnull(Sum(CASE WHEN Isnull(LC.ind_credit_lcpt, 0) = 0 THEN LC.montant_ttc_lcpt ELSE NULL END), 0) AS SOLDERECOUVERT FROM contrat LEFT JOIN ligne_comptable AS LC ON contrat.id_ctrat = LC.id_ctrat LEFT JOIN type_reglement ON LC.id_typregl = type_reglement.id_typregl LEFT JOIN nature_donnee_comptable ON LC.id_nat_don_cpt = nature_donnee_comptable.id_nat_don_cpt LEFT JOIN produit ON LC.code_prod = produit.code_prod LEFT JOIN ligne_comptable LCM ON LCM.id_lcpt = LC.lig_id_lcpt LEFT JOIN payeur ON LCM.id_pay = payeur.id_pay LEFT JOIN financeur ON financeur.numpers_fin = payeur.numpers LEFT JOIN type_reglement TRR ON LCM.id_typregl = TRR.id_typregl WHERE LC.ind_comptable_lcpt = 1 AND LC.id_typregl IS NOT NULL AND Datediff(d, Isnull(LC.reg_date_encaissement_lcpt, LC.date_creat_lcpt), Getdate()) >= 0 AND contrat.id_ctrat = C.id_ctrat AND CASE WHEN LC.id_nat_don_cpt = 2 THEN Isnull(LC.reg_date_encaissement_lcpt, LC.date_creat_lcpt) WHEN LC.id_nat_don_cpt = 5 THEN Isnull(LC.imp_date_impaye_lcpt, LC.date_creat_lcpt) ELSE LC.date_creat_lcpt END BETWEEN Isnull(( SELECT TOP 1 CONVERT(DATE, SF2.dtrel_statut) FROM statut_fin SF2 WHERE Datediff(d, SF2.dtrel_statut, SF1.dtrel_statut) > 0 AND SF2.dtrel_statut BETWEEN @Date1 AND @Date2 AND SF2.num_ins = SF1.num_ins AND SF2.n_statut NOT IN ( 'BL' ,'DB' ) ORDER BY dtrel_statut DESC ), @Date1) AND SF1.dtrel_statut ) END AS SOLDERECOUVERT FROM statut_fin SF1 INNER JOIN contrat C ON C.num_ins = SF1.num_ins WHERE SF1.n_statut NOT IN ( 'BL' ,'DB' ) AND SF1.dtrel_statut BETWEEN @Date1 AND @Date2 ) TOTALRECOUVERTSTATUT WHERE TOTALRECOUVERTSTATUT.num_ins = RO.num_ins GROUP BY num_ins ), 0) + CASE ISNULL(( SELECT TOP 1 SF.n_statut FROM statut_fin SF WHERE SF.num_ins = RO.num_ins AND SF.n_statut NOT IN ( 'BL' ,'DB' ) AND Datediff(d, CONVERT(DATE, SF.dtrel_statut), @Date2) >= 0 ORDER BY SF.dtrel_statut DESC ), 'AJ') WHEN 'AJ' THEN 0 ELSE ( SELECT Isnull(Sum(CASE WHEN Isnull(LC.ind_credit_lcpt, 0) = 1 THEN LC.montant_ttc_lcpt ELSE NULL END), 0) - Isnull(SUM(CASE WHEN Isnull(LC.ind_credit_lcpt, 0) = 0 THEN LC.montant_ttc_lcpt ELSE NULL END), 0) AS SOLDERECOUVERT FROM contrat LEFT JOIN ligne_comptable AS LC ON contrat.id_ctrat = LC.id_ctrat LEFT JOIN type_reglement ON LC.id_typregl = type_reglement.id_typregl LEFT JOIN nature_donnee_comptable ON LC.id_nat_don_cpt = nature_donnee_comptable.id_nat_don_cpt LEFT JOIN produit ON LC.code_prod = produit.code_prod LEFT JOIN ligne_comptable LCM ON LCM.id_lcpt = LC.lig_id_lcpt LEFT JOIN payeur ON LCM.id_pay = payeur.id_pay LEFT JOIN financeur ON financeur.numpers_fin = payeur.numpers LEFT JOIN type_reglement TRR ON LCM.id_typregl = TRR.id_typregl WHERE LC.ind_comptable_lcpt = 1 AND LC.id_typregl IS NOT NULL AND Datediff(d, Isnull(LC.reg_date_encaissement_lcpt, LC.date_creat_lcpt), Getdate()) >= 0 AND contrat.id_ctrat = ( SELECT id_ctrat FROM contrat C WHERE C.num_ins = RO.num_ins ) AND CONVERT(DATE, CASE WHEN LC.id_nat_don_cpt = 2 THEN Isnull(LC.reg_date_encaissement_lcpt, LC.date_creat_lcpt) WHEN LC.id_nat_don_cpt = 5 THEN Isnull(LC.imp_date_impaye_lcpt, LC.date_creat_lcpt) ELSE LC.date_creat_lcpt END, 103) BETWEEN Isnull(( SELECT TOP 1 CONVERT(DATE, SF.dtrel_statut) FROM statut_fin SF WHERE SF.num_ins = RO.num_ins AND SF.n_statut NOT IN ( 'BL' ,'DB' ) AND Datediff(d, CONVERT(DATE, SF.dtrel_statut), @Date2) >= 0 AND CONVERT(DATE, SF.dtrel_statut) BETWEEN @Date1 AND @Date2 ORDER BY SF.dtrel_statut DESC ), @Date1) AND @Date2 ) END AS SoldeRecouvert FROM rel_oprec RO INNER JOIN inscrit I ON RO.num_ins = I.num_ins INNER JOIN personne P ON P.numpers = I.numpers INNER JOIN contrat C ON C.num_ins = RO.num_ins WHERE RO.ind_act = 1 ORDER BY RO.num_ins DESC;