MS SQL Server
  1. MS SQL Server
  3. t-SQL pour mettre à jour la table pour supprimer les périodes de chevauchement
Intereting Posts
T-SQL: Comment sélectionner des valeurs dans la list des valeurs qui ne sont pas dans la table? Comment get la valeur de la longueur maximale en server sql Enregistrements récursifs SQL: Comment puis-je réduire à 'un seul niveau'? Suppression des lignes dupliquées de la table temporaire Comment faire pour Sum "case when" clause après jointure interne sans duplication Requête SQL pour countr la fréquence Mise à jour vers EF 7.0.0-rc1-final cassé SQL DbContextOptionsBuilder UseSqlServer Plusieurs lignes, avec plusieurs valeurs, comment get une valeur plus élevée pour le même ID de ligne SQL Merging 4 requêtes à un Remplacer les valeurs d'une colonne si une colonne différente est vraie Je voudrais utiliser le "model de jeu nested" mais je suis obligé d'avoir un GUID comme key primaire. Comment puis-je faire sans entiers comme pk? Exécuter du code C # dans un travail d'agent SQL Comment faire un test unitaire sur une procédure de magasin à distance Utiliser une plage avec GETDATE & BETWEEN Conversion de varchar en datetime2 dans SQL Server

t-SQL pour mettre à jour la table pour supprimer les périodes de chevauchement

Je me demandais si quelqu'un pouvait m'aider avec cette déclaration SQL?

Dites, j'ai une table SQL Server 2008 comme ceci: 

id -- INT PRIMARY KEY dtIn -- DATETIME2 dtOut -- DATETIME2 type -- INT id dtIn dtOut type 1 05:00 10:00 1 2 08:00 16:00 2 3 02:00 08:00 1 4 07:30 11:00 1 5 07:00 12:00 2

Je dois supprimer tous les chevauchements de time dans le tableau ci-dessus. Cela peut être illustré avec ce diagramme: entrez la description de l'image ici

Donc je suis venu avec ce SQL: 

 UPDATE [table] AS t SET dtOut = (SELECT MIN(dtIn) FROM [table] WHERE type = t.type AND t.dtIn >= dtIn AND t.dtIn < dtOut) WHERE type = t.type AND t.dtIn >= dtIn AND t.dtIn < dtOut

Mais ça ne marche pas. Une idée de ce que je fais mal ici?

****MODIFIER****

OK, ça m'a pris du time pour y arriver. Semble être un SQL de travail pour ce dont j'ai besoin pour: 

 --BEGIN TRANSACTION; --delete identical dtIn DELETE dT1 FROM tbl dT1 WHERE EXISTS ( SELECT * FROM tbl dT2 WHERE dT1.Type = dT2.Type AND dT1.dtIn = dT2.dtIn AND ( dT1.dtOut < dT2.dtOut OR (dT1.dtOut = dT2.dtOut AND dT1.id < dT2.id) ) ); --adjust dtOuts to the max dates for overlapping section UPDATE tbl SET dtOut = COALESCE(( SELECT MAX(dtOut) FROM tbl as t1 WHERE t1.type = tbl.type AND t1.dtIn < tbl.dtOut AND t1.dtOut > tbl.dtIn ), dtOut); -- Do the actual updates of dtOut UPDATE tbl SET dtOut = COALESCE(( SELECT MIN(dtIn) FROM tbl as t2 WHERE t2.type = tbl.type AND t2.id <> tbl.id AND t2.dtIn >= tbl.dtIn AND t2.dtIn < tbl.dtOut ), dtOut); --COMMIT TRANSACTION;

Je crois que l'un des livres de Joe Celko présentait cela comme un exemple. Vous pourriez find l'extrait disponible sur Google.

Cela pourrait être plus proche. Je pense que vous ne faisiez pas vraiment la sous-requête de la bonne façon. 

 UPDATE table SET dtOut = ( SELECT MIN(t2.dtIn) FROM [table] as t2 WHERE t2.id <> table.id AND t2.type = table.type AND table.dtIn < t2.dtIn AND t2.dtIn < table.dtOut AND table.dtOut <= t2.dtOut ) WHERE EXISTS ( SELECT 1 FROM [table] as t3 WHERE t3.type = table.type AND t3.id <> table.id AND table.dtIn < t3.dtIn AND t3.dtIn < table.dtOut AND table.dtOut <= t3.dtOut )

EDIT J'ai négligé la colonne id en haut de la page, donc c'est évidemment une meilleure vérification que de s'assurer que les points de terminaison ne correspondent pas. La solution est probablement plus facile si vous pouvez supposer que pas deux lignes de type identique ont le dtIn.

BTW, il n'y a aucune raison d'utiliser un CROSS APPLY quand une sous-requête fera exactement le même travail.

EDIT 2 J'ai fait quelques tests rapides et je pense que ma requête gère le scénario dans votre diagramme. Il y a un cas où cela pourrait ne pas faire ce que vous voulez.

Pour un type donné, pensez aux deux derniers segments S1 et S2 dans l'ordre de l'heure de début. S2 commence après S1 mais imagine aussi qu'il se termine avant S1. S2 est entièrement contenu dans l'intervalle de S1, donc il est soit ignorable, soit l'information pour les deux segments doit être divisée en un troisième segment et c'est là que le problème devient plus délicat.

Cette solution suppose donc qu'ils peuvent être ignorés.

EDIT 3 basé sur le commentaire sur la combinaison des mises à jour

SQLFiddle posté par OP 

 -- eliminate redundant rows DELETE dT1 /* FROM tbl dT1 -- unnecessary */ WHERE EXISTS ( SELECT * FROM tbl dT2 WHERE dT1.Type = dT2.Type AND dT1.dtIn = dT2.dtIn AND ( dT1.dtOut < dT2.dtOut OR (dT1.dtOut = dT2.dtOut AND dT1.id < dT2.id) ) ); --adjust dtOuts to the max dates UPDATE tbl SET dtOut = COALESCE(( SELECT MAX(dtOut) FROM tbl as t1 WHERE t1.type = tbl.type ), dtOut); -- Do the actual updates of dtOut UPDATE tbl SET dtOut = COALESCE(( SELECT MIN(dtIn) FROM tbl as t2 WHERE t2.type = tbl.type AND t2.id <> tbl.id AND t2.dtIn >= tbl.dtIn AND t2.dtIn < tbl.dtOut ), dtOut);

L'une des deux mises à jour ci-dessous doit replace les deux mises à jour ci-dessus. 

 UPDATE tbl SET dtOut = ( SELECT COALESCE( MIN(dtIn), /* as long as there's no GROUP BY, there's always one row */ (SELECT MAX(dtOut) FROM tbl as tmax WHERE tmax.type = tbl.type) ) FROM tbl as tmin WHERE tmin.type = tbl.type AND tmin.dtIn > tbl.dtIn /* regarding the original condition in the second update: t2.dtIn >= tbl.dtIn AND t2.dtIn < tbl.dtOut dtIns can't be equal because you already deleted those and if dtIn was guaranteed to be less than dtOut it's also automatically always less than max(dtOut) */ ); UPDATE tbl SET dtOut = COALESCE( ( SELECT MIN(dtIn) FROM tbl as tmin WHERE tmin.type = tbl.type AND tmin.dtIn > tbl.dtIn ), ( SELECT MAX(dtOut) FROM tbl as tmax WHERE tmax.type = tbl.type ) );

Je pense que CROSS APPLY pourrait faire l'affaire: 

 DECLARE @T TABLE (ID INT, DTIn DATETIME2, dtOut DATETIME2, Type INT) INSERT @T VALUES (1, '05:00', '10:00', 1), (2, '08:00', '16:00', 2), (3, '02:00', '08:00', 1), (4, '07:30', '11:00', 1), (5, '07:00', '12:00', 2) UPDATE @T SET DtOut = T3.DtOut FROM @T T1 CROSS APPLY ( SELECT MIN(DtIn) [DtOut] FROM @T T2 WHERE T2.Type = T1.Type AND T2.DtIn > T1.dtIn AND T2.DtIn < T1.dtOut ) T3 WHERE T3.dtOut IS NOT NULL SELECT * FROM @T