[SQL Express] estrazione record che hanno subito una variazione

[softecparma]

Salve a tutti!
Non sapevo bene che titolo dare alla discussione, spero di essere pi� chiaro qui sotto ...

ho una tabella con questi record:

Batch LocalTimeCol StateCurrent
719664 2017-02-17 13:10:50 18
719664 2017-02-17 13:11:00 0
719664 2017-02-17 13:11:10 6
719664 2017-02-17 13:11:20 6
719664 2017-02-17 13:11:30 6
719664 2017-02-17 13:11:40 6
719664 2017-02-17 13:11:50 6
719664 2017-02-17 13:12:00 6
719664 2017-02-17 13:12:10 18
719664 2017-02-17 13:12:20 6
719664 2017-02-17 13:12:30 6

Avrei la necessit� di ottenere questo risultato:

Batch Dalle Alle StateCurrent
719664 2017-02-17 13:10:50 2017-02-17 13:11:00 18
719664 2017-02-17 13:11:00 2017-02-17 13:11:10 0
719664 2017-02-17 13:11:10 2017-02-17 13:12:10 6
719664 2017-02-17 13:12:10 2017-02-17 13:12:20 18
719664 2017-02-17 13:12:20 2017-02-17 13:12:30 6

Ho fatto diverse prove con il Group by e datediff ma non ho "cavato un ragno dal buco". Qualcuno pu� aiutarmi?
Grazie
Saluti
Gianluca

[marino51]

.

[softecparma]

.

Ciao e grazie per l'interessamento!
La tua query non ottiene il risultato desiderato. A me serve che restituisca un record unico per gli StateCurrent consecutivamente uguali:

esempio:
StateCurrent
6
6
6
8
8
18
6

devo ottenere
6
8
18
6

con a fianco il valore "Dalle" "Alle" che saranno il primo e l'ultimo valore degli StateCurrent uguali consecutivi.

Spero di essermi spiegato bene...
Buona giornata

[badaze]

Quello che chiedi mi pare difficile da fare con una sola query. Se ci riesci postala che imparero' qualcosa.

Non so che cosa aveva postato marino51 ma ti propongo questo (fatto con MySQL ma non avendo SQL express...) che puo' darti uno spunto. Poi con un linguaggio di programmazione sarà facile fare quello che vuoi.

SELECT Batch, Dalle, Alle, StateCurrent, @rank3:=@rank3+1
from
(SELECT a1.`Batch`, a1.`LocalTimeCol` as Dalle, a2.`LocalTimeCol` as Alle, a1.`StateCurrent` 
FROM
(SELECT `Batch`,`LocalTimeCol`,`StateCurrent`, @rank:=@rank+1 as rank  FROM `batchd`
inner join (SELECT @rank:=0) t2
order by `Batch`,`LocalTimeCol`) a1
LEFT JOIN
(SELECT `Batch`,`LocalTimeCol`,`StateCurrent`, @rank2:=@rank2+1 as rank  FROM `batchd`
inner join (SELECT @rank2:=-1) t3
order by `Batch`,`LocalTimeCol`) a2
ON a1.`Batch` = a2.`Batch` and a1.`rank` = a2.`rank`
where a2.`LocalTimeCol` is not null) r0
inner join (SELECT @rank3:=0) r1

Guarda immagine.

170224-002.JPG

[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

[marino51]

CatturaX.jpg
questo è il risultato dell query (chiamiamola così...)

[softecparma]

Sembra funzionare! Fantastico!
Cercher� di studiarmi cosa fa esattamente la query
Grazie mille!

[marino51]

Sembra funzionare! Fantastico!
Cercher� di studiarmi cosa fa esattamente la query
Grazie mille!

non sapevo se avevi gi� risolto, questa � una versione rivista e corretta che lavora meglio,

WITH t1 AS
(
  select Batch, LocalTimeCol, StateCurrent,
  ROW_NUMBER() OVER (ORDER BY Batch, LocalTimeCol) AS myRank
  from tabella
),
t2 as
(
  select Batch, LocalTimeCol, StateCurrent, myRank,  TakeIt
  from
  (
    select t1.Batch, t1.LocalTimeCol, t1.StateCurrent, t1.myRank,
    CASE WHEN t1a.StateCurrent = t1.StateCurrent THEN 0 ELSE 1 END as TakeIt
    FROM t1
    FULL OUTER JOIN t1 as t1a ON t1.myRank = t1a.myRank + 1
  ) t3
  where StateCurrent is not null 
  and   ( TakeIt = 1 or myRank = ( select max(myRank) from t1 ) )
)
select 
  t2.Batch,
  t2.LocalTimeCol as Dalle,
  min(t2a.LocalTimeCol) as Alle,
  t2.StateCurrent
from t2
inner join t2 as t2a ON t2.Batch=t2a.Batch and t2a.myRank > t2.myRank
group by t2.Batch, t2.LocalTimeCol, t2.StateCurrent
order by t2.Batch, t2.LocalTimeCol, t2.StateCurrent 

risultato,
Batch Dalle Alle StateCurrent
719664 2017-02-17 13:10:50.000 2017-02-17 13:11:00.000 18
719664 2017-02-17 13:11:00.000 2017-02-17 13:11:10.000 0
719664 2017-02-17 13:11:10.000 2017-02-17 13:12:10.000 6
719664 2017-02-17 13:12:10.000 2017-02-17 13:12:20.000 18
719664 2017-02-17 13:12:20.000 2017-02-17 13:12:30.000 6

il funzionamento � relativamente semplice, delle 4 select lavorano nell'ordine,
la prima seleziona i record aggiungendo il rank ovvero numerandoli progressivamente
la terza identifica, dal set precedente, i record da utilizzare con la colonna TakeIt
la seconda prende i record "TakeIt" + l'ultimo
la quarta associa la data "Alle" usando il rank opportunamente

ps forse non sono necessari i confronti sulla colonna batch, perch�
probabilmente batch diversi possono contenere comunque LocalTimeCol successive

[softecparma]

Grazie mille ancora!