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
| SELECT
IDMaintenance,
IDService,
IDUtilisateur,
IDClient,
Date,
HeureDeb,
ARappelerIDService,
ARappelerIDUtilisateur,
Client,
AgenceCommerciale,
Interlocuteur,
DECODE(Client.Activite,'SAP','Service à la personne','INT','Intérim','RH','Ressources humaines','AUT','Autres','') AS Activite,
Utilisateur,
Service,
ARappeler,
AppelObjet,
Probleme,
Solution
FROM (SELECT
Appel.IDAppel AS IDMaintenance,
Appel.IDService AS IDService,
Appel.IDUtilisateur AS IDUtilisateur,
Appel.IDClient AS IDClient,
Appel.Date AS Date,
Appel.HeureDeb AS HeureDeb,
Appel.ARappelerIDService AS ARappelerIDService,
Appel.ARappelerIDUtilisateur AS ARappelerIDUtilisateur,
Client.RaisonSociale AS Client,
AgenceCommerciale.RaisonSociale AS AgenceCommerciale,
Appel.Interlocuteur AS Interlocuteur,
DECODE(Client.Activite,'SAP','Service à la personne','INT','Intérim','RH','Ressources humaines','AUT','Autres','') AS Activite ,
Utilisateur.NomPrenom AS Utilisateur,
Service.Designation AS Service,
Appel.ARappeler AS ARappeler,
AppelObjet.Designation AS AppelObjet,
AppelDetail.Probleme AS Probleme,
AppelDetail.Solution AS Solution
FROM
Appel
Left OUTER JOIN Client on (Appel.IDClient = Client.IDClient)
Left outer join DepartementActivite on (Client.NumDepartement = DepartementActivite.NumDepartement AND Client.Activite = DepartementActivite.Activite)
Left outer join AgenceCommerciale on (DepartementActivite.IDAgenceCommerciale = AgenceCommerciale.IDAgenceCommerciale)
LEFT JOIN Salarie ON Salarie.IDUtilisateur = Appel.IDUtilisateur, Utilisateur LEFT JOIN Salarie ON Salarie.IDUtilisateur = Utilisateur.IDUtilisateur
Left OUTER JOIN service on (Appel.IDService = Service.IDService)
LEFT JOIN AppelDetail ON Appel.IDAppel = AppelDetail.IDAppel
Left JOIN AppelObjet on AppelDetail.IDAppelObjet = AppelObjet.IDAppelObjet
WHERE
Appel.IDAppel <> 0 UNION SELECT
Mail.IDMail AS IDMaintenance,
Mail.S_IDService AS IDService,
Mail.S_IDUtilisateur AS IDUtilisateur,
Mail.E_IDClient AS IDClient,
Mail.E_Date AS Date,
Mail.E_Heure AS HeureDeb,
Mail.ARappelerIDService AS ARappelerIDService,
Mail.ARappelerIDUtilisateur AS ARappelerIDUtilisateur,
Client.RaisonSociale AS Client,
AgenceCommerciale.RaisonSociale AS AgenceCommerciale,
Mail.E_Interlocuteur AS Interlocuteur,
DECODE(Client.Activite,'SAP','Service à la personne','INT','Intérim','RH','Ressources humaines','AUT','Autres','') AS Activite ,
Utilisateur.NomPrenom AS Utilisateur,
Service.Designation AS Service,
Mail.ARappeler AS ARappeler,
AppelObjet.Designation AS AppelObjet,
MailDetail.Probleme AS Probleme,
MailDetail.Solution AS Solution
FROM
Mail
Left OUTER JOIN Client on (Mail.E_IDClient = Client.IDClient)
Left outer join DepartementActivite on (Client.NumDepartement = DepartementActivite.NumDepartement AND Client.Activite = DepartementActivite.Activite)
Left outer join AgenceCommerciale on (DepartementActivite.IDAgenceCommerciale = AgenceCommerciale.IDAgenceCommerciale)
Left JOIN Salarie on Salarie.IDUtilisateur = Mail.S_IDUtilisateur, Utilisateur Left JOIN Salarie on Salarie.IDUtilisateur = Utilisateur.IDUtilisateur
Left OUTER JOIN Service on (Mail.S_IDService = Service.IDService)
Left JOIN MailDetail on Mail.IDMail = MailDetail.IDMail
Left JOIN AppelObjet on MailDetail.IDAppelObjet = AppelObjet.IDAppelObjet
WHERE
Mail.IDMail <> 0
) Maintenance
Where Date BETWEEN '20110101' AND '20111231' |
Partager