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
| DECLARE @Date nvarchar(20) = '2012-11-06T00:00:00'
DECLARE @idLieu integer = 3986
SELECT TSE.Id1, TSE.2, TSEJ.DtD, TSR.Nom, TSS.Rg, TSR.CF, TSR.TMR, TSR.SMR, TSR.L , TSE.idLD,
(select Id
from
(SELECT TOP (1) E.Id1, E.IdLA
FROM TSLR as LR
LEFT OUTER JOIN TSS as S ON LR.LRId = S.LRId
LEFT OUTER JOIN TSEJ as EJ ON S.EJId = EJ.Id
LEFT OUTER JOIN TSE as E ON EJ.EId = E.EId
WHERE TSLR.LRId = L.LRId
AND cast(EJ.dtA as datetime) + cast(E.HA as datetime)
< cast(TS_EJ.dtD as datetime) + cast(TSE.HD as datetime)
ORDER BY EJ.dtA desc, E.HA desc ) as [Précédent]
where [Précédent].IdLieuArrivee = @idLieu ) as Précédent
FROM TSE
LEFT OUTER JOIN TSEJ ON TSE.EId = TSEJ.EId
LEFT OUTER JOIN TSS ON TSEJ.Id = TSS.EJId
LEFT OUTER JOIN TSLR ON TSS.LRId = TSLR.LRId
LEFT OUTER JOIN TSJR ON TSLR.JRId = TSJR.JRId
LEFT OUTER JOIN TSR ON TSJR.RId = TSR.RId
WHERE (TSE.Id1 in ('5555555', [...]) OR TSE.Id2 in ('892701', [...]))
AND (TSEJ.DtD = @Date)
AND (TSE.idLD = @idLieu or TSE.idLA = @idLieu)
ORDER BY Id1, TSS.R |
Partager