Servidor SQL: ¿Cómo puedo transformar SQL para que muestre las fechas de inicio y finalización y todos los días?

Persona Estado Fecha de inicio José azul 4/2/2020 Beto rojo 1/12/2019 Beto negro 3/12/2009 José azul 4/2/2018 José rojo 1/12/2015 María negro 3/12/2009

Tengo una mesa configurada como se muestra arriba. Quiero transformar esto a lo siguiente

Persona Estado Fecha de inicio Fecha final José azul 4/2/2020 Beto rojo 1/12/2019 Beto negro 3/12/2009 30/11/2019 José azul 4/2/2018 3/2/2020 José rojo 1/12/2015 3/02/2018 María negro 3/12/2009

Después de esto, quiero tener una línea por cada día calendario en el que una persona se encuentre en un estado determinado. Si no hay una fecha de finalización, los días en un estado determinado deben terminar en la fecha actual.

¿Cómo puedo hacer esto solo con SQL?

1

Estoy a punto de cerrar sesión, así que intentaré volver mañana si no obtengo una respuesta, pero probablemente puedas hacerlo con una función de ventana. es decir, retraso (fecha de inicio) terminado (partición por persona orden por fecha de inicio desc) Debería obtener la penúltima fecha de inicio

- cod3-jr

28 de marzo de 2021 a las 3:09

Por favor, muestra tu intento

-Ardilla

28 de marzo de 2021 a las 3:21

¿Adivinaremos que EndDate surge cada vez que alguien entra en un estado alterado? ¿Puede una persona transitar por varios estados en un día, p. ¿Un viaje sublime del sólido al gas y al plasma? ¿Es una línea como una fila en una tabla?

HABO

28/03/2021 a las 13:01

------------------------------------

Quizás la función de ventana lead() junto con dateadd() sería una buena opción

Ejemplo

Select *
      ,EndDate = dateadd(day,-1,lead(StartDate,1) over (partition by Person Order by StartDate))
 From YourTable A

Devoluciones

Person  State   StartDate   EndDate
bob     black   2009-12-03  2019-11-30
bob     red     2019-12-01  NULL
joe     red     2015-12-01  2018-02-03
joe     blue    2018-02-04  2020-02-03
joe     blue    2020-02-04  NULL
mary    black   2009-12-03  NULL

EDITAR - Para expandir a diario

Tomamos la consulta anterior y agregamos IsNull(...,convert(date,getdate())) para capturar las fechas de finalización. Luego creamos una tabla de calendario ad-hoc y realizamos una combinación simple.

Select A.*
      ,B.D
 From (
        Select *
              ,EndDate = IsNull(dateadd(day,-1,lead(StartDate,1) over (partition by Person Order by StartDate)),convert(date,getdate()))
         From  YourTable A
      ) A  
 Join (
        Select Top (datediff(day,'1999-12-31',getdate()))
               D=dateadd(day,Row_Number() Over (Order By (Select NULL)),convert(date,'1999-12-31'))
         From  master..spt_values n1, master..spt_values n2 
      ) B on D between StartDate and EndDate
 Order By Person,D

Devuelve 10.210 filas

------------------------------------

Me gusta utilizar CTE recursivos para ampliar datos. Es bastante simple en tu caso:

with cte as (
      select person, state, startdate,
             lead(dateadd(day, -1, startdate),
                  1,
                  convert(date, getdate())
                 ) over (partition by person order by startdate) as enddate
      from t
      union all
      select person, state, dateadd(day, 1, startdate), enddate
      from cte
      where startdate < enddate
     )
select person, state, startdate
from cte
option (maxrecursion 0);

Aquí hay un violín db<>.