Generieren Sie eine Ergebnismenge von inkrementierenden Daten in SQL

Im Folgenden wird ein rekursiver CTE (SQL Server 2005+) verwendet:

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.

Damit diese Methode funktionieren kann, müssen Sie diese Einstellung für einen Zeitplan vornehmen:

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)

Sobald die Numbers-Tabelle eingerichtet ist, verwenden Sie diese Abfrage:

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

um sie zu fangen machen:

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

ausgabe:

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)

Die Antwort von @ KM erstellt zuerst eine Zahlentabelle und verwendet diese zur Auswahl eines Datumsbereichs. Um dies auch ohne die Tabelle mit temporären Nummern zu tun:

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 ;

Testen Sie natürlich, wenn Sie dies häufig tun, ist eine permanente Tabelle möglicherweise leistungsfähiger.

Die obige Abfrage ist eine modifizierte Version aus diesem Artikel , in der das Generieren von Sequenzen beschrieben und viele mögliche Methoden angegeben werden. Ich mochte dieses, da es keine temporäre Tabelle erstellt und nicht auf die Anzahl der Elemente in der sys.objects-Tabelle beschränkt ist.

Versuche dies. Kein Looping, CTE-Limits usw. und Sie könnten so gut wie keine Nein haben. von Datensätzen generiert. Verwalten Sie den Cross-Join und das Top je nach Bedarf.

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

Bitte beachten Sie, dass die Schachtelung zur einfacheren Kontrolle und Umwandlung in Ansichten usw. dient.

Diese Lösung basiert auf einer erstaunlichen Antwort auf dieselbe Frage für MySQL. Es ist auch sehr performant für MSSQL. https://stackoverflow.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

funktioniert nur für Datumsangaben in der Vergangenheit, für Datumsangaben in der zukünftigen Änderung Minuszeichen in der DATEADD-Funktion. Die Abfrage funktioniert nur für SQL Server 2008+, könnte jedoch auch für 2005 umgeschrieben werden, indem das Konstrukt "select from values" durch Vereinigungen ersetzt wird.

Eine andere Option ist das Erstellen einer entsprechenden Funktion in .NET. So sieht es aus:

[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;
}

Dies ist im Grunde ein Prototyp und es kann viel intelligenter gemacht werden, veranschaulicht aber die Idee. Aus meiner Erfahrung ist diese Funktion für eine kleine bis mittlere Zeitspanne (wie in einigen Jahren) besser als die in T-SQL implementierte. Eine weitere schöne Funktion der CLR-Version ist, dass keine temporäre Tabelle erstellt wird.

Überblick

Hier ist meine Version (2005 kompatibel). Die Vorteile dieses Ansatzes sind:

• sie erhalten eine allgemeine Funktion, die Sie für eine Reihe ähnlicher Szenarien verwenden können. nicht nur auf Termine beschränkt
• der Bereich ist nicht durch den Inhalt einer vorhandenen Tabelle begrenzt
• sie können das Inkrement einfach ändern (z. B. alle 7 Tage statt jeden Tag).
• sie benötigen keinen Zugriff auf andere Kataloge (d. h. Master)
• die SQL-Engine ist in der Lage, die TVF zu optimieren, was mit einer while-Anweisung nicht möglich war
• diese Methode wird in einigen anderen DBS verwendet. Dies kann dazu beitragen, dass Ihr Code instinktiv einem größeren Publikum bekannt wird

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

Code

Eine wiederverwendbare Funktion zum Generieren eines Zahlenbereichs basierend auf angegebenen Parametern:

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

Setzen Sie dies für Ihr Szenario ein:

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)

2005 kompatibel

• Allgemeine Tabellenausdrücke: http://technet.Microsoft.com/de-de/library/ms190766(v=sql.90).aspx
• Option MaxRecursion Hinweis: http://technet.Microsoft.com/de-de/library/ms181714(v=sql.90).aspx
• Tabellenwertfunktionen: http://technet.Microsoft.com/en-us/library/ms191165(v=sql.90).aspx
• Standardparameter: 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

Ich benutze folgendes:

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

Es unterscheidet sich nicht allzu sehr von vielen der bereits vorgeschlagenen Lösungen, aber es gibt einige Dinge, die ich daran mag:

• Keine tische erforderlich
• Argumente können in beliebiger Reihenfolge übergeben werden
• Das Limit von 65.536 Datumsangaben ist willkürlich und kann durch den Wechsel zu einer Funktion wie RangeInt leicht erweitert werden

Ich mag CTE, da er einfach zu lesen und zu warten ist 

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

Vergessen Sie nicht, MAXRECURSION einzustellen  

erstellen Sie eine temporäre Tabelle mit Ganzzahlen von 0 bis zur Differenz zwischen Ihren beiden Datumsangaben.

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

Was ich empfehlen würde: Erstellen Sie eine Hilfstabelle mit Zahlen und erstellen Sie daraus eine Liste mit Datumsangaben. Sie können auch einen rekursiven CTE verwenden, der jedoch möglicherweise nicht so gut funktioniert wie eine Verknüpfung mit einer Hilfstabelle mit Zahlen. Weitere Informationen zu beiden Optionen finden Sie unter SQL, Hilfstabelle der Nummern . 

Ich mag KMs Lösung über (+1) wirklich sehr, aber ich muss Ihre "No Loop" -Annahme in Frage stellen - angesichts der plausiblen Datumsbereiche, mit denen Ihre App funktionieren wird, sollte eine Loop nicht wirklich so teuer sein. Der Haupttrick besteht darin, die Ergebnisse der Schleife in der Staging/Cache-Tabelle zu speichern, so dass extrem große Abfragesätze das System nicht verlangsamen, indem dieselben exakten Daten erneut berechnet werden. Z.B. Jede Abfrage berechnet/speichert nur die Datumsbereiche, die NICHT im Cache gespeichert sind und die sie benötigt (und füllt die Tabelle mit einem realistischen Datumsbereich wie ~ 2 Jahre im Voraus, wobei der Bereich von den Anforderungen Ihres Anwendungsgeschäfts bestimmt wird).

Das sollte funktionieren.

wählen Sie Top 1000 DATEADD (d, ROW_NUMBER () OVER (ORDER BY Id), getdate ()) aus den Sysobjects aus

Wirklich wie die Lösung von Devio, da ich genau so etwas brauchte, das auf SQL Server 2000 ausgeführt werden muss (also kein CTE verwenden kann). Wie könnte es jedoch geändert werden, um NUR Datumsangaben zu generieren, die mit einem bestimmten Satz von Wochentagen übereinstimmen. Zum Beispiel möchte ich nur die Daten, die mit Montag, Mittwoch und Freitag übereinstimmen, oder mit einer beliebigen Reihenfolge, die ich basierend auf dem folgenden Zahlenschema auswähle:

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

Beispiel: 

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

Was ich versuche zu kodieren, ist, zwei zusätzliche Felder hinzuzufügen: day, day_code Dann die generierte Liste mit einer Bedingung filtern ...

Ich habe mir folgendes ausgedacht:

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

Die beste Antwort ist wahrscheinlich die Verwendung des CTE, aber es gibt keine Garantie dafür, dass Sie dies tun können. In meinem Fall musste ich diese Liste in eine vorhandene Abfrage einfügen, die von einem Abfragegenerator auf dinamische Weise erstellt wurde ... CTE und gespeicherte Prozeduren konnten nicht verwendet werden.

Die Antwort von Devio war also wirklich nützlich, aber ich musste sie an meine Umgebung anpassen.

Falls Sie keinen Zugriff auf die Master-Datenbank haben, können Sie eine andere Tabelle in Ihrer Datenbank verwenden. Wie im vorherigen Beispiel wird der maximale Datumsbereich durch die Anzahl der Zeilen in der ausgewählten Tabelle angegeben. 

In meinem Beispiel können Sie mit row_number Tabellen ohne eine tatsächliche int-Spalte verwenden. 

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