Générer un jeu de résultats de l'incrémentation dates en TSQL
Envisager la nécessité de créer un jeu de résultats de dates. Nous avons de début et dates de fin, et nous tenons à générer une liste de dates entre les deux.
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.
Évaluer la mise en œuvre avec une WHILE
boucle:
DECLARE @dCounter datetime
SELECT @dCounter = @Start
WHILE @dCounter <= @End
BEGIN
INSERT INTO @AllDates VALUES (@dCounter)
SELECT @dCounter=@dCounter+1
END
Question: Comment voulez-vous créer un ensemble de dates qui sont à l'intérieur d'une plage définie par l'utilisateur à l'aide de T-SQL? Supposons SQL 2005+. Si votre réponse est à l'aide de SQL 2008, veuillez marque en tant que telle.
- La version de MSSQL Server utilisez-vous? Si nous donnons des exemples de 2008 et que vous êtes à l'aide de 2000, alors il serait inutile de parler de 2008 options.
- Merci James. Je pensais que j'avais précisé dans la question avec " d'assumer 2005+'.
Vous devez vous connecter pour publier un commentaire.
Si vos dates ne sont plus de 2047 jours d'intervalle:
J'ai mis à jour ma réponse après plusieurs demandes de le faire. Pourquoi?
L'original de la réponse contenue de la sous-requête
qui fournit le même résultat, comme je les ai testés sur SQL Server 2008, 2012 et 2016.
Cependant, comme j'ai essayé d'analyser le code que MSSQL en interne lors de l'interrogation de
spt_values
, j'ai trouvé que leSELECT
déclarations contiennent toujours la clauseWHERE [type]='[magic code]'
.Donc j'ai décidé que, même si la requête retourne un résultat correct, il livre le résultat correct pour de mauvaises raisons:
Il y a peut être une future version de SQL Server qui définit un autre
[type]
valeur qui a égalementNULL
les valeurs pour les[name]
, en dehors de la plage de 0-2047, ou même les non-contigus, auquel cas le résultat serait tout simplement faux.spt_values
jusqu'à maintenant!WHERE type = "P"
, qui est le vecteur Sybase fournir pour les nombres.number * 2048
.select distinct [1].number from master.dbo.spt_values [1] left join master.dbo.spt_values [2] on [2].number = [1].number+1 where [1].number between 0 and 1035 -- and [2].number is null order by [1].number
WHERE type='P'
. @devio, pensez à faire un modifier pour votre réponse.DISTINCT
. Quand un seul Vecteur est utilisé, il n'y a pas de doublons et doncDISTINCT
n'est pas nécessaire. Comme vous l'avez compris dans la mise à jour de réponse.WHERE 0..1035
obtenir 1036 lignes,WHERE 1..99
obtenir 99 lignes.spt
est synonyme de Système de Table de Procédure,type
identifie la Table, ou Vecteur.spt_values
est utilisé par tout le système de procs, c'est donc la garantie d'être mis en cache.Lle-ci utilise une expression de table commune récursive (SQL Server 2005+):
DATEADD
deux fois, le mettre dans unCROSS APPLY
après laFROM
alors la référence dans leWHERE
etSELECT
clauses. E. g.CROSS APPLY ( SELECT DATEADD(dd, 1, t.date) [Value] ) [My Added Date Reference]
.Pour que cette méthode fonctionne, vous devez faire cette fois une table de configuration:
Une fois que les Nombres de la table est mis en place, utilisez la requête:
de les capturer à faire:
de sortie:
@KM de réponse crée un tableau de nombres premiers, et l'utilise pour sélectionner une plage de dates. Pour faire la même chose sans le temporaire table de nombres:
Test bien sûr, si vous le faites souvent, une table permanente, peut-être plus performant.
La requête ci-dessus est une version modifiée de cet article, qui traite de la génération de séquences et donne de nombreuses méthodes possibles. J'ai bien aimé celui-ci car il ne permet pas de créer une table temporaire, et n'est pas limité au nombre d'éléments dans le
sys.objects
table.De l'essayer. Pas de Boucle, CTE de limites, etc. et vous pourriez avoir à peu près tout pas. de documents générés. Gérer la croix-jointure et de haut en fonction de ce qui est requis.
Veuillez noter que l'imbrication est pour faciliter le contrôle et la conversion en points de vue, etc.
Cette solution est basée sur une merveilleuse réponse à la même question pour MySQL. Il est également très performant sur MSSQL. https://stackoverflow.com/a/2157776/466677
fonctionne uniquement pour les dates dans le passé, pour les dates dans le futur changement de signe dans la fonction DATEADD. La requête ne fonctionne que pour SQL Server 2008+ mais pourrait être réécrit également pour 2005 par le remplacement de "sélection de valeurs" construire avec les syndicats.
Une autre option est de créer de la fonction correspondante dans .NET. Voici à quoi ça ressemble:
C'est un prototype et il peut être fait beaucoup plus intelligent, mais illustre bien l'idée. De mon expérience, pour une petite à modérée des intervalles de temps (comme un couple de d'années) cette fonction fonctionne mieux que celui mis en œuvre en T-SQL. Une autre fonctionnalité intéressante de la version CLR est qu'il ne crée une table temporaire.
Aperçu
Voici ma version (2005 compatible). Les avantages de cette approche sont:
SQL Violon: http://sqlfiddle.com/#!6/c3896/1
Code
Réutilisables fonction pour générer une gamme de nombres basés sur des paramètres donnés:
Met ce à utiliser pour votre scénario:
2005 Compatible
J'aime CTE comme il est facile de lire et d'entretien
N'oubliez pas de mettre MAXRECURSION
créer une table temporaire avec des entiers de 0 à la différence entre les deux dates.
J'utilise la suite:
Ce n'est pas différent de la plupart des solutions proposées déjà, mais il y a plusieurs choses que j'aime:
Celui-ci devrait fonctionner.
select Top 1000 DATEADD(d, ROW_NUMBER() OVER(ORDER BY Id),getdate()) de sysobjects
Ce que je vous recommande: créer une table auxiliaire des nombres et les utiliser pour générer votre liste de dates. Vous pouvez également utiliser une expression de table commune récursive, mais qui ne peuvent pas effectuer aussi bien que de se joindre à une table auxiliaire de nombres. Voir SQL, Auxiliaire tableau de nombres pour info sur les deux options.
Bien que j'aime vraiment KM de la solution ci-dessus (+1), je dois remettre en question votre "pas de boucle" hypothèse plausible compte tenu de la date de plages que votre application fonctionnera avec, d'avoir une boucle ne devrait pas vraiment être cher. L'astuce consiste à strore les résultats de la boucle dans la mise en scène/tableau de cache, de sorte que les très grands ensembles de requêtes ne pas ralentir le système en le re-calcul de la même date exacte. E. g. chaque requête ne calcule/caches les plages de dates qui ne sont PAS déjà dans le cache et qu'il a besoin (et pré-remplir le tableau avec une certaine date réaliste gamme comme ~2 ans à l'avance, avec la gamme déterminé par votre application besoins de l'entreprise).
La meilleure réponse est probablement d'utiliser le CTE, mais il n'y a aucune garantie que vous êtes en mesure de les utiliser. Dans mon cas, j'ai eu insérer cette liste à l'intérieur d'une requête existante créé dinamically par un générateur de requêtes...ne pouvait pas l'utiliser CTE ni de procédures stockées.
Donc, la réponse de Devio a été vraiment utile, mais j'ai dû le modifier pour travailler dans mon environnement.
Dans le cas où vous n'avez pas accès au master db, vous pouvez utiliser une autre table dans votre base de données. Comme pour l'exemple précédent, de la date maximale de la gamme est donnée par le nombre de lignes à l'intérieur de la table choisie.
Dans mon exemple difficile, à l'aide de la fonction row_number, vous pouvez utiliser des tables sans une réelle int colonne.
Aime vraiment Devio la solution que j'ai besoin exactement quelque chose comme ce qui doit s'exécuter sur un Serveur SQL server 2000 (donc ne peut pas utiliser CTE) cependant, comment pourrait-il être modifié pour produire SEULEMENT des dates qui correspondent à un ensemble donné de jours de la semaine. Par exemple, je veux seulement les dates qui tombent en ligne avec les lundi, mercredi et vendredi ou quelle que soit la séquence particulière-je choisir basé sur le numéro suivant le Schéma:
Exemple:
Ce que je suis en train de code est à ajouter deux champs supplémentaires: jour,day_code
Ensuite filtrer la liste générée avec une condition...
Je suis venu avec les éléments suivants: