SQL Server - Seleccionar primer y ultimo registro de grupos

Pos: 98

Val: 7

Seleccionar primer y ultimo registro de grupos

Publicado por Alan (4 intervenciones) el 12/03/2019 15:27:21

Tengo un log con la siguiente estructura y va actualizándose cada día.

FechaHora Estado
12/03/2019 02:29 Parado
13/03/2019 02:29 Parado
14/03/2019 02:29 Parado
15/03/2019 02:29 Parado
16/03/2019 02:29 Parado
17/03/2019 02:29 Parado
18/03/2019 02:29 Operando
19/03/2019 02:29 Operando
20/03/2019 02:29 Operando
21/03/2019 02:29 Operando
22/03/2019 02:29 Operando
23/03/2019 02:29 Operando
24/03/2019 02:29 Operando
25/03/2019 02:29 Operando
26/03/2019 02:29 Operando
27/03/2019 02:29 Parado
28/03/2019 02:29 Parado
29/03/2019 02:29 Parado
30/03/2019 02:29 Parado
31/03/2019 02:29 Parado
01/04/2019 02:29 Parado
02/04/2019 02:29 Parado
03/04/2019 02:29 Operando
04/04/2019 02:29 Operando
05/04/2019 02:29 Operando
Quiero obtener la hora de inicio y fin del estado "parado" de toda la columna tal y como se ve en la siguiente tabla.

HoraInicio HoraFin Estado
12/03/2019 02:29 17/03/2019 02:29 Parado
27/03/2019 02:29 02/04/2019 02:29 Parado

Valora esta pregunta

Me gusta: Está pregunta es útil y esta clara

No me gusta: Está pregunta no esta clara o no es útil

Responder

Pos: 1

Val: 3.250

Seleccionar primer y ultimo registro de grupos

Publicado por Isaias (4558 intervenciones) el 12/03/2019 18:47:19

¿Es su motor SQL SERVER?, ¿Que versión?

Valora esta respuesta

Me gusta: Está respuesta es útil y esta clara

No me gusta: Está respuesta no esta clara o no es útil

Comentar

Pos: 98

Val: 7

Seleccionar primer y ultimo registro de grupos

Publicado por Alan (4 intervenciones) el 12/03/2019 19:00:55

Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1

Valora esta respuesta

Comentar

Pos: 1

Val: 3.250

Seleccionar primer y ultimo registro de grupos

Publicado por Isaias (4558 intervenciones) el 12/03/2019 19:41:29

Opssss, ya es viejo el motor y no hay muchas funciones como en 2014 0 2016

Valora esta respuesta

Comentar

Pos: 6

Val: 102

Seleccionar primer y ultimo registro de grupos

Publicado por Vega (43 intervenciones) el 13/03/2019 13:59:34

Hola Alan,
A ver si marcha esto

SET DATEFORMAT DMY

IF EXISTS (SELECT OBJECT_ID('tempdb..#Estado'))

	DROP TABLE #Estado

GO

SELECT	*

INTO	#Estado

FROM (

	SELECT FechaHora = CAST('12/03/2019 02:29' AS DATETIME) , Estado = 'Parado'		UNION ALL

	SELECT CAST('13/03/2019 02:29' AS DATETIME),	'Parado'						UNION ALL

	SELECT CAST('14/03/2019 02:29' AS DATETIME),	'Parado'						UNION ALL

	SELECT CAST('15/03/2019 02:29' AS DATETIME),	'Parado'						UNION ALL

	SELECT CAST('16/03/2019 02:29' AS DATETIME),	'Parado'						UNION ALL

	SELECT CAST('17/03/2019 02:29' AS DATETIME),	'Parado'						UNION ALL

	SELECT CAST('18/03/2019 02:29' AS DATETIME),	'Operando'						UNION ALL

	SELECT CAST('19/03/2019 02:29' AS DATETIME),	'Operando'						UNION ALL

	SELECT CAST('20/03/2019 02:29' AS DATETIME),	'Operando'						UNION ALL

	SELECT CAST('21/03/2019 02:29' AS DATETIME),	'Operando'						UNION ALL

	SELECT CAST('22/03/2019 02:29' AS DATETIME),	'Operando'						UNION ALL

	SELECT CAST('23/03/2019 02:29' AS DATETIME),	'Operando'						UNION ALL

	SELECT CAST('24/03/2019 02:29' AS DATETIME),	'Operando'						UNION ALL

	SELECT CAST('25/03/2019 02:29' AS DATETIME),	'Operando'						UNION ALL

	SELECT CAST('26/03/2019 02:29' AS DATETIME),	'Operando'						UNION ALL

	SELECT CAST('27/03/2019 02:29' AS DATETIME),	'Parado'						UNION ALL

	SELECT CAST('28/03/2019 02:29' AS DATETIME),	'Parado'						UNION ALL

	SELECT CAST('29/03/2019 02:29' AS DATETIME),	'Parado'						UNION ALL

	SELECT CAST('30/03/2019 02:29' AS DATETIME),	'Parado'						UNION ALL

	SELECT CAST('31/03/2019 02:29' AS DATETIME),	'Parado'						UNION ALL

	SELECT CAST('01/04/2019 02:29' AS DATETIME),	'Parado'						UNION ALL

	SELECT CAST('02/04/2019 02:29' AS DATETIME),	'Parado'						UNION ALL

	SELECT CAST('03/04/2019 02:29' AS DATETIME),	'Operando'						UNION ALL

	SELECT CAST('04/04/2019 02:29' AS DATETIME),	'Operando'						UNION ALL

	SELECT CAST('05/04/2019 02:29' AS DATETIME),	'Operando'

) Datos

create clustered index I_CL_Estado on #Estado (FechaHora asc)

;WITH EstadoSecuenciado AS (

	SELECT	*

	,		Secuenciador = row_number() over (order by FechaHora asc)

	FROM	#Estado i

),

Continuidad AS (

SELECT		i_fechahora		= i.FechaHora

,			f_fechahora		= ISNULL(f.FechaHora, i.FechaHora)

,			i_estado		= i.Estado

,			f_estado		= ISNULL(f.Estado, I.Estado)

,			i_secuenciador	= i.secuenciador

,			f_secuenciador	= f.secuenciador

,			HayContinuidad	= case when datediff(minute, f.fechahora , i.fechahora) < 0 and f.Estado = i.Estado then  1 else 0 end

FROM		EstadoSecuenciado I

LEFT JOIN	EstadoSecuenciado F on i.secuenciador = F.secuenciador -1

,Rec AS (

	SELECT	i_fechahora

	,		f_fechahora = CASE WHEN i_estado<> f_estado THEN i_fechahora ELSE f_fechahora END

	,		i_estado

	,		f_estado

	,		i_secuenciador

	,		f_secuenciador

	FROM	Continuidad

	WHERE	HayContinuidad = 0

	UNION ALL

	SELECT		c.i_fechahora

	,			r.f_fechahora

	,			r.i_estado

	,			r.f_estado

	,			r.i_secuenciador

	,			r.f_secuenciador

	FROM		Continuidad C

	JOIN		Rec R on c.f_fechahora = R.i_fechahora

			AND c.i_estado = R.i_estado

			AND c.HayContinuidad = 1

SELECT		DISTINCT

			HoraInicio	= MIN(i_fechahora) OVER(PARTITION BY i_secuenciador, f_secuenciador ORDER BY i_fechahora ASC)

,			HoraFin		= MAX(f_fechahora) OVER(PARTITION BY i_secuenciador, f_secuenciador ORDER BY f_fechahora ASC)

,			Estado		= i_estado

FROM		Rec

WHERE		i_estado	= 'Parado'

ORDER BY	1 ASC

Valora esta respuesta

Comentar

Pos: 1

Val: 3.250

Seleccionar primer y ultimo registro de grupos

Publicado por Isaias (4558 intervenciones) el 15/03/2019 17:23:33

¿Hizo la prueba en 2008?

Valora esta respuesta

Comentar

Pos: 8

Val: 86

Seleccionar primer y ultimo registro de grupos

Publicado por Rafael (110 intervenciones) el 13/03/2019 15:15:37

Hola Alan:

Como bien te comenta Isaias, tu motor es anticuado pero no por eso es imposible de hacerse...

Una de las cosas que tienes que hacer es saber que valor tiene el registro previo para conocer su estado... luego es un poco de coser y cantar...

¿como podemos saber que valor tiene el registro anterior?
En versiones superirores a la 2012 existe la funcion LAG que te indica eso el valor de una columna para el registro anterior. Pero entonces tendremos que simularla...

Primero como solo tienes DOS columnas y no una llave primaria hay que generar un apuntador...
algo como esto:

select Fecha_Hora

           , Estado

           , ROW_NUMBER() OVER (Order BY Fecha_Hora) Posicion

      from   temporal

Esto debe hacer un join asi mismo pero viendo el registro previo...
Usare la clausula WITH para hacerlo ...

WITH

TMP_ORD (Fecha, Estado, Posicion)

AS   (select Fecha_Hora

           , Estado

           , ROW_NUMBER() OVER (Order BY Fecha_Hora) Posicion

      from   temporal)

Y luego el query

select *

        from   tmp_ord a

               left join tmp_ord b

                      on a.Posicion = b.Posicion - 1

Con esto puedes ver que veo el registro actual y el registro siguiente en la misma linea ....
Bueno con eso y un chocolate....

Necesitamos crear un agrupador cada que el estado actual sea distinto al estado siguiente ...
Obtener la fecha MINIMA y MAXIMA cuando los estados sean IGUALES tal que asi ...

WITH

TMP_ORD (Fecha, Estado, Posicion)

AS   (select Fecha_Hora

           , Estado

           , ROW_NUMBER() OVER (Order BY Fecha_Hora) Posicion

      from   temporal)

select FORMAT(min(fecha), 'dd/MM/yyyy hh:mm' ) fecha_inicio

     , FORMAT(max(sig_fecha), 'dd/MM/yyyy hh:mm' ) fecha_termino

     , estado

from   (

        select a.fecha fecha

             , b.fecha sig_fecha

             , a.estado estado

             , b.estado sig_estado

             , sum(case when a.estado!=b.estado

                        then 1 else 0

                   end) over (partition by a.estado order by a.posicion) grp

        from   tmp_ord a

               left join tmp_ord b

                      on a.Posicion = b.Posicion - 1

       ) SQ

where  estado = sig_estado

and    estado = 'Parado'

group  by grp, estado

Aqui el ejemplo de que funciona http://sqlfiddle.com/#!18/01b79/107
Estoy casi al 100% seguro que todas las sentencias usadas son validas en SQL Server 2008 pero si no es asi ... dime que error te marca y le buscamos solución.

Espero te sirva
COn muchos datos tardara mas si no tiene indices....

Valora esta respuesta

Comentar

Pos: 98

Val: 7

Seleccionar primer y ultimo registro de grupos

Publicado por Alan (4 intervenciones) el 13/03/2019 15:49:24

Gracias por tu respuesta y ayuda. Al momento de ejecutar el script me aparecen los siguientes errores que imagino son por la version de la BD.

create table temporal (Fecha_Hora datetime, Estado varchar(10))

insert into temporal values (convert(datetime, '2019-03-12 02:29', 120), 'Parado'  );

insert into temporal values (convert(datetime, '2019-03-13 02:29', 120), 'Parado'  );

insert into temporal values (convert(datetime, '2019-03-14 02:29', 120), 'Parado'  );

insert into temporal values (convert(datetime, '2019-03-15 02:29', 120), 'Parado'  );

insert into temporal values (convert(datetime, '2019-03-16 02:29', 120), 'Parado'  );

insert into temporal values (convert(datetime, '2019-03-17 02:29', 120), 'Parado'  );

insert into temporal values (convert(datetime, '2019-03-18 02:29', 120), 'Operando');

insert into temporal values (convert(datetime, '2019-03-19 02:29', 120), 'Operando');

insert into temporal values (convert(datetime, '2019-03-20 02:29', 120), 'Operando');

insert into temporal values (convert(datetime, '2019-03-21 02:29', 120), 'Operando');

insert into temporal values (convert(datetime, '2019-03-22 02:29', 120), 'Operando');

insert into temporal values (convert(datetime, '2019-03-23 02:29', 120), 'Operando');

insert into temporal values (convert(datetime, '2019-03-24 02:29', 120), 'Operando');

insert into temporal values (convert(datetime, '2019-03-25 02:29', 120), 'Operando');

insert into temporal values (convert(datetime, '2019-03-26 02:29', 120), 'Operando');

insert into temporal values (convert(datetime, '2019-03-27 02:29', 120), 'Parado'  );

insert into temporal values (convert(datetime, '2019-03-28 02:29', 120), 'Parado'  );

insert into temporal values (convert(datetime, '2019-03-29 02:29', 120), 'Parado'  );

insert into temporal values (convert(datetime, '2019-03-30 02:29', 120), 'Parado'  );

insert into temporal values (convert(datetime, '2019-03-31 02:29', 120), 'Parado'  );

insert into temporal values (convert(datetime, '2019-04-01 02:29', 120), 'Parado'  );

insert into temporal values (convert(datetime, '2019-04-02 02:29', 120), 'Parado'  );

insert into temporal values (convert(datetime, '2019-04-03 02:29', 120), 'Operando');

insert into temporal values (convert(datetime, '2019-04-04 02:29', 120), 'Operando');

insert into temporal values (convert(datetime, '2019-04-05 02:29', 120), 'Operando');

WITH

TMP_ORD (Fecha, Estado, Posicion)

AS   (select Fecha_Hora

           , Estado

           , ROW_NUMBER() OVER (Order BY Fecha_Hora) Posicion

      from   temporal)

select FORMAT(min(fecha), 'dd/MM/yyyy hh:mm' ) fecha_inicio

     , FORMAT(max(sig_fecha), 'dd/MM/yyyy hh:mm' ) fecha_termino

     , estado

from   (

        select a.fecha fecha

             , b.fecha sig_fecha

             , a.estado estado

             , b.estado sig_estado

             , sum(case when a.estado!=b.estado

                        then 1 else 0

                   end) over (partition by a.estado order by a.posicion) grp

        from   tmp_ord a

               left join tmp_ord b

                      on a.Posicion = b.Posicion - 1

       ) SQ

where  estado = sig_estado

and    estado = 'Parado'

group  by grp, estado;

Messages:
Msg 195, Level 15, State 10, Line 34
'FORMAT' is not a recognized built-in function name.
Msg 102, Level 15, State 1, Line 44
Incorrect syntax near 'order'.

Valora esta respuesta

Comentar

Pos: 8

Val: 86

Seleccionar primer y ultimo registro de grupos

Publicado por Rafael (110 intervenciones) el 13/03/2019 17:35:09

quita el format ...
dejalo asi

WITH

TMP_ORD (Fecha, Estado, Posicion)

AS   (select Fecha_Hora

           , Estado

           , ROW_NUMBER() OVER (Order BY Fecha_Hora) Posicion

      from   temporal)

select min(fecha) fecha_inicio

     ,  max(sig_fecha) fecha_termino

     , estado

from   (

        select a.fecha fecha

             , b.fecha sig_fecha

             , a.estado estado

             , b.estado sig_estado

             , sum(case when a.estado!=b.estado

                        then 1 else 0

                   end) over (partition by a.estado order by a.posicion) grp

        from   tmp_ord a

               left join tmp_ord b

                      on a.Posicion = b.Posicion - 1

       ) SQ

where  estado = sig_estado

and    estado = 'Parado'

group  by grp, estado;

Valora esta respuesta

Comentar

Pos: 98

Val: 7

Seleccionar primer y ultimo registro de grupos

Publicado por Alan (4 intervenciones) el 13/03/2019 17:39:17

Justo estaba intentando colocar un convert y desapareció el error

WITH

TMP_ORD (Fecha, Estado, Posicion)

AS   (select Fecha_Hora

           , Estado

           , ROW_NUMBER() OVER (Order BY Fecha_Hora) Posicion

      from   temporal)

select convert(varchar(10),min(Fecha),101) + ' ' +convert(varchar(8), min(Fecha),108) as fecha_inicio

     , convert(varchar(10),max(Fecha),101) + ' ' +convert(varchar(8), max(Fecha),108)  as fecha_termino

     , estado

from   (

        select a.fecha fecha

             , b.fecha sig_fecha

             , a.estado estado

             , b.estado sig_estado

             , sum(case when a.estado!=b.estado

                        then 1 else 0

                   end) over (partition by a.estado order by a.Posicion) grp

        from   tmp_ord a left join tmp_ord b

        on a.Posicion = b.Posicion - 1

       ) SQ

where  estado = sig_estado

and    estado = 'Parado'

group  by grp, estado;

Sin embargo aun me figura el error del 'order'

Msg 102, Level 15, State 1, Line 17
Incorrect syntax near 'order'.

Valora esta respuesta

Comentar

Pos: 8

Val: 86

Seleccionar primer y ultimo registro de grupos

Publicado por Rafael (110 intervenciones) el 14/03/2019 09:34:04

OJO

La fecha termino no debe ser sobre el MAX(fecha) sino MAX (sig_Fecha)

El error te lo esta marcando al parecer en esta sentencia:
over (partition by a.estado order by a.Posicion) grp

Yo no puedo ver el error ... y segun la documentacion esta soportada la agregacion con over en la version 2008 ....
Revisa la documentacion en tu ordenador ... para ver si no existe algun otro problema....

Valora esta respuesta

Comentar

Pos: 6

Val: 102

Seleccionar primer y ultimo registro de grupos

Publicado por Vega (43 intervenciones) el 19/03/2019 22:39:37

Hola,
He vuelto a mirar esto por el gugel.
Parece haber alguna confusión al respecto.
Encontré un post en sqlservercentral (https://www.sqlservercentral.com/Forums/Topic1755825-3077-1.aspx) al respecto en donde alguien preguntaba por la necesidad de utilizar ORDER BY con las funciones Min() y Max() ya que por definición solamente dependen de la partición del OVER [/] siendo la función misma quien ordena los registros para extraer el resultado.

Bueno, básicamente esto quiere decir que el ORDER BY es redundante para Min() y [i]Max().

He probado mi consulta con SQL Server 2008 R2 quitando el order by en las líneas 79 y 80 y me funciona el código.

SET DATEFORMAT DMY

IF EXISTS (SELECT OBJECT_ID('tempdb..#Estado'))

	DROP TABLE #Estado

GO

SELECT	*

INTO	#Estado

FROM (

	SELECT FechaHora = CAST('12/03/2019 02:29' AS DATETIME) , Estado = 'Parado'		UNION ALL

	SELECT CAST('13/03/2019 02:29' AS DATETIME),	'Parado'						UNION ALL

	SELECT CAST('14/03/2019 02:29' AS DATETIME),	'Parado'						UNION ALL

	SELECT CAST('15/03/2019 02:29' AS DATETIME),	'Parado'						UNION ALL

	SELECT CAST('16/03/2019 02:29' AS DATETIME),	'Parado'						UNION ALL

	SELECT CAST('17/03/2019 02:29' AS DATETIME),	'Parado'						UNION ALL

	SELECT CAST('18/03/2019 02:29' AS DATETIME),	'Operando'						UNION ALL

	SELECT CAST('19/03/2019 02:29' AS DATETIME),	'Operando'						UNION ALL

	SELECT CAST('20/03/2019 02:29' AS DATETIME),	'Operando'						UNION ALL

	SELECT CAST('21/03/2019 02:29' AS DATETIME),	'Operando'						UNION ALL

	SELECT CAST('22/03/2019 02:29' AS DATETIME),	'Operando'						UNION ALL

	SELECT CAST('23/03/2019 02:29' AS DATETIME),	'Operando'						UNION ALL

	SELECT CAST('24/03/2019 02:29' AS DATETIME),	'Operando'						UNION ALL

	SELECT CAST('25/03/2019 02:29' AS DATETIME),	'Operando'						UNION ALL

	SELECT CAST('26/03/2019 02:29' AS DATETIME),	'Operando'						UNION ALL

	SELECT CAST('27/03/2019 02:29' AS DATETIME),	'Parado'						UNION ALL

	SELECT CAST('28/03/2019 02:29' AS DATETIME),	'Parado'						UNION ALL

	SELECT CAST('29/03/2019 02:29' AS DATETIME),	'Parado'						UNION ALL

	SELECT CAST('30/03/2019 02:29' AS DATETIME),	'Parado'						UNION ALL

	SELECT CAST('31/03/2019 02:29' AS DATETIME),	'Parado'						UNION ALL

	SELECT CAST('01/04/2019 02:29' AS DATETIME),	'Parado'						UNION ALL

	SELECT CAST('02/04/2019 02:29' AS DATETIME),	'Parado'						UNION ALL

	SELECT CAST('03/04/2019 02:29' AS DATETIME),	'Operando'						UNION ALL

	SELECT CAST('04/04/2019 02:29' AS DATETIME),	'Operando'						UNION ALL

	SELECT CAST('05/04/2019 02:29' AS DATETIME),	'Operando'

) Datos

create clustered index I_CL_Estado on #Estado (FechaHora asc)

;WITH EstadoSecuenciado AS (

	SELECT	*

	,		Secuenciador = row_number() over (order by FechaHora asc)

	FROM	#Estado i

),

Continuidad AS (

SELECT		i_fechahora		= i.FechaHora

,			f_fechahora		= ISNULL(f.FechaHora, i.FechaHora)

,			i_estado		= i.Estado

,			f_estado		= ISNULL(f.Estado, I.Estado)

,			i_secuenciador	= i.secuenciador

,			f_secuenciador	= f.secuenciador

,			HayContinuidad	= case when datediff(minute, f.fechahora , i.fechahora) < 0 and f.Estado = i.Estado then  1 else 0 end

FROM		EstadoSecuenciado I

LEFT JOIN	EstadoSecuenciado F on i.secuenciador = F.secuenciador -1

,Rec AS (

	SELECT	i_fechahora

	,		f_fechahora = CASE WHEN i_estado<> f_estado THEN i_fechahora ELSE f_fechahora END

	,		i_estado

	,		f_estado

	,		i_secuenciador

	,		f_secuenciador

	FROM	Continuidad

	WHERE	HayContinuidad = 0

	UNION ALL

	SELECT		c.i_fechahora

	,			r.f_fechahora

	,			r.i_estado

	,			r.f_estado

	,			r.i_secuenciador

	,			r.f_secuenciador

	FROM		Continuidad C

	JOIN		Rec R on c.f_fechahora = R.i_fechahora

			AND c.i_estado = R.i_estado

			AND c.HayContinuidad = 1

SELECT		DISTINCT

			HoraInicio	= MIN(i_fechahora) OVER(PARTITION BY i_secuenciador, f_secuenciador)

,			HoraFin		= MAX(f_fechahora) OVER(PARTITION BY i_secuenciador, f_secuenciador)

,			Estado		= i_estado

FROM		Rec

WHERE		i_estado	= 'Parado'

ORDER BY	1 ASC

Valora esta respuesta

Comentar

Pregunta sobre el nivel de dífucultad con el que se manejan bases de datos con este programa?

Conexión ODBC (se desconecta)