Generare un set di risultati con date di incremento in TSQL

Considera la necessità di creare un set di risultati. Abbiamo date di inizio e fine e vorremmo generare un elenco di date intermedie.

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. 

Considera l’implementazione corrente con un ciclo WHILE :

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

Domanda: come creeresti un insieme di date all’interno di un intervallo definito dall’utente utilizzando T-SQL? Assumere SQL 2005+. Se la risposta sta utilizzando le funzionalità di SQL 2008, contrassegnare come tali.

Solutions Collecting From Web of "Generare un set di risultati con date di incremento in TSQL"

Se le date non sono più di 2047 giorni di distanza:

 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 

Ho aggiornato la mia risposta dopo diverse richieste di farlo. Perché?

La risposta originale conteneva la sottoquery

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

che offre lo stesso risultato, come li ho testati su SQL Server 2008, 2012 e 2016.

Tuttavia, mentre cercavo di analizzare internamente il codice MSSQL quando interrogavo da spt_values , ho trovato che le istruzioni SELECT contengono sempre la clausola WHERE [type]='[magic code]' .

Pertanto ho deciso che sebbene la query restituisca il risultato corretto, fornisce il risultato corretto per motivi errati:

Potrebbe esserci una versione futura di SQL Server che definisce un diverso [type] valore che ha anche NULL come valori per [name] , al di fuori dell'intervallo 0-2047 o anche non contigui, nel qual caso il risultato sarebbe semplicemente sbagliato.

Di seguito viene utilizzato un CTE ricorsivo (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. 

Affinché questo metodo funzioni, è necessario eseguire questa impostazione con una sola volta:

 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 volta impostata la tabella Numbers, utilizzare questa query:

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

per catturarli fai:

 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 

produzione:

 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) 

@ La risposta di KM crea prima una tabella di numeri e la usa per selezionare un intervallo di date. Per fare lo stesso senza la tabella dei numeri temporanei:

 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 ; 

Prova ovviamente, se lo fai spesso, un tavolo permanente potrebbe essere più performante.

La query sopra è una versione modificata di questo articolo , che tratta della generazione di sequenze e fornisce molti metodi possibili. Mi è piaciuto questo perché non crea una tabella temporanea e non è limitato al numero di elementi nella tabella sys.objects .

Prova questo. Nessun loop, limiti CTE, ecc. E potresti avere praticamente tutti i no. di record generati. Gestisci il cross-join e top in base a ciò che è richiesto.

 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 

Si noti che l’annidamento facilita il controllo e la conversione in viste, ecc.

Un’altra opzione è creare la funzione corrispondente in .NET. Ecco come appare:

 [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 res = new List(); 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; } 

Questo è fondamentalmente un prototipo e può essere reso molto più intelligente, ma illustra l'idea. Dalla mia esperienza, per periodi di tempo da piccoli a moderati (come un paio d'anni) questa funzione si comporta meglio di quella implementata in T-SQL. Un'altra caratteristica interessante della versione CLR è che non crea una tabella temporanea.

Panoramica

Ecco la mia versione (compatibile 2005). I vantaggi di questo approccio sono:

  • si ottiene una funzione generica che è ansible utilizzare per un numero di scenari simili; non limitato a solo date
  • l’intervallo non è limitato dal contenuto di una tabella esistente
  • puoi facilmente modificare l’incremento (es. ottieni la data ogni 7 giorni invece che ogni giorno)
  • non è necessario accedere ad altri cataloghi (es. master)
  • il motore sql è in grado di fare un po ‘di ottimizzazione del TVF che non potrebbe con un attimo
  • generate_series è usato in altri dbs, quindi questo può aiutare a rendere il codice istintivamente familiare a un pubblico più ampio

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

Codice

Una funzione riutilizzabile per generare un intervallo di numeri in base a determinati parametri:

 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 (ie 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 

Mettendo questo da usare per il tuo scenario:

 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) 

Compatibile 2005

Questa soluzione si basa sulla meravigliosa risposta della stessa domanda per MySQL. È anche molto performante su MSSQL. https://stackoverflow.com/a/2157776/466677

 select DateGenerator.DateValue from ( select DATEADD(day, - (aa + (10 * ba) + (100 * ca) + (1000 * da)), CONVERT(DATE, GETDATE()) ) as DateValue from (select aa from (values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) as a(a)) as a cross join (select ba from (values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) as b(a)) as b cross join (select ca from (values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) as c(a)) as c cross join (select da 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 

funziona solo per le date in passato, per le date in modifica futura meno accedi alla funzione DATEADD. Query funziona solo per SQL Server 2008+ ma potrebbe essere riscritto anche per il 2005 sostituendo il costrutto “select from values” con i sindacati.

crea una tabella temporanea con numeri interi da 0 alla differenza tra le tue due date.

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

Io uso il seguente:

 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, AN, 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 ); 

Non è poi così diverso da molte delle soluzioni proposte ma ci sono molte cose che mi piacciono:

  • Non sono richieste tabelle
  • Gli argomenti possono essere passati in qualsiasi ordine
  • Il limite di 65.536 date è arbitrario e può essere facilmente espanso passando a una funzione come RangeInt

Mi piace il CTE in quanto è di facile lettura e manutenzione

 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); 

Non dimenticare di impostare MAXRECURSION

Cosa consiglierei: crea una tabella ausiliaria di numeri e usala per generare il tuo elenco di date. Puoi anche usare un CTE ricorsivo, ma questo potrebbe non funzionare come unirsi a una tabella ausiliaria di numeri. Vedi SQL, tabella ausiliaria di numeri per informazioni su entrambe le opzioni.

Anche se mi piace molto la soluzione di KM sopra (+1), devo mettere in dubbio la tua ipotesi di “no loop” – dati i plausibili intervalli di date con cui la tua app funzionerà, avere un ciclo non dovrebbe essere davvero così costoso. Il trucco principale è quello di strorizzare i risultati del ciclo nella tabella di staging / cache, in modo che serie di query estremamente grandi non rallentino il sistema ricalcolando le stesse date esatte. Ad esempio, ogni query calcola / memorizza nella cache gli intervalli di date che NON sono già nella cache e di cui ha bisogno (e pre-popolano la tabella con un intervallo di date realistico come ~ 2 anni in anticipo, con intervallo determinato dalle esigenze aziendali dell’applicazione).

La risposta migliore è probabilmente usare il CTE, ma non è garantito che tu possa usarlo. Nel mio caso, ho dovuto inserire questa lista all’interno di una query esistente creata dynamicmente da un generatore di query … non è stato ansible utilizzare CTE né stored procedure.

Quindi, la risposta di Devio è stata davvero utile, ma ho dovuto modificarla per funzionare nel mio ambiente.

Nel caso in cui non si abbia accesso al master db, è ansible utilizzare un’altra tabella nel proprio database. Come nell’esempio precedente, l’intervallo di date massimo è dato dal numero di righe all’interno della tabella scelta.

Nel mio esempio difficile, utilizzando il numero_riga, è ansible utilizzare le tabelle senza una colonna int effettiva.

 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 

Mi piace molto la soluzione di Devio in quanto avevo bisogno di qualcosa di simile che dovesse funzionare su SQL Server 2000 (quindi non posso usare CTE), tuttavia, come potrebbe essere modificato SOLO per generare date che si allineano con un determinato set di giorni della settimana. Ad esempio, desidero solo le date che coincidono con il lunedì, il mercoledì e il venerdì o qualsiasi altra sequenza che scelgo in base al seguente schema:

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

Esempio:

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

Quello che sto cercando di codificare è aggiungere due campi aggiuntivi: day, day_code Quindi filtrare l’elenco generato con una condizione …

Ho trovato il seguente:

 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 

Questo dovrebbe funzionare.

seleziona Inizio 1000 DATEADD (d, ROW_NUMBER () OVER (ORDER BY Id), getdate ()) da sysobjects