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
| SELECT IDCompteProlongation
,DateDebutPro
,IDCompte
,sumDuree
,DureeDebut
,DATE_ADD(DateDebutPro, INTERVAL sumDuree DAY_HOUR) AS DateFin
,DATE_ADD(DateDebutPro, INTERVAL DureeDebut DAY_HOUR) AS DateDebut
FROM (
SELECT IDCompte,IDCompteProlongation
,(SELECT DATE_ADD(DateCreation,INTERVAL Duree DAY_HOUR)
FROM Compte
WHERE IDCompte = idCompte ) AS DateDebutPro
,Duree
,(SELECT SUM(Duree)
FROM CompteProlongation pc2
WHERE pc2.DateProlongation <= pc.DateProlongation AND IDCompte = idCompte) AS sumDuree
,((SELECT SUM(Duree)
FROM CompteProlongation pc2
WHERE pc2.DateProlongation <= pc.DateProlongation AND IDCompte = idCompte) - (SELECT Duree
FROM CompteProlongation pc2
WHERE IDCompteProlongation IN(SELECT MAX(IDCompteProlongation)
FROM CompteProlongation pc2
GROUP BY IDCompte))) AS DureeDebut
FROM CompteProlongation pc ) AS T
WHERE IDCompte = idCompte; |
Partager