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 tmp as (
select a.id_client, b.vpc_trt_statut, row_number() over(partition by a.vpc_id order by b.VPC_DATE_VENTE desc) as rnk
from T_VPC a
inner join T_VPC_TRT b on a.id_vpc = b.idvpc
where b.VPC_DATE_VENTE < '01/05/10')
SELECT T_CLIENT.ID_CLIENT, T_CLIENT.NOM, T_CLIENT.PRENOM, T_CLIENT.DATE_NAISSANCE, CRD.CRDDATEATTRIBUTION,
(
CASE
WHEN T_VPC_TRT.VPC_TRT_STATUT = 1 then 'EN BASE'
WHEN T_VPC_TRT.VPC_TRT_STATUT = 2 then 'ENVOYE'
WHEN T_VPC_TRT.VPC_TRT_STATUT = 3 then 'ACQUITTE'
WHEN T_VPC_TRT.VPC_TRT_STATUT = 4 then 'EN ERREUR'
WHEN T_VPC_TRT.VPC_TRT_STATUT = 5 then 'ANNULE'
WHEN T_VPC_TRT.VPC_TRT_STATUT = 6 then 'ANNULE ET REMBOURSE'
WHEN T_VPC_TRT.VPC_TRT_STATUT = 8 then 'ANNULE PR COPIE CARTE'
ELSE 'AUCUNE'
END
) AS VPC_STATUT
FROM T_CLIENT
INNER JOIN CRD ON T_CLIENT.ID_CLIENT = CRD.CRDCUSTID
left outer join tmp on tmp.id_client = T_CLIENT.id_client and tmp.rnk = 1
WHERE T_CLIENT.CUSTNUMSCOL IS NOT NULL
AND CRD.CRDSTATUS NOT IN (3, 4, 5)
AND CRD.CRDDATEATTRIBUTION < '01/05/10'
ORDER BY NOM, PRENOM ; |
Partager