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
|
GO
SELECT DISTINCT
'''' + CONVERT(VARCHAR(40),ct.ContactId)+''',' AS IdContact,
crt.ContractId,
crt.jfa_Referencecontrat AS [Ref. Contrat],
brt.gs2e_adrtechnique AS [Adresse technique],
ct.FullName,
CASE
WHEN crt.jfa_StatutSAPHIR = 1 THEN 'Contrat actif'
WHEN crt.jfa_StatutSAPHIR = 7 THEN 'Contrat à résilier'
WHEN crt.jfa_StatutSAPHIR = 4 THEN 'Contrat résilié'
END AS [Statut contrat],
CASE
WHEN it.gs2e_Typeinterventionsgo = 1 THEN 'Activité de repose'
WHEN it.gs2e_Typeinterventionsgo = 10 THEN 'Activité de coupure'
WHEN it.gs2e_Typeinterventionsgo = 11 THEN 'Activité de rétablissement'
WHEN it.gs2e_Typeinterventionsgo = 16 THEN 'Activité de dépose'
END AS [Type activité],
CASE
WHEN sca.jfa_StatutSAPHIR = 1 THEN 'A planifier'
WHEN sca.jfa_StatutSAPHIR = 3 THEN 'Pour saisie du CRI'
WHEN sca.jfa_StatutSAPHIR = 5 THEN 'Réalisé'
END AS [Statut activité],
lot.gs2e_name AS [Nom du lot],
FORMAT(lot.CreatedOn,'dd/mm/yyyy') AS [Date création lot],
'''' +CONVERT(VARCHAR(40),sca.ActivityId)+''',' AS IdActivité
FROM Contact ct WITH (NOLOCK)
INNER JOIN ContractBase crt WITH (NOLOCK) ON crt.CustomerId = ct.ContactId |
Partager