MS SQL Server
  1. MS SQL Server
  3. Rechercher des intervalles de date conflictuels à l'aide de SQL
Intereting Posts
procédure stockée pour différentes bases de données Pourquoi je ne peux pas modifier la database dynamicment SQL Server 2008 NamedParameterJDBCTemplate et syntaxe de fonction sur différents SGBDR Comment searchr des lignes dans une table avec des valeurs de string similaires Roll up count dans la table hiérarchique (table auto-référentielle) Comment puis-je empêcher l'insertion de données en double dans une table SQL Server? Comment prendre des instantanés quotidiens d'une table Comparez une ligne ayant une string séparée par une virgule à une ligne d'une autre table, sortingez-la puis insérez la ligne dans une troisième table TSQL Transpose une seule colonne en ligne Puis-je utiliser la réplication pour ce cas? Pourquoi cette mise à jour génère-t-elle une erreur même si la command Alter Table doit être terminée? Le plan de maintenance SQL envoie du courrier lorsque aucun courrier n'est configuré Existe-t-il un moyen d'optimiser une requête de comptage complexe par rapport à des données volumineuses? Instruction SQL pour supprimer des duplicates en fonction d'un champ spécifique Essayer de faire un "top 1 / order by" dans une jointure interne avec T-SQL / SQL Server

Rechercher des intervalles de date conflictuels à l'aide de SQL

Supposons que j'ai la table suivante dans Sql Server 2008: 

ItemId StartDate EndDate 1 NULL 2011-01-15 2 2011-01-16 2011-01-25 3 2011-01-26 NULL

Comme vous pouvez le voir, cette table contient des colonnes StartDate et EndDate. Je veux valider datatables dans ces colonnes. Les intervalles ne peuvent pas entrer en conflit les uns avec les autres. Ainsi, le tableau ci-dessus est valide, mais la table suivante n'est pas valide car la première ligne a une date de fin supérieure à StartDate dans la deuxième ligne. 

 ItemId StartDate EndDate 1 NULL 2011-01-17 2 2011-01-16 2011-01-25 3 2011-01-26 NULL

NULL signifie l'infini ici.

Pourriez-vous m'aider à écrire un script pour la validation des données?

[La deuxième tâche]

Merci pour les réponses. J'ai une complication. Supposons que j'ai une telle table: 

 ItemId IntervalId StartDate EndDate 1 1 NULL 2011-01-15 2 1 2011-01-16 2011-01-25 3 1 2011-01-26 NULL 4 2 NULL 2011-01-17 5 2 2011-01-16 2011-01-25 6 2 2011-01-26 NULL

Ici, je veux valider les intervalles dans un groupe de IntervalId , mais pas dans la table entière. Donc, l'Intervalle 1 sera valide, mais l'Intervalle 2 sera invalide.

Et aussi. Est-il possible d'append une contrainte à la table afin d'éviter de tels loggings invalides?

[Solution finale]

J'ai créé une fonction pour vérifier si l'intervalle est en conflit: 

 CREATE FUNCTION [dbo].[fnIntervalConflict] ( @intervalId INT, @originalItemId INT, @startDate DATETIME, @endDate DATETIME ) RETURNS BIT AS BEGIN SET @startDate = ISNULL(@startDate,'1/1/1753 12:00:00 AM') SET @endDate = ISNULL(@endDate,'12/31/9999 11:59:59 PM') DECLARE @conflict BIT = 0 SELECT TOP 1 @conflict = 1 FROM Items WHERE IntervalId = @intervalId AND ItemId <> @originalItemId AND ( (ISNULL(StartDate,'1/1/1753 12:00:00 AM') >= @startDate AND ISNULL(StartDate,'1/1/1753 12:00:00 AM') <= @endDate) OR (ISNULL(EndDate,'12/31/9999 11:59:59 PM') >= @startDate AND ISNULL(EndDate,'12/31/9999 11:59:59 PM') <= @endDate) ) RETURN @conflict END

Et puis j'ai ajouté 2 contraintes à ma table: 

 ALTER TABLE dbo.Items ADD CONSTRAINT CK_Items_Dates CHECK (StartDate IS NULL OR EndDate IS NULL OR StartDate <= EndDate) GO

et 

 ALTER TABLE dbo.Items ADD CONSTRAINT CK_Items_ValidInterval CHECK (([dbo].[fnIntervalConflict]([IntervalId], ItemId,[StartDate],[EndDate])=(0))) GO

Je sais, la deuxième contrainte ralentit les opérations d'insertion et de mise à jour, mais ce n'est pas très important pour mon application. Et aussi, maintenant je peux appeler la fonction fnIntervalConflict de mon code d'application avant les insertions et les mises à jour de données dans la table. 

 declare @T table (ItemId int, IntervalID int, StartDate datetime, EndDate datetime) insert into @T select 1, 1, NULL, '2011-01-15' union all select 2, 1, '2011-01-16', '2011-01-25' union all select 3, 1, '2011-01-26', NULL union all select 4, 2, NULL, '2011-01-17' union all select 5, 2, '2011-01-16', '2011-01-25' union all select 6, 2, '2011-01-26', NULL select T1.* from @T as T1 inner join @T as T2 on coalesce(T1.StartDate, '1753-01-01') < coalesce(T2.EndDate, '9999-12-31') and coalesce(T1.EndDate, '9999-12-31') > coalesce(T2.StartDate, '1753-01-01') and T1.IntervalID = T2.IntervalID and T1.ItemId <> T2.ItemId

Résultat: 

 ItemId IntervalID StartDate EndDate ----------- ----------- ----------------------- ----------------------- 5 2 2011-01-16 00:00:00.000 2011-01-25 00:00:00.000 4 2 NULL 2011-01-17 00:00:00.000

Quelque chose comme ça devrait vous donner toutes les périodes de chevauchement 

 SELECT * FROM mytable t1 JOIN mytable t2 ON t1.EndDate>t2.StartDate AND t1.StartDate < t2.StartDate

Edité pour Adrians commentaire ci-dessous

Cela vous donnera les lignes qui sont incorrectes.

ROW_NUMBER() ajouté car je ne savais pas si toutes les inputs étaient dans l'ordre. 

 -- Testdata declare @date datetime = '2011-01-17' ;with yourTable(itemID, startDate, endDate) as ( SELECT 1, NULL, @date UNION ALL SELECT 2, dateadd(day, -1, @date), DATEADD(day, 10, @date) UNION ALL SELECT 3, DATEADD(day, 60, @date), NULL ) -- End testdata ,tmp as ( select * ,ROW_NUMBER() OVER(order by startDate) as rowno from yourTable ) select * from tmp t1 left join tmp t2 on t1.rowno = t2.rowno - 1 where t1.endDate > t2.startDate

EDIT: Comme pour la question mise à jour:

Ajoutez simplement une clause PARTITION BY à la requête ROW_NUMBER() et modifiez la jointure. 

 -- Testdata declare @date datetime = '2011-01-17' ;with yourTable(itemID, startDate, endDate, intervalID) as ( SELECT 1, NULL, @date, 1 UNION ALL SELECT 2, dateadd(day, 1, @date), DATEADD(day, 10, @date),1 UNION ALL SELECT 3, DATEADD(day, 60, @date), NULL, 1 UNION ALL SELECT 4, NULL, @date, 2 UNION ALL SELECT 5, dateadd(day, -1, @date), DATEADD(day, 10, @date),2 UNION ALL SELECT 6, DATEADD(day, 60, @date), NULL, 2 ) -- End testdata ,tmp as ( select * ,ROW_NUMBER() OVER(partition by intervalID order by startDate) as rowno from yourTable ) select * from tmp t1 left join tmp t2 on t1.rowno = t2.rowno - 1 and t1.intervalID = t2.intervalID where t1.endDate > t2.startDate

Pas directement lié à l'OP, mais depuis Adrian a exprimé un intérêt. Voici une table dont SQL Server conserve l'intégrité, garantissant qu'une seule valeur valide est présente à tout moment. Dans ce cas, j'ai affaire à un tableau courant / historique, mais l'exemple peut aussi être modifié pour fonctionner avec des données futures (bien que dans ce cas, vous ne puissiez pas avoir la vue indexée, et vous devez écrire directement la fusion) plutôt que de maintenir les triggersurs).

Dans ce cas particulier, j'ai affaire à une table de liens dont je veux suivre l'historique. D'abord, les tables que nous lions: 

 create table dbo.Clients ( ClientID int IDENTITY(1,1) not null, Name varchar(50) not null, /* Other columns */ constraint PK_Clients PRIMARY KEY (ClientID) ) go create table dbo.DataItems ( DataItemID int IDENTITY(1,1) not null, Name varchar(50) not null, /* Other columns */ constraint PK_DataItems PRIMARY KEY (DataItemID), constraint UQ_DataItem_Names UNIQUE (Name) ) go

Maintenant, si nous construisions une table normale, nous aurions ce qui suit ( Ne lancez pas celui-ci ): 

 create table dbo.ClientAnswers ( ClientID int not null, DataItemID int not null, IntValue int not null, Comment varchar(max) null, constraint PK_ClientAnswers PRIMARY KEY (ClientID,DataItemID), constraint FK_ClientAnswers_Clients FOREIGN KEY (ClientID) references dbo.Clients (ClientID), constraint FK_ClientAnswers_DataItems FOREIGN KEY (DataItemID) references dbo.DataItems (DataItemID) )

Mais, nous voulons une table qui puisse représenter une histoire complète. En particulier, nous voulons concevoir la structure de sorte que les périodes de chevauchement ne puissent jamais apparaître dans la database. Nous soaps toujours quel logging était valide à un moment donné: 

 create table dbo.ClientAnswerHistories ( ClientID int not null, DataItemID int not null, IntValue int null, Comment varchar(max) null, /* Temporal columns */ Deleted bit not null, ValidFrom datetime2 null, ValidTo datetime2 null, constraint UQ_ClientAnswerHistories_ValidFrom UNIQUE (ClientID,DataItemID,ValidFrom), constraint UQ_ClientAnswerHistories_ValidTo UNIQUE (ClientID,DataItemID,ValidTo), constraint CK_ClientAnswerHistories_NoTimeTravel CHECK (ValidFrom < ValidTo), constraint FK_ClientAnswerHistories_Clients FOREIGN KEY (ClientID) references dbo.Clients (ClientID), constraint FK_ClientAnswerHistories_DataItems FOREIGN KEY (DataItemID) references dbo.DataItems (DataItemID), constraint FK_ClientAnswerHistories_Prev FOREIGN KEY (ClientID,DataItemID,ValidFrom) references dbo.ClientAnswerHistories (ClientID,DataItemID,ValidTo), constraint FK_ClientAnswerHistories_Next FOREIGN KEY (ClientID,DataItemID,ValidTo) references dbo.ClientAnswerHistories (ClientID,DataItemID,ValidFrom), constraint CK_ClientAnswerHistory_DeletionNull CHECK ( Deleted = 0 or ( IntValue is null and Comment is null )), constraint CK_ClientAnswerHistory_IntValueNotNull CHECK (Deleted=1 or IntValue is not null) ) go

C'est beaucoup de contraintes. La seule façon de maintenir cette table est de faire des instructions de fusion (voir les exemples ci-dessous, et essayez de raisonner sur la raison de vous-même). Nous allons maintenant créer une vue qui imite cette table ClientAnswers définie ci-dessus: 

 create view dbo.ClientAnswers with schemabinding as select ClientID, DataItemID, ISNULL(IntValue,0) as IntValue, Comment from dbo.ClientAnswerHistories where Deleted = 0 and ValidTo is null go create unique clustered index PK_ClientAnswers on dbo.ClientAnswers (ClientID,DataItemID) go

Et nous avons la contrainte PK que nous voulions initialement. Nous avons également utilisé ISNULL pour rétablir la not null de la colonne IntValue (même si les contraintes de vérification le garantissent déjà, SQL Server ne peut pas extraire cette information). Si nous travaillons avec un ORM, nous le laissons cibler ClientAnswers , et l'historique est automatiquement construit. Ensuite, nous pouvons avoir une fonction qui nous permet de remonter le time: 

 create function dbo.ClientAnswers_At ( @At datetime2 ) returns table with schemabinding as return ( select ClientID, DataItemID, ISNULL(IntValue,0) as IntValue, Comment from dbo.ClientAnswerHistories where Deleted = 0 and (ValidFrom is null or ValidFrom <= @At) and (ValidTo is null or ValidTo > @At) ) go

Et enfin, nous avons besoin des triggersurs sur ClientAnswers qui construisent cet historique. Nous devons utiliser des instructions de fusion, car nous devons insert simultanément de nouvelles lignes et mettre à jour la ligne "valide" précédente pour la dater avec une nouvelle valeur ValidTo. 

 create sortinggger T_ClientAnswers_I on dbo.ClientAnswers instead of insert as set nocount on ;with Dup as ( select i.ClientID,i.DataItemID,i.IntValue,i.Comment,CASE WHEN cah.ClientID is not null THEN 1 ELSE 0 END as PrevDeleted,t.Dupl from inserted i left join dbo.ClientAnswerHistories cah on i.ClientID = cah.ClientID and i.DataItemID = cah.DataItemID and cah.ValidTo is null and cah.Deleted = 1 cross join (select 0 union all select 1) t(Dupl) ) merge into dbo.ClientAnswerHistories cah using Dup on cah.ClientID = Dup.ClientID and cah.DataItemID = Dup.DataItemID and cah.ValidTo is null and Dup.Dupl = 0 and Dup.PrevDeleted = 1 when matched then update set ValidTo = SYSDATETIME() when not matched and Dup.Dupl=1 then insert (ClientID,DataItemID,IntValue,Comment,Deleted,ValidFrom) values (Dup.ClientID,Dup.DataItemID,Dup.IntValue,Dup.Comment,0,CASE WHEN Dup.PrevDeleted=1 THEN SYSDATETIME() END); go create sortinggger T_ClientAnswers_U on dbo.ClientAnswers instead of update as set nocount on ;with Dup as ( select i.ClientID,i.DataItemID,i.IntValue,i.Comment,t.Dupl from inserted i cross join (select 0 union all select 1) t(Dupl) ) merge into dbo.ClientAnswerHistories cah using Dup on cah.ClientID = Dup.ClientID and cah.DataItemID = Dup.DataItemID and cah.ValidTo is null and Dup.Dupl = 0 when matched then update set ValidTo = SYSDATETIME() when not matched then insert (ClientID,DataItemID,IntValue,Comment,Deleted,ValidFrom) values (Dup.ClientID,Dup.DataItemID,Dup.IntValue,Dup.Comment,0,SYSDATETIME()); go create sortinggger T_ClientAnswers_D on dbo.ClientAnswers instead of delete as set nocount on ;with Dup as ( select d.ClientID,d.DataItemID,t.Dupl from deleted d cross join (select 0 union all select 1) t(Dupl) ) merge into dbo.ClientAnswerHistories cah using Dup on cah.ClientID = Dup.ClientID and cah.DataItemID = Dup.DataItemID and cah.ValidTo is null and Dup.Dupl = 0 when matched then update set ValidTo = SYSDATETIME() when not matched then insert (ClientID,DataItemID,Deleted,ValidFrom) values (Dup.ClientID,Dup.DataItemID,1,SYSDATETIME()); go

Évidemment, j'aurais pu build une table plus simple (pas une table de jointure), mais ceci est mon exemple standard (bien qu'il m ait fallu du time pour le rebuild – j'ai oublié le set nocount on instructions pendant un moment). Mais la force ici est que, la table de base, ClientAnswerHistories est incapable de stocker des plages de time se chevauchant pour les mêmes valeurs ClientID et DataItemID .

Les choses deviennent plus complexes lorsque vous devez gérer des foreign keys temporelles.

Bien sûr, si vous ne voulez pas de réels trous, vous pouvez supprimer la colonne Deleted (et les vérifications associées), ne not null vraiment annuler les colonnes not null , modifier le triggersur d' insert pour effectuer une insertion simple et créer le triggersur de delete triggersr une erreur à la place.

J'ai toujours pris une approche légèrement différente de la design si j'ai des données qui ne doivent jamais avoir d'intervalles qui se chevauchent … à savoir ne pas stocker les intervalles, mais seulement les heures de début. Ensuite, ayez une vue qui aide à afficher les intervalles. 

 CREATE TABLE intervalStarts ( ItemId int, IntervalId int, StartDate datetime ) CREATE VIEW intervals AS with cte as ( select ItemId, IntervalId, StartDate, row_number() over(partition by IntervalId order by isnull(StartDate,'1753-01-01')) row from intervalStarts ) select c1.ItemId, c1.IntervalId, c1.StartDate, dateadd(dd,-1,c2.StartDate) as 'EndDate' from cte c1 left join cte c2 on c1.IntervalId=c2.IntervalId and c1.row=c2.row-1

Ainsi, des exemples de données peuvent ressembler à: 

 INSERT INTO intervalStarts select 1, 1, null union select 2, 1, '2011-01-16' union select 3, 1, '2011-01-26' union select 4, 2, null union select 5, 2, '2011-01-26' union select 6, 2, '2011-01-14'

et un simple SELECT * FROM intervals donne: 

 ItemId | IntervalId | StartDate | EndDate 1 | 1 | null | 2011-01-15 2 | 1 | 2011-01-16 | 2011-01-25 3 | 1 | 2011-01-26 | null 4 | 2 | null | 2011-01-13 6 | 2 | 2011-01-14 | 2011-01-25 5 | 2 | 2011-01-26 | null