1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23
|
with dernier (UPDATETIME, ID)
as
(
select max(UPDATETIME), id
from Alexman59
where cast(UPDATETIME as date) = cast(getdate() as date)
group by ID
),
avantdernier (UPDATETIME, ID)
as
(
select max(Alexman59.UPDATETIME), Alexman59.id
from Alexman59
inner join dernier on dernier.UPDATETIME > Alexman59.UPDATETIME and dernier.ID = Alexman59.ID
group by Alexman59.ID
)
select a1.UPDATETIME, a1.ID, a1.ID
from Alexman59 a1
inner join dernier on dernier.UPDATETIME = a1.UPDATETIME and dernier.ID = a1.ID
left outer join avantdernier on avantdernier.ID = dernier.ID
left outer join Alexman59 a2 on a2.UPDATETIME = avantdernier.UPDATETIME and a2.ID = avantdernier.ID
where a2.[STATUS] <> a1.[STATUS] or a2.[STATUS] is null; |
Partager