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
| SELECT LOWER(Edemande.nomemploye) AS nomEmployeDemande,
LOWER(Edemande.prenomemploye) AS prenomEmployeDemande,
DemandeAchat.nomArticleDemande AS nomArticleDemande,
DemandeAchat.justificationDemandeAchat AS justificationDemandeAchat,
LOWER(Esoccupe.nomemploye) AS nomEmployeSoccupe,
LOWER(Esoccupe.prenomemploye) AS prenomEmployeSoccupe,
TO_CHAR(Demande_EtatAchatDebut.dateEtatAchat,'DD/MM/YYYY à HH24:MI:SS') dateEtatDebut,
TO_CHAR(DateEtatActuelle.dateActuelle,'DD/MM/YYYY à HH24:MI:SS') dateActuelle,
DateEtatActuelle.etatActuel AS etatActuel
FROM Employe Edemande INNER JOIN DemandeAchat ON Edemande.idEmploye = DemandeAchat.idEmployeDemande
LEFT OUTER JOIN Employe Esoccupe ON Esoccupe.idEmploye = DemandeAchat.idEmployeSoccupe
INNER JOIN Demande_EtatAchat Demande_EtatAchatDebut ON Demande_EtatAchatDebut.idDemandeAchat = DemandeAchat.idDemandeAchat AND Demande_EtatAchatDebut.idEtatDemandeAchat = 1
INNER JOIN
(SELECT * FROM (SELECT Demande_EtatAchat.idDemandeAchat AS idDemandeAchat,
MAX(dateEtatAchat) AS dateActuelle,
libelleEtatDemandeAchat AS etatActuel
FROM DemandeAchat, Demande_EtatAchat, EtatDemandeAchat
WHERE Demande_EtatAchat.idDemandeAchat = DemandeAchat.idDemandeAchat
AND EtatDemandeAchat.idEtatDemandeAchat=Demande_EtatAchat.idEtatDemandeAchat
GROUP BY Demande_EtatAchat.idDemandeAchat,
libelleEtatDemandeAchat) AS T0
WHERE (idDemandeAchat, dateActuelle, etatActuel) NOT IN (
SELECT T1.idDemandeAchat AS idDemandeAchat, T1.dateActuelle AS dateActuelle, T1.etatActuel AS etatActuel FROM (SELECT Demande_EtatAchat.idDemandeAchat AS idDemandeAchat,
MAX(dateEtatAchat) AS dateActuelle,
libelleEtatDemandeAchat AS etatActuel
FROM DemandeAchat, Demande_EtatAchat, EtatDemandeAchat
WHERE Demande_EtatAchat.idDemandeAchat = DemandeAchat.idDemandeAchat
AND EtatDemandeAchat.idEtatDemandeAchat=Demande_EtatAchat.idEtatDemandeAchat
GROUP BY Demande_EtatAchat.idDemandeAchat,
libelleEtatDemandeAchat) AS T1, (SELECT Demande_EtatAchat.idDemandeAchat AS idDemandeAchat,
MAX(dateEtatAchat) AS dateActuelle,
libelleEtatDemandeAchat AS etatActuel
FROM DemandeAchat, Demande_EtatAchat, EtatDemandeAchat
WHERE Demande_EtatAchat.idDemandeAchat = DemandeAchat.idDemandeAchat
AND EtatDemandeAchat.idEtatDemandeAchat=Demande_EtatAchat.idEtatDemandeAchat
GROUP BY Demande_EtatAchat.idDemandeAchat,
libelleEtatDemandeAchat) AS T2
WHERE T1.idDemandeAchat = T2.idDemandeAchat AND T1.dateActuelle < T2.dateActuelle)) AS DateEtatActuelle
ON DateEtatActuelle.idDemandeAchat = DemandeAchat.idDemandeAchat |
Partager