una operaciones obtener mes into fechas fecha convert con año sql-server tsql sql-server-2012

sql-server - operaciones - sql server split string into rows



Encontrar espacios en rangos de fechas-TSQL (3)

Tengo debajo de SQL y quiero brechas en las fechas a continuación.

declare @startdate datetime = ''2017-05-01'' declare @enddate datetime = ''2017-05-25'' create table #tmpdates (id int, date1 datetime, date2 datetime, rate int) insert into #tmpdates values (1, ''2017-05-05'', ''2017-05-15'', 10) insert into #tmpdates values (2, ''2017-05-16'', ''2017-05-18'', 12) insert into #tmpdates values (3, ''2017-05-21'', ''2017-05-25'', 15) select * from #tmpdates where date1 >= @startdate and date2 <= @enddate drop table #tmpdates

Por lo tanto, la producción debe contener 2017-05-01 a 2017-05-04 y 2017-05-19 a 2017-05-20 - 2 registros más.

Output: 1 5/1/2017 0:00 5/4/2017 0:00 NO DATA 2 5/5/2017 0:00 5/15/2017 0:00 10 3 5/16/2017 0:00 5/18/2017 0:00 12 4 5/19/2017 0:00 5/20/2017 0:00 NO DATA 5 5/21/2017 0:00 5/25/2017 0:00 15

En mi consulta anterior, solo regresa el registro del rango de la fecha. Por favor guía o ¿cómo puedo incluir esos también?


Esto funciona bajo la suposición de que no hay intervalos superpuestos.

declare @startdate datetime = ''2017-05-16'' declare @enddate datetime = ''2017-05-26'' create table #tmpdates (id int, date1 datetime, date2 datetime, rate int) insert into #tmpdates values (0, ''2017-04-01'', ''2017-04-25'',22) insert into #tmpdates values (1, ''2017-05-05'', ''2017-05-15'', 10) insert into #tmpdates values (2, ''2017-05-16'', ''2017-05-18'', 12) insert into #tmpdates values (3, ''2017-05-21'', ''2017-05-25'', 15) declare @final_result table (date1 date, date2 date, rate int) insert into @final_result select @startdate,dateadd(day,-1,t.date1),null from #tmpdates t where @startdate < t.date1 and t.date1 <= (select min(t1.date1) from #tmpdates t1 where t1.date1 >= @startdate) union all select date1, date2, rate from #tmpdates where (date1 >= @startdate or date2 >= @startdate) and (date2 <= @enddate or date1 <= @enddate) union all select dateadd(day,1,t.date2), ( select dateadd(day,-1,min(t3.date1)) from #tmpdates t3 where t3.date1 > t.date2) , null from #tmpdates t where dateadd(day,1,t.date2) < (select min(t1.date1) from #tmpdates t1 where t1.date1 > t.date2) and t.date1 >= @startdate and t.date2 <= @enddate union all select dateadd(day,1,max(t.date2)), @enddate, null from #tmpdates t having max(t.date2) < @enddate drop table #tmpdates select * from @final_result order by date1

EDITAR

Recopila datos de cuatro consultas y lo hace union all .

La primera consulta

select @startdate,dateadd(day,-1,t.date1),null from #tmpdates t where @startdate < t.date1 and t.date1 <= (select min(t1.date1) from #tmpdates t1 where t1.date1 >= @startdate)

Selecciona el espacio entre @startdate y la primera (más pequeña) fecha en la tabla, si hay intervalos antes de @startdate , se ignoran. Por lo tanto, selecciona la brecha, si la hay, desde @startdate hasta la primera fecha del intervalo que sea mayor que @startdate .

La segunda consulta:

select date1, date2, rate from #tmpdates where (date1 >= @startdate or date2 >= @startdate) and (date2 <= @enddate or date1 <= @enddate)

Selecciona los registros de la tabla (no huecos). Si @startdate se encuentra entre el rango, ese registro está incluido. Lo mismo ocurre con el parámetro @enddate .

La tercera consulta:

select dateadd(day,1,t.date2), ( select dateadd(day,-1,min(t3.date1)) from #tmpdates t3 where t3.date1 > t.date2) , null from #tmpdates t where dateadd(day,1,t.date2) < (select min(t1.date1) from #tmpdates t1 where t1.date1 > t.date2) and t.date1 >= @startdate and t.date2 <= @enddate

selecciona las brechas entre los intervalos más pequeños y más grandes (que caen entre @startdate y @enddate ) en la tabla.

Y finalmente la cuarta consulta:

select dateadd(day,1,max(t.date2)), @enddate, null from #tmpdates t having max(t.date2) < @enddate

Selecciona la brecha entre la fecha más grande (la más grande entre @startdate y @enddate ) en la tabla y @enddate , si hay un espacio.

Todos estos registros se insertan en la tabla @final_result , de modo que se pueden ordenar por intervalo.


Por favor, use la siguiente consulta:

DECLARE @STARTDATE DATE = ''2017-05-01'' DECLARE @ENDDATE DATE = ''2017-05-25'' DECLARE @DATES TABLE (ID INT, DATE1 DATE, DATE2 DATE, RATE INT) INSERT INTO @DATES VALUES (1, ''2017-05-05'', ''2017-05-15'', 10), (2, ''2017-05-16'', ''2017-05-19'', 12), (3, ''2017-05-21'', ''2017-05-25'', 15) SELECT* FROM ( SELECT @STARTDATE AS DATE1,DATEADD(DAY,-1,MIN(DATE1)) AS DATE2,''NO DATA''AS RATE FROM @DATES UNION SELECT CASE WHEN LEAD(DATE1) OVER (ORDER BY DATE1) = DATEADD(DAY,1,DATE2) THEN NULL ELSE DATEADD(DAY,1,DATE2) END AS DATE1, CASE WHEN LEAD(DATE1) OVER (ORDER BY DATE1) = DATEADD(DAY,1,DATE2) THEN NULL ELSE LEAD(DATEADD(DAY,-1,DATE1)) OVER (ORDER BY DATE1) END AS DATE2, ''NO DATA''AS RATE FROM @DATES d UNION SELECT DATE1, DATE2,CAST(RATE AS NVARCHAR(10)) FROM @DATES UNION SELECT DATEADD(DAY,1,MAX(DATE2)) AS DATE1,@ENDDATE AS DATE2,''NO DATA''AS RATE FROM @DATES ) A WHERE A.DATE2 IS NOT NULL AND A.DATE1 <= A.DATE2 AND DATE1 >= @STARTDATE AND DATE2 <=@ENDDATE ORDER BY A.DATE1


Puedes probar el siguiente código. Estoy atravesando desde @StartDate hasta @endDate y buscando los huecos.

declare @startdate datetime = ''2017-05-01'' declare @enddate datetime = ''2017-05-04'' declare @startdate1 datetime, @enddate1 datetime declare @dates table (date1 date,date2 date) create table #tmpdates (id int, date1 datetime, date2 datetime, rate int) insert into #tmpdates values (1, ''2017-05-05'', ''2017-05-15'', 10) insert into #tmpdates values (2, ''2017-05-16'', ''2017-05-18'', 12) insert into #tmpdates values (3, ''2017-05-21'', ''2017-05-25'', 15) select * from #tmpdates where date1 >= @startdate and date2 <= @enddate set @startdate1=@startdate while @startdate1<=@enddate begin if not exists(select 1 from #tmpdates where @startdate1 between date1 and date2) begin if not exists (select 1 from @dates where @startdate1 > date1 and date2 is null) begin insert into @dates(date1)values(@startdate1) end else begin if @startdate1+1>=@enddate begin update @dates set date2=@startdate1 where date2 is null end set @startdate1+=1 end end else begin update @dates set date2=@startdate1-2 where date2 is null end set @startdate1+=1 end select * from (select date1,date2, rate from #tmpdates union select *,0 as rate from @dates ) A WHERE date1>=@startdate and date2<=@enddate drop table #tmpdates