una over operaciones obtener mes fechas fecha extraer ejemplos dense_rank datepart con año sql sql-server database sql-server-2005 tsql

over - Generar un conjunto de resultados de fechas de incremento en TSQL



rank over partition sql (16)

Aunque realmente me gusta la solución de KM anterior (+1), debo cuestionar su suposición de "no bucle", dados los intervalos de fechas plausibles con los que su aplicación funcionará, tener un bucle no debería ser tan caro. El truco principal es generar los resultados del bucle en la tabla de transición / caché, de modo que conjuntos de consultas extremadamente grandes no ralenticen el sistema al volver a calcular las mismas fechas exactas. Por ejemplo, cada consulta solo calcula / almacena en caché los intervalos de fechas que NO están ya en caché y que necesita (y rellena previamente la tabla con un rango de fechas realistas como ~ 2 años por adelantado, con un rango determinado por las necesidades comerciales de la aplicación).

Considere la necesidad de crear un conjunto de resultados de fechas. Tenemos fechas de inicio y finalización, y nos gustaría generar una lista de fechas intermedias.

DECLARE @Start datetime ,@End datetime DECLARE @AllDates table (@Date datetime) SELECT @Start = ''Mar 1 2009'', @End = ''Aug 1 2009'' --need to fill @AllDates. Trying to avoid looping. -- Surely if a better solution exists.

Considere la implementación actual con un bucle WHILE :

DECLARE @dCounter datetime SELECT @dCounter = @Start WHILE @dCounter <= @End BEGIN INSERT INTO @AllDates VALUES (@dCounter) SELECT @dCounter=@dCounter+1 END

Pregunta: ¿Cómo crearías un conjunto de fechas que están dentro de un rango definido por el usuario usando T-SQL? Suponer SQL 2005+. Si su respuesta es el uso de características de SQL 2008, marque como tal.

El siguiente usa un CTE recursivo (SQL Server 2005+):

WITH dates AS ( SELECT CAST(''2009-01-01'' AS DATETIME) ''date'' UNION ALL SELECT DATEADD(dd, 1, t.date) FROM dates t WHERE DATEADD(dd, 1, t.date) <= ''2009-02-01'') SELECT ... FROM TABLE t JOIN dates d ON d.date = t.date --etc.

Esta solución se basa en la maravillosa respuesta de la misma pregunta para MySQL. También es muy eficiente en MSSQL. https://.com/a/2157776/466677

select DateGenerator.DateValue from ( select DATEADD(day, - (a.a + (10 * b.a) + (100 * c.a) + (1000 * d.a)), CONVERT(DATE, GETDATE()) ) as DateValue from (select a.a from (values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) as a(a)) as a cross join (select b.a from (values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) as b(a)) as b cross join (select c.a from (values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) as c(a)) as c cross join (select d.a from (values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) as d(a)) as d ) DateGenerator WHERE DateGenerator.DateValue BETWEEN ''Mar 1 2009'' AND ''Aug 1 2009'' ORDER BY DateGenerator.DateValue ASC

funciona solo para fechas anteriores, para fechas en cambios futuros, menos signo en la función DATEADD. Query funciona solo para SQL Server 2008+, pero también podría reescribirse para 2005 reemplazando la construcción "select from values" por uniones.

Este debería funcionar

seleccione Top 1000 DATEADD (d, ROW_NUMBER () OVER (ORDER BY Id), getdate ()) de sysobjects

La mejor respuesta es, probablemente, utilizar el CTE, pero no hay garantía de que pueda usarlo. En mi caso, tuve que insertar esta lista dentro de una consulta existente creada dinámicamente por un generador de consultas ... no pude usar CTE ni procedimientos almacenados.

Entonces, la respuesta de Devio fue realmente útil, pero tuve que modificarla para que funcione en mi entorno.

En caso de que no tenga acceso a la base de datos maestra, puede usar otra tabla en su base de datos. En cuanto al ejemplo anterior, el rango máximo de fechas viene dado por el número de filas dentro de la tabla elegida.

En mi ejemplo difícil, usando row_number, puede usar tablas sin una columna int real.

declare @bd datetime --begin date declare @ed datetime --end date set @bd = GETDATE()-50 set @ed = GETDATE()+5 select DATEADD(dd, 0, DATEDIFF(dd, 0, Data)) --date format without time from ( select (GETDATE()- DATEDIFF(dd,@bd,GETDATE())) --Filter on the begin date -1 + ROW_NUMBER() over (ORDER BY [here_a_field]) AS Data from [Table_With_Lot_Of_Rows] ) a where Data < (@ed + 1) --filter on the end date

La respuesta de @ KM crea primero una tabla de números y la usa para seleccionar un rango de fechas. Para hacer lo mismo sin la tabla de números temporales:

DECLARE @Start datetime ,@End datetime DECLARE @AllDates table (Date datetime) SELECT @Start = ''Mar 1 2009'', @End = ''Aug 1 2009''; WITH Nbrs_3( n ) AS ( SELECT 1 UNION SELECT 0 ), Nbrs_2( n ) AS ( SELECT 1 FROM Nbrs_3 n1 CROSS JOIN Nbrs_3 n2 ), Nbrs_1( n ) AS ( SELECT 1 FROM Nbrs_2 n1 CROSS JOIN Nbrs_2 n2 ), Nbrs_0( n ) AS ( SELECT 1 FROM Nbrs_1 n1 CROSS JOIN Nbrs_1 n2 ), Nbrs ( n ) AS ( SELECT 1 FROM Nbrs_0 n1 CROSS JOIN Nbrs_0 n2 ) SELECT @Start+n-1 as Date FROM ( SELECT ROW_NUMBER() OVER (ORDER BY n) FROM Nbrs ) D ( n ) WHERE n <= DATEDIFF(day,@Start,@End)+1 ;

Prueba por supuesto, si lo haces a menudo, una mesa permanente puede ser más eficiente.

La consulta anterior es una versión modificada de este artículo , que analiza la generación de secuencias y ofrece muchos métodos posibles. Me gustó esta, ya que no crea una tabla temporal, y no está limitada a la cantidad de elementos en la tabla sys.objects .

Lo que recomendaría: crear una tabla auxiliar de números y usarla para generar su lista de fechas. También puede usar un CTE recursivo, pero puede no funcionar tan bien como unirse a una tabla auxiliar de números. Consulte SQL, tabla de números auxiliares para obtener información sobre ambas opciones.

Me gusta CTE ya que es fácil de leer y de mantenimiento

Declare @mod_date_from date =getdate(); Declare @mod_date_to date =dateadd(year,1,@mod_date_from); with cte_Dates as ( SELECT @mod_date_from as reqDate UNION ALL SELECT DATEADD(DAY,1,reqDate) FROM cte_Dates WHERE DATEADD(DAY,1,reqDate) < @mod_date_to ) SELECT * FROM cte_Dates OPTION(MAXRECURSION 0);

No te olvides de establecer MAXRECURSION

Otra opción es crear la función correspondiente en .NET. Así es como se ve:

[Microsoft.SqlServer.Server.SqlFunction( DataAccess = DataAccessKind.None, FillRowMethodName = "fnUtlGetDateRangeInTable_FillRow", IsDeterministic = true, IsPrecise = true, SystemDataAccess = SystemDataAccessKind.None, TableDefinition = "d datetime")] public static IEnumerable fnUtlGetDateRangeInTable(SqlDateTime startDate, SqlDateTime endDate) { // Check if arguments are valid int numdays = Math.Min(endDate.Value.Subtract(startDate.Value).Days,366); List<DateTime> res = new List<DateTime>(); for (int i = 0; i <= numdays; i++) res.Add(dtStart.Value.AddDays(i)); return res; } public static void fnUtlGetDateRangeInTable_FillRow(Object row, out SqlDateTime d) { d = (DateTime)row; }

Esto es básicamente un prototipo y se puede hacer mucho más inteligente, pero ilustra la idea. Desde mi experiencia, para períodos de tiempo pequeños a moderados (como un par de años), esta función funciona mejor que la implementada en T-SQL. Otra buena característica de la versión CLR es que no crea una tabla temporal.

Para que este método funcione, debe realizar esta configuración de tabla de una sola vez:

SELECT TOP 10000 IDENTITY(int,1,1) AS Number INTO Numbers FROM sys.objects s1 CROSS JOIN sys.objects s2 ALTER TABLE Numbers ADD CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED (Number)

Una vez que la tabla Numbers está configurada, use esta consulta:

SELECT @Start+Number-1 FROM Numbers WHERE Number<=DATEDIFF(day,@Start,@End)+1

capturarlos hacer:

DECLARE @Start datetime ,@End datetime DECLARE @AllDates table (Date datetime) SELECT @Start = ''Mar 1 2009'', @End = ''Aug 1 2009'' INSERT INTO @AllDates (Date) SELECT @Start+Number-1 FROM Numbers WHERE Number<=DATEDIFF(day,@Start,@End)+1 SELECT * FROM @AllDates

salida:

Date ----------------------- 2009-03-01 00:00:00.000 2009-03-02 00:00:00.000 2009-03-03 00:00:00.000 2009-03-04 00:00:00.000 2009-03-05 00:00:00.000 2009-03-06 00:00:00.000 2009-03-07 00:00:00.000 2009-03-08 00:00:00.000 2009-03-09 00:00:00.000 2009-03-10 00:00:00.000 .... 2009-07-25 00:00:00.000 2009-07-26 00:00:00.000 2009-07-27 00:00:00.000 2009-07-28 00:00:00.000 2009-07-29 00:00:00.000 2009-07-30 00:00:00.000 2009-07-31 00:00:00.000 2009-08-01 00:00:00.000 (154 row(s) affected)

Prueba esto. Sin bucle, límites de CTE, etc. y podría tener casi cualquier no. de registros generados Administre la combinación cruzada y la parte superior según lo que se requiera.

select top 100000 dateadd(d,incr,''2010-04-01'') as dt from (select incr = row_number() over (order by object_id, column_id), * from ( select a.object_id, a.column_id from sys.all_columns a cross join sys.all_columns b ) as a ) as b

Tenga en cuenta que la anidación es para facilitar el control y la conversión en vistas, etc.

Realmente me gusta la solución de Devio, ya que necesitaba exactamente algo como esto que necesita ejecutarse en SQL Server 2000 (por lo que no puede usar CTE); sin embargo, ¿cómo podría modificarse SOLO para generar fechas que se alineen con un conjunto determinado de días de la semana. Por ejemplo, solo quiero las fechas que coinciden con los lunes, miércoles y viernes o cualquier secuencia particular que elijo según el siguiente número Scheme:

Sunday = 1 Monday = 2 Tuesday = 3 Wednesday = 4 Thursday = 5 Friday = 6 Saturday = 7

Ejemplo:

StartDate = ''2015-04-22'' EndDate = ''2017-04-22'' --2 years worth Filter on: 2,4,6 --Monday, Wednesday, Friday dates only

Lo que intento codificar es agregar dos campos adicionales: día, día_digo Luego filtre la lista generada con una condición ...

Se me ocurrió lo siguiente:

declare @dt datetime, @dtEnd datetime set @dt = getdate() set @dtEnd = dateadd(day, 1095, @dt) select dateadd(day, number, @dt) as Date, DATENAME(DW, dateadd(day, number, @dt)) as Day_Name into #generated_dates from (select distinct number from master.dbo.spt_values where name is null ) n where dateadd(day, number, @dt) < @dtEnd select * from #generated_dates where Day_Name in (''Saturday'', ''Friday'') drop table #generated_dates

Si sus fechas no están separadas más de 2047 días:

declare @dt datetime, @dtEnd datetime set @dt = getdate() set @dtEnd = dateadd(day, 100, @dt) select dateadd(day, number, @dt) from (select number from master.dbo.spt_values where [type] = ''P'' ) n where dateadd(day, number, @dt) < @dtEnd

Actualicé mi respuesta después de varias solicitudes para hacerlo. ¿Por qué?

La respuesta original contenía la subconsulta

select distinct number from master.dbo.spt_values where name is null

que ofrece el mismo resultado, ya que los probé en SQL Server 2008, 2012 y 2016.

Sin embargo, cuando traté de analizar el código que MSSQL internamente cuando consultaba desde spt_values , encontré que las instrucciones SELECT siempre contienen la cláusula WHERE [type]=''[magic code]'' .

Por lo tanto, decidí que aunque la consulta arroja el resultado correcto, ofrece el resultado correcto por razones equivocadas:

Puede haber una versión futura de SQL Server que defina un valor diferente [type] que también tenga NULL como valores para [name] , fuera del rango de 0-2047, o incluso no contiguo, en cuyo caso el resultado sería simplemente incorrecto.

Yo uso lo siguiente:

SELECT * FROM dbo.RangeDate(GETDATE(), DATEADD(d, 365, GETDATE())); -- Generate a range of up to 65,536 contiguous DATES CREATE FUNCTION dbo.RangeDate ( @date1 DATE = NULL , @date2 DATE = NULL ) RETURNS TABLE AS RETURN ( SELECT D = DATEADD(d, A.N, CASE WHEN @date1 <= @date2 THEN @date1 ELSE @date2 END) FROM dbo.RangeSmallInt(0, ABS(DATEDIFF(d, @date1, @date2))) A ); -- Generate a range of up to 65,536 contiguous BIGINTS CREATE FUNCTION dbo.RangeSmallInt ( @num1 BIGINT = NULL , @num2 BIGINT = NULL ) RETURNS TABLE AS RETURN ( WITH Numbers(N) AS ( SELECT N FROM(VALUES (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 16 , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 32 , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 48 , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 64 , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 80 , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 96 , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 112 , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 128 , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 144 , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 160 , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 176 , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 192 , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 208 , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 224 , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 240 , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 256 ) V (N) ) SELECT TOP ( CASE WHEN @num1 IS NOT NULL AND @num2 IS NOT NULL THEN ABS(@num1 - @num2) + 1 ELSE 0 END ) ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) + CASE WHEN @num1 <= @num2 THEN @num1 ELSE @num2 END - 1 FROM Numbers A , Numbers B WHERE ABS(@num1 - @num2) + 1 < 65537 );

No es muy diferente de muchas de las soluciones propuestas, pero hay varias cosas que me gustan al respecto:

  • No se requieren tablas
  • Los argumentos se pueden pasar en cualquier orden
  • El límite de 65.536 fechas es arbitrario y puede ampliarse fácilmente cambiando a una función como RangeInt

crea una tabla temporal con enteros de 0 a la diferencia entre tus dos fechas.

SELECT DATE_ADD(@Start, INTERVAL tmp_int DAY) AS the_date FROM int_table;

Visión de conjunto

Aquí está mi versión (compatible con 2005). Las ventajas de este enfoque son:

  • obtienes una función de propósito general que puedes usar para varios escenarios similares; no restringido a solo fechas
  • el rango no está limitado por el contenido de una tabla existente
  • puede cambiar fácilmente el incremento (por ejemplo, obtener la fecha cada 7 días en lugar de cada día)
  • no requiere acceso a otros catálogos (es decir, maestro)
  • el motor sql puede hacer algo de optimización del TVF que no pudo con una instrucción while
  • generate_series se usa en algunos otros dbs, por lo que esto puede ayudar a que tu código sea instintivamente familiar para un público más amplio

SQL Fiddle: http://sqlfiddle.com/#!6/c3896/1

Código

Una función reutilizable para generar un rango de números basado en parámetros dados:

create function dbo.generate_series ( @start bigint , @stop bigint , @step bigint = 1 , @maxResults bigint = 0 --0=unlimitted ) returns @results table(n bigint) as begin --avoid infinite loop (i.e. where we''re stepping away from stop instead of towards it) if @step = 0 return if @start > @stop and @step > 0 return if @start < @stop and @step < 0 return --ensure we don''t overshoot set @stop = @stop - @step --treat negatives as unlimited set @maxResults = case when @maxResults < 0 then 0 else @maxResults end --generate output ;with myCTE (n,i) as ( --start at the beginning select @start , 1 union all --increment in steps select n + @step , i + 1 from myCTE --ensure we''ve not overshot (accounting for direction of step) where (@maxResults=0 or i<@maxResults) and ( (@step > 0 and n <= @stop) or (@step < 0 and n >= @stop) ) ) insert @results select n from myCTE option (maxrecursion 0) --sadly we can''t use a variable for this; however checks above should mean that we have a finite number of recursions / @maxResults gives users the ability to manually limit this --all good return end

Poniendo esto en práctica para su escenario:

declare @start datetime = ''2013-12-05 09:00'' ,@end datetime = ''2014-03-02 13:00'' --get dates (midnight) --, rounding <12:00 down to 00:00 same day, >=12:00 to 00:00 next day --, incrementing by 1 day select CAST(n as datetime) from dbo.generate_series(cast(@start as bigint), cast(@end as bigint), default, default) --get dates (start time) --, incrementing by 1 day select CAST(n/24.0 as datetime) from dbo.generate_series(cast(@start as float)*24, cast(@end as float)*24, 24, default) --get dates (start time) --, incrementing by 1 hour select CAST(n/24.0 as datetime) from dbo.generate_series(cast(@start as float)*24, cast(@end as float)*24, default, default)

Compatible 2005