Iniciar sesiónCerrar
Correo:
Contraseña:
Entrar
Recordar sesión en este navegador
Iniciar sesiónIniciar sesiónCrear cuentaCrear cuenta
LWP » Foros » SQL » Intento alcanzar el OUTPUT

SQL - Intento alcanzar el OUTPUT

Volver
Nuevo Tema
<<>>
 
Vista:
sin imagen de perfil

Intento alcanzar el OUTPUT

Publicado por Patricio (1 intervención) el 12/11/2022 09:21:02
Tengo esta tabla que se llama (t)

ed (t)

| fecha | employee | motive | descrip |
| ---------- | -------- | ------ | ------- |
| 01/01/2022 | PETER | B | baja |
| 01/01/2022 | MARY | R | ruta |
| 01/01/2022 | ANN | R | ruta |
| 02/01/2022 | PETER | B | baja |
| 02/01/2022 | MARY | R | ruta |
| 02/01/2022 | ANN | R | ruta |
| 03/01/2022 | PETER | B | baja |
| 03/01/2022 | MARY | R | ruta |
| 03/01/2022 | ANN | R | ruta |
| 04/01/2022 | PETER | R | ruta |
| 04/01/2022 | MARY | R | ruta |
| 04/01/2022 | ANN | R | ruta |
| 05/01/2022 | PETER | R | ruta |
| 05/01/2022 | MARY | R | ruta |
| 05/01/2022 | ANN | R | ruta |
| 06/01/2022 | PETER | B | baja |
| 06/01/2022 | MARY | R | ruta |
| 06/01/2022 | ANN | R | ruta |


Y quiero alcanzar este OUTPUT

| employee | start\_date | end\_date |
| -------- | ----------- | ---------- |
| PETER | 01/01/2022 | 03/01/2022 |
| PETER | 06/01/2022 | 06/01/2022 |


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
select
    employee, min(fecha), max(fecha)
from
    (select
         t.*,
         lag(motive) over (partition by employee order by fecha) as prev_motive,
         lead(motive) over (partition by employee order by fecha) as next_motive,
         sum(case when motive = 'B' then 1 else 0 end) over (partition by employee order by fecha) as num_b
     from t) t
where
    motive = 'B'
    and (prev_motive <> 'B' or prev_motive is null)
    and (next_motive <> 'B' or next_motive is null)
group by
    employee, num_b;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
select
    employee, min(fecha), max(fecha)
from
    (select
         t.*,
         lag(motive) over (partition by employee order by fecha) as prev_motive,
         lead(motive) over (partition by employee order by fecha) as next_motive,
         sum(case when motive = 'B' then 1 else 0 end) over (partition by employee order by fecha) as num_b
     from t) t
where
    motive = 'B'
    and (prev_motive <> 'B')
    and (next_motive <> 'B')
group by
    employee, num_b;

Me pueden ayudar?
Valora esta pregunta
Me gusta: Está pregunta es útil y esta claraNo me gusta: Está pregunta no esta clara o no es útil
0
Responder