1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
| SELECT `description`,`substr(date, 1, 10)`, `substr(date, 12, 19)` FROM
(
SELECT T1.`description`,T1.`substr(date, 1, 10)`, T1.`substr(date, 12, 19)`,
CASE WHEN T2.`substr(date, 12, 19)` IS NULL THEN T1.`substr(date, 12, 19)` ELSE T2.`substr(date, 12, 19)` END AS `heure2`,
T1.`substr(date, 12, 19)` - (CASE WHEN T2.`substr(date, 12, 19)` IS NULL THEN T1.`substr(date, 12, 19)` ELSE T2.`substr(date, 12, 19)` END ) AS `delta`
FROM
(
SELECT `description`,`substr(date, 1, 10)`, `substr(date, 12, 19)`, @rank:=@rank+1 as rang
FROM `tbl_excel`
inner join (SELECT @rank:=0) RANK
) T1
LEFT JOIN
(
SELECT `description`,`substr(date, 1, 10)`, `substr(date, 12, 19)`, @rank2:=@rank2+1 as rang
FROM `tbl_excel`
inner join (SELECT @rank2:=0) RANK
) T2
ON T1.description = T2.description AND T1.`substr(date, 1, 10)` = T2.`substr(date, 1, 10)` AND T1.`rang` = T2.`rang`+1
) AS RESULTAT
WHERE `delta` NOT BETWEEN 1 AND 18 |
Partager