[SOLUCIONADO] Consulta SQL resta de 2 fechas en el mismo campo en distinto registro. (con condición

[mariolos]

Hola, estimados compañeros tengo una duda sobre si se puede hacer o no una consulta con las sgtes caracteristicas:

con una tabla perecida a esta:

id - Fecha - Estado

1 - 01-01-2013 08:00 - inicio

1 - 01-01-2013 10:00 - fin

1 - 01-01-2013 13:00 - inicio

1 - 01-01-2013 13:30 - fin

1 - 01-01-2013 13:40 - fin

1 - 01-01-2013 14:00 - inicio

1 - 01-01-2013 14:30 - inicio

1 - 01-01-2013 15:40 - fin

La idea es poder restar las horas entre el primer inicio y el ultimo fin (por cada inicio) por ejemplo en este caso quedaria

10:00 - 08:00

13:40 - 13:00

15:40 - 14:00

Se puede hacer por consulta?


Muchas Gracias.

[Libras]

si se puede pero con la estructura de datos que mencionas no, porque? porque no sabes que inicio o que fin le corresponde a cada "evento" como se cual es el primer inicio? como se que fin le corresponde a cada inicio??

[mariolos]

Ese es el problema, debo considerar el primer inicio y el próximo fin seria el que este antes de un inicio.
Sobre el inicio y el fin.. la lógica es esta:

Se tomar el primer inicio luego se buscar el ultimo fin antes de un inicio, luego se repite el ciclo.
Pensaba que algo se podía hacer el sql.

Pude crear algo así:
Esto me deja en un registro el inicio y el fin, pero no me soluciona el problema que exista un segundo fin luego del anterior.

Código SQL:

SELECT 
DISTINCT  
  CONVERT(VARCHAR(10),s.DATE, 103) AS fecha,   
  (SELECT  
   MIN(CONVERT(VARCHAR(5),x.DATE,108))
  FROM TABLA_Datos AS x 
  WHERE 
  estado IN('Inicio') AND CONVERT(VARCHAR(10),x.DATE, 103) 
  = CONVERT(VARCHAR(10),s.DATE, 103)) AS encendido,
  (SELECT  
 MAX(CONVERT(VARCHAR(5),x.DATE,108))
  FROM TABLA_Datos AS x 
  WHERE 
  estado IN('Fin') AND CONVERT(VARCHAR(10),x.DATE, 103) 
  = CONVERT(VARCHAR(10),s.DATE, 103)) AS apagado
  FROM FROM TABLA_Datos AS s
  WHERE 
  estado IN('Inicio')
  AND   DATE BETWEEN  '20130601' AND '20131230'  
  AND id= 61
  GROUP BY DATE
 

gracias

[iislas]

Coincido con Libras, pésima estructura de la tabla

[mariolos]

compañeros.. la estructura venia, de hecho lo estoy tomando de una maquina que registra los inicios y los fin.. ante esta estructura no puedo hacer nada, solo necesito poder sacar la info en una SQL.
tienen alguna idea que me puedan dar?

muchas gracias.

[Libras]

se me ocurre que puedes hacer esto:

Código SQL:

CREATE TABLE #temp
(
id INT,
fecha datetime,
estado VARCHAR(20),
rn INT IDENTITY(1,1)
)
 
 
 
INSERT INTO #temp VALUES (1,'01-01-2013 08:00','inicio')
 
INSERT INTO #temp VALUES (1,'01-01-2013 10:00','fin')
 
INSERT INTO #temp VALUES (1,'01-01-2013 13:00','inicio')
 
INSERT INTO #temp VALUES (1,'01-01-2013 13:30','fin')
 
INSERT INTO #temp VALUES (1,'01-01-2013 13:40','fin')
 
INSERT INTO #temp VALUES (1,'01-01-2013 14:00','inicio')
 
INSERT INTO #temp VALUES (1,'01-01-2013 14:30','inicio')
 
INSERT INTO #temp VALUES (1,'01-01-2013 15:40','fin')
 
 
 
DECLARE @x INT
DECLARE @resultado TABLE (inicio datetime,fin datetime, horas INT)
DECLARE @inicio datetime
DECLARE @fin datetime
DECLARE @horas INT
DECLARE @STATUS VARCHAR(20)
DECLARE @flag INT
SET @x=1
SET @flag=0
while @x<=(SELECT COUNT(*) FROM #temp)
BEGIN
 
IF ((SELECT estado FROM #temp WHERE rn=@x)='inicio' AND @flag=0)
BEGIN
    SELECT @inicio=fecha FROM #temp WHERE rn=@x
    SET @flag=1
END
IF(SELECT estado FROM #temp WHERE rn=@x)='fin'
    BEGIN
        IF( ((SELECT estado FROM #temp WHERE rn=@x+1)='Inicio') OR (@x=(SELECT COUNT(*) FROM #temp))  )
        BEGIN           
            SELECT @fin=fecha FROM #temp WHERE rn=@x
            SET @horas=datediff(hh,@inicio,@fin)
            INSERT INTO @resultado VALUES (@inicio,@fin,@horas)
            SET @flag=0
        END
    END
 
SET @x=@x+1
END
SELECT * FROM @resultado
 

Con un query sencillo veo muy complejo que puedas hacer lo que necesitas :P

[GeriReshef]

Los datos:

Código SQL:

CREATE TABLE #T(id INT,
                Fecha DateTime,
                Estado VARCHAR(10));
 
INSERT INTO #T SELECT 1,'01-01-2013 08:00','inicio';
INSERT INTO #T SELECT 1,'01-01-2013 10:00','fin';
INSERT INTO #T SELECT 1,'01-01-2013 13:00','inicio';
INSERT INTO #T SELECT 1,'01-01-2013 13:30','fin';
INSERT INTO #T SELECT 1,'01-01-2013 13:40','fin';
INSERT INTO #T SELECT 1,'01-01-2013 14:00','inicio';
INSERT INTO #T SELECT 1,'01-01-2013 14:30','inicio';
INSERT INTO #T SELECT 1,'01-01-2013 15:40','fin';
 

La recuperacion:

Código SQL:

WITH T1 AS
(SELECT ROW_NUMBER() OVER(ORDER BY Fecha)-ROW_NUMBER() OVER(Partition BY Estado ORDER BY Fecha) N,
        *
FROM    #T),
T2 AS
(SELECT CASE Estado WHEN 'inicio' THEN N+1 ELSE N END N,
        Estado,
        CASE Estado WHEN 'inicio' THEN MIN(Fecha) ELSE MAX(Fecha) END Fecha
FROM    T1
GROUP BY N,
        Estado)
SELECT  MIN(Fecha) inicio,
        MAX(Fecha) Fin
FROM    T2
GROUP BY N
ORDER BY N;
 

[Libras]

wow excelente respuesta Geri :)

[mariolos]

muchas gracias, las voy a probar y les comentos!! graciasl Mil!

[GeriReshef]

De hecho- mi respuesta no es correcta..
Voy a agregar unas filas mas (02-01-2013 14:30 - 02-01-2013 15:40):

Código SQL:

INSERT INTO #T SELECT 1,'02-01-2013 14:00','inicio';
INSERT INTO #T SELECT 1,'02-01-2013 14:30','inicio';
INSERT INTO #T SELECT 1,'02-01-2013 14:40','inicio';
INSERT INTO #T SELECT 1,'02-01-2013 14:50','inicio';
INSERT INTO #T SELECT 1,'02-01-2013 15:30','fin';
INSERT INTO #T SELECT 1,'02-01-2013 15:33','fin';
INSERT INTO #T SELECT 1,'02-01-2013 15:35','fin';
INSERT INTO #T SELECT 1,'02-01-2013 15:40','fin';
 

y la correcta recuperacion es:

Código SQL:

WITH T1 AS
(SELECT ROW_NUMBER() OVER(ORDER BY Fecha)-ROW_NUMBER() OVER(Partition BY Estado ORDER BY Fecha) N,
        *
FROM    #T),
T2 AS
(SELECT N, --Case Estado When 'inicio' Then N+1 Else N End N,
        Estado,
        CASE Estado WHEN 'inicio' THEN MIN(Fecha) ELSE MAX(Fecha) END Fecha
FROM    T1
GROUP BY N,
        Estado),
T3 AS
(SELECT ROW_NUMBER() OVER(Partition BY Estado ORDER BY Fecha) N,
        Estado,
        Fecha
FROM    T2 )
SELECT  MIN(Fecha) inicio,
        MAX(Fecha) Fin
FROM    T3
GROUP BY N
ORDER BY N;
 

Libras- no debes corregir nada, tu codigo es correcto tambien con este caso extremo.

[mariolos]

muchas gracias a ambos!!! me faltan palabras para agradecer la desinteresada ayuda... solo me salta una duda... si el primer registro es "fin" debería pasar al próximo.. en el ejemplo de GeriReshef lo toma como el primero.. intente arreglarlo pero no pude.. me ayudan??

[Libras]

en mi ejemplo aunque el primer registro sea fin toma el primer inicio ;)

[GeriReshef]

Se pone más y más complicado..
Supongo que en tal casos debemos omitir los 'fin' en el principio y los 'inicio' en el fin.
Te adjunto toda la solución con todos los datos:

Código SQL:

IF Object_ID('tempdb..#T','U') IS NOT NULL DROP TABLE #T;
 
CREATE TABLE #T(id INT,
                Fecha DateTime,
                Estado VARCHAR(10));
 
INSERT INTO #T SELECT 1,'01-01-2013 01:00','fin';
INSERT INTO #T SELECT 1,'01-01-2013 02:00','fin';
INSERT INTO #T SELECT 1,'01-01-2013 03:00','fin';
 
INSERT INTO #T SELECT 1,'01-01-2013 08:00','inicio';
INSERT INTO #T SELECT 1,'01-01-2013 10:00','fin';
INSERT INTO #T SELECT 1,'01-01-2013 13:00','inicio';
INSERT INTO #T SELECT 1,'01-01-2013 13:30','fin';
INSERT INTO #T SELECT 1,'01-01-2013 13:40','fin';
INSERT INTO #T SELECT 1,'01-01-2013 14:00','inicio';
INSERT INTO #T SELECT 1,'01-01-2013 14:30','inicio';
INSERT INTO #T SELECT 1,'01-01-2013 15:40','fin';
 
INSERT INTO #T SELECT 1,'02-01-2013 14:00','inicio';
INSERT INTO #T SELECT 1,'02-01-2013 14:30','inicio';
INSERT INTO #T SELECT 1,'02-01-2013 14:40','inicio';
INSERT INTO #T SELECT 1,'02-01-2013 14:50','inicio';
INSERT INTO #T SELECT 1,'02-01-2013 15:30','fin';
INSERT INTO #T SELECT 1,'02-01-2013 15:33','fin';
INSERT INTO #T SELECT 1,'02-01-2013 15:35','fin';
INSERT INTO #T SELECT 1,'02-01-2013 15:40','fin';
 
 
INSERT INTO #T SELECT 1,'02-01-2013 23:00','inicio';
INSERT INTO #T SELECT 1,'02-01-2013 23:10','inicio';
INSERT INTO #T SELECT 1,'02-01-2013 23:20','inicio';
 
WITH T1 AS
(SELECT ROW_NUMBER() OVER(ORDER BY Fecha)-ROW_NUMBER() OVER(Partition BY Estado ORDER BY Fecha) N,
        *
FROM    #T),
T2 AS
(SELECT N,
        Estado,
        CASE Estado WHEN 'inicio' THEN MIN(Fecha) ELSE MAX(Fecha) END Fecha,
        MIN(N) OVER() MinN,
        MAX(N) OVER() MaxN
FROM    T1
GROUP BY N,
        Estado),
T3 AS
(SELECT ROW_NUMBER() OVER(Partition BY Estado ORDER BY Fecha) N,
        Estado,
        Fecha
FROM    T2
WHERE   NOT (N=MinN AND Estado='fin')
        AND NOT (N=MaxN AND Estado='inicio'))
SELECT  MIN(Fecha) inicio,
        MAX(Fecha) Fin
FROM    T3
GROUP BY N
ORDER BY N;
 

[mariolos]

mucha gracias a ambos! realmente se pasaron..
Libras en realidad si me percate que tu consulta no considera si fin era al principio, pero me complicaba porque le asignas un indice a la tabla, y en mi tabla de producción actual no la tengo.. no supe como solucionar eso.. pero agradezco igualmente..

GeriReshef -> muchas gracias nuevamente..

Estas consultas no creo que tenga problema para correrla en una conexión php + odbc de sqlserver cierto..

[Libras]

para asignar el id en una tabla que tienes en produccion puedes hacer lo siguiente:

Código SQL:

SELECT *,IDENTITY(INT,1,1) AS rn INTO #temp FROM tabla
 
SELECT * FROM #temp
 

prueba con ese query y obtienes los datos con un numerico consecutivo :)

[mariolos]

Estupendo Libras.. muchas gracias.

[GeriReshef]

Mariolos- recibi tu mensaje, pero no puedo contestar.

Editado: Violacion a la Política de uso de Foros del Web 2.3

[mariolos]

gracias te lo envió enseguida!