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
- Expresiones de tabla comunes: http://technet.microsoft.com/en-us/library/ms190766(v=sql.90).aspx
- Opción MaxRecursion Sugerencia: http://technet.microsoft.com/en-us/library/ms181714(v=sql.90).aspx
- Funciones con valores de tabla: http://technet.microsoft.com/en-us/library/ms191165(v=sql.90).aspx
- Parámetros predeterminados: http://technet.microsoft.com/en-us/library/ms186755(v=sql.90).aspx
- DateTime: http://technet.microsoft.com/en-us/library/ms187819(v=sql.90).aspx
- Casting: http://technet.microsoft.com/en-us/library/aa226054(v=sql.90).aspx