[SQL Express] estrazione record che hanno subito una variazione

[marino51]

@Badaze, non sono riuscito a convertire la tua "query" per farla funzionare su ms sql 2005 (abbiamo questo ...)

ho cercato una via diversa e mi sembra di averla trovata,

WITH t1 AS
(
  select Batch, LocalTimeCol, StateCurrent,
  ROW_NUMBER() OVER (ORDER BY LocalTimeCol) AS myRank
  from tabella
),
t4 as
(
  select 
    Batch,
    LocalTimeCol,
    StateCurrent,
    myRank
  from
  (
    select t1.Batch, t1.LocalTimeCol, t1.StateCurrent, t1.myRank,
    CASE WHEN t2.StateCurrent = t1.StateCurrent THEN 0 ELSE 1 END as TakeItX
    FROM t1
    INNER JOIN t1 as t2 ON t1.myRank = t2.myRank + 1
    UNION
    select TOP 1 Batch, LocalTimeCol, StateCurrent, 1, 1
    from t1
  ) t3
  where TakeItX > 0
)
select 
  t4.Batch,
  t4.LocalTimeCol as Dalle,
  min(t5.LocalTimeCol) as Alle,
  t4.StateCurrent
from t4
inner join t4 as t5 ON t4.Batch=t5.Batch and t5.myRank > t4.myRank
group by t4.Batch, t4.LocalTimeCol, t4.StateCurrent 

se riesco allego anche l'mmagine del risultato