Re: [pgsql-es-ayuda] RE: [pgsql-es-ayuda] Re: [pgsql-es-ayuda] Consulta SQL resta de 2 fechas en el mismo campo en distinto registro. (con condición especial)

Hola de nuevo

Haciendo el mismo ejercicio pude extender la consulta para que coloque el
inicio y el fin en el mismo registro.

Asumiendo que tienes una tabla llamada mi_tabla puedes crear una vista con
el primer query que pase...

create view mi_vista as
select t.id, t.estado, t.fecha from (
select id, estado, fecha,
case
when estado = 'fin' and lead(estado) over w = 'inicio' then fecha
when estado = 'fin' and lead(estado) over w is null then fecha
when estado = 'inicio' and lag(estado) over w = 'fin' then fecha
when estado = 'inicio' and lag(estado) over w is null then fecha
else null end as ini_fin
from mi_tabla window w as (order by fecha)
) t
where t.ini_fin is not null

Con esta vista puedes ejecutar esta nueva consulta similar a la anterior...

select t.id, t.fin, t.inicio from (
select id, estado, fecha,
case
when estado = 'inicio' then lead(fecha) over w
else null end as fin,
case
when estado = 'inicio' then fecha
else null end as inicio
from mi_vista window w as (order by fecha)
) t
where t.inicio is not null

que te dará este resultado...

id | fin | inicio
----+---------------------+---------------------
1 | 2013-01-01 10:00:00 | 2013-01-01 08:00:00
1 | 2013-01-01 13:40:00 | 2013-01-01 13:00:00
1 | 2013-01-01 15:40:00 | 2013-01-01 14:00:00

Deberías analizar si te sirve en terminos de rendimiento pero cumple tu
pedido de ser una consulta SQL.

Saludos,
Walter

2013/10/22 Walter Cattebeke <walter(dot)cattebeke(at)gmail(dot)com>

> Hola
>
> Esto se puede solucionar con funciones de ventana.
> Te recomiendo leer este articulo
> http://tapoueh.org/blog/2012/10/05-reset-counter
> alli se explica un problema que presenta similitudes con el tuyo.
> Modificando un poco ese ejemplo consegui este query...
>
> select t.id, t.estado, t.fecha from (
> select id, estado, fecha,
> case
> when estado = 'fin' and lead(estado) over w = 'inicio' then fecha
> when estado = 'fin' and lead(estado) over w is null then fecha
> when estado = 'inicio' and lag(estado) over w = 'fin' then fecha
> when estado = 'inicio' and lag(estado) over w is null then fecha
> else null end as ini_fin
> from mi_tabla window w as (order by fecha)
> ) t
> where t.ini_fin is not null
>
> que te da este resultado...
>
> id | estado | fecha
> ----+--------+---------------------
> 1 | inicio | 2013-01-01 08:00:00
> 1 | fin | 2013-01-01 10:00:00
> 1 | inicio | 2013-01-01 13:00:00
> 1 | fin | 2013-01-01 13:40:00
> 1 | inicio | 2013-01-01 14:00:00
> 1 | fin | 2013-01-01 15:40:00
>
> Eliminando los registros intermedios. A partir de ahi podrias usar
> crosstab o seguir con las funciones de ventana.
>
> Saluds,
> Walter
>
>
>
> 2013/10/22 Pelluco Pelluco <pelluco_(at)hotmail(dot)com>
>
>> Muchas gracias por la respuesta, sobre el diseño esta descartado, no
>> puedo modificarlo.[image: Emoji]
>> 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í: (tiene un par de funciones de sql server, pero eso
>> era solo para una prueba.
>> 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.
>>
>> 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
>>
>>
>> Muchas gracias.
>>
>>
>> ------------------------------
>> Date: Tue, 22 Oct 2013 09:26:37 -0500
>>
>> Subject: [pgsql-es-ayuda] Re: [pgsql-es-ayuda] Consulta SQL resta de 2
>> fechas en el mismo campo en distinto registro. (con condición especial)
>> From: miguel(dot)hdz(dot)mrn(at)gmail(dot)com
>> To: mariolos(at)gmail(dot)com
>> CC: npolanco(at)cuij(dot)edu(dot)cu; pgsql-es-ayuda(at)postgresql(dot)org
>>
>>
>> Buenos días
>>
>> Probablemente puedas hacer algo con consultas anidadas, pero como sabes
>> que inició y que final van juntos, te falta un Id para distinguir el
>> agrupamiento, lo único que podrías hacer es una función que los ordene
>> primero los inicio y luego los finales y tomar uno x uno y ordenarlos manual
>>
>> Por qué con eso de que todos los id son 1 y la premisa es de que la mitad
>> es inició y la otra mitad es fin pudiera aplicar
>>
>> Pero cúrate de males y haz lo que te comento el compañero, es inpractico
>> el diseño así!
>>
>> Saludos
>>
>> El martes, 22 de octubre de 2013, Pelluco Pelluco escribió:
>>
>> muchas gracias por la respuesta, pero te comento que el diseño ya venia
>> así, esto se registra a través de una maquina y no puedo entrar a
>> modificarlo, es por eso que me gustaría saber si se puede realizar una
>> consulta que lo pueda calcular.
>>
>> gracias
>> Pedro.
>>
>> ------------------------------
>> Date: Tue, 22 Oct 2013 09:53:42 -0400
>> From: npolanco(at)cuij(dot)edu(dot)cu
>> To: pgsql-es-ayuda(at)postgresql(dot)org
>> Subject: [pgsql-es-ayuda] Re: [pgsql-es-ayuda] Consulta SQL resta de 2
>> fechas en el mismo campo en distinto registro. (con condición especial)
>>
>> ¿por qué ese diseño, no sería mejor:?
>>
>> *id - fecha - hora_inicio - hora_fin*
>>
>> No sé, digo yo, me parece que es más fácil cualquier operación y hasta
>> más lógico el diseño.
>>
>>
>> On 22/10/13 08:42, Pelluco Pelluco wrote:
>>
>> 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.
>>
>>
>>
>>
>> --
>> ISC Miguel Angel Hernandez Moreno
>>
>>
>