MS SQL Server
  1. MS SQL Server
  3. Groupe SQL par dates
Intereting Posts
Je souhaite créer un mot de passe ou une connection pour les bases de données SQL Server 2008 R2 Quelle valeur est sélectionnée dans le paramètre dans la requête SQL sans clause where procédures de magasin d'appel de structure d'entité avec UDT Exemple de CTE et de récursivité du server SQL Éviter les conditions de concurrency lors de l'implémentation manuelle de l'incrément de type IDENTITY pour une colonne de database SQL Server supprimer toutes les valeurs, mais minimes, en fonction de deux colonnes dans la table SQL Server Fusionner les keys primaires – Mise à jour en cascade Masquer la relation d'un diagramme de database SQL Server Exécution de commands SQL et return du message Les données du groupe SQL Server par semaine mais montrent le début de la semaine Comment sélectionner l'logging / les loggings appropriés d'un groupe d'loggings lors de l'utilisation des fonctions d'agrégation MAX ou MIN Comment puis-je effectuer une requête pour toutes les dates supérieures à une certaine date dans SQL Server? Entité framework core: l'expression de propriété 'xx' n'est pas valide. L'expression doit représenter un access à la propriété: 't => t.MyProperty' ADO.NET convertit Decimal (4,2) en double en utilisant Generics Comment puis-je sélectionner les lignes d'une table qui ne sont pas liées à une autre table dans SQL Server?

Groupe SQL par dates

J'ai lu des sujets sur le groupe par séquence, c'est presque ce dont j'ai besoin, mais je n'ai pas trouvé de solution à mon problème.

J'ai une table comme celle-ci: 

PlanificatorPozitieID JalonID DataStart DataFinal --------------------- ----------- ----------------------- ----------------------- 26 46 2012-05-21 00:00:00.000 2012-05-31 00:00:00.000 28 48 2012-06-01 00:00:00.000 2012-06-01 00:00:00.000 27 60 2012-06-02 00:00:00.000 2012-06-02 00:00:00.000 29 60 2012-06-07 00:00:00.000 2012-06-08 00:00:00.000 37 60 2012-06-08 00:00:00.000 2012-06-10 00:00:00.000 30 65 2012-06-10 00:00:00.000 2012-06-13 00:00:00.000 31 65 2012-06-18 00:00:00.000 2012-06-24 00:00:00.000 32 65 2012-06-23 00:00:00.000 2012-07-01 00:00:00.000 33 66 2012-07-02 00:00:00.000 2012-07-02 00:00:00.000 34 66 2012-07-02 00:00:00.000 2012-07-05 00:00:00.000 36 66 2012-07-06 00:00:00.000 2012-07-10 00:00:00.000 Desired output: PlanificatorPozitieID JalonID DataStart DataFinal --------------------- ----------- ----------------------- ----------------------- 26 46 2012-05-21 00:00:00.000 2012-05-31 00:00:00.000 28 48 2012-06-01 00:00:00.000 2012-06-01 00:00:00.000 27 60 2012-06-02 00:00:00.000 2012-06-02 00:00:00.000 29 60 2012-06-07 00:00:00.000 2012-06-10 00:00:00.000 30 65 2012-06-10 00:00:00.000 2012-06-13 00:00:00.000 31 65 2012-06-18 00:00:00.000 2012-07-01 00:00:00.000 33 66 2012-07-02 00:00:00.000 2012-07-05 00:00:00.000 36 66 2012-07-06 00:00:00.000 2012-07-10 00:00:00.000

Je dois donc grouper par JalonID, mais le groupe par ne devrait être fait que si le DataFinal> = DataStart. Je veux get la période de time de chaque JalonID, mais je veux get seulement les périodes qui n'ont pas de time de pause.

Accueil Je me suis fait clair. 

 select MIN(pp.DataStart) as DataStart, MAX(pp.DataFinal) as DataFinal, pp.JalonID FROM #PlanPozitii pp GROUP BY pp.JalonID

Mais cette requête ne satisfait pas ma condition de groupe par des périodes continues.

Quant à clarifier. Prenez l'exemple suivant 

 30 65 2012-06-10 00:00:00.000 2012-06-13 00:00:00.000 31 65 2012-06-18 00:00:00.000 2012-06-24 00:00:00.000 32 65 2012-06-23 00:00:00.000 2012-07-01 00:00:00.000

2012-06-13 00:00:00.000 < 2012-06-18 00:00:00.000 donc aucun groupe n'aurait lieu entre PlanificatorPozitieID 30 et 31 . Mais 2012-06-24 00:00:00.000 > 2012-06-23 00:00:00.000 alors maintenant il y aurait un groupe entre PlanificatorPozitieID 31 et 32 .

Donc, à partir de ces 3 lignes, nous aurons deux lignes. 

 30 65 2012-06-10 00:00:00.000 2012-06-13 00:00:00.000 31 65 2012-06-18 00:00:00.000 2012-07-01 00:00:00.000 DECLARE @YourTable TABLE(PlanificatorPozitieID INT, JalonID INT,DataStart DATETIME, DataFinal DATETIME) INSERT INTO @YourTable VALUES (39,1223,'2015-02-16 00:00:00.000','2015-02-20 00:00:00.000'), (43,1223,'2015-02-19 00:00:00.000','2015-02-24 00:00:00.000'), (40,1223,'2015-02-23 00:00:00.000','2015-02-27 00:00:00.000'), (42,1223,'2015-03-09 00:00:00.000','2015-03-13 00:00:00.000') ;WITH cte AS ( SELECT a.PlanificatorPozitieID, a.JalonID, a.DataStart, COALESCE(b.DataFinal,a.datafinal) AS [DataFinal], ROW_NUMBER() OVER (PARTITION BY a.JalonID ORDER BY DATEDIFF(dd,a.datastart, COALESCE(b.DataFinal,a.datafinal))) [rn], COUNT(*) OVER (PARTITION BY a.JalonID) [cnt] FROM @YourTable a LEFT JOIN @YourTable b ON a.JalonID = b.JalonID AND b.DataStart BETWEEN a.DataStart AND a.DataFinal AND a.PlanificatorPozitieID <> b.PlanificatorPozitieID AND DATEDIFF(dd,a.DataStart,a.DataFinal) < DATEDIFF(dd,a.DataStart,b.DataFinal) ) SELECT * FROM cte WHERE rn= 1 OR rn=cnt

Résultat : 

 PlanificatorPozitieID JalonID DataStart DataFinal rn cnt --------------------- ----------- ----------------------- ----------------------- -------------------- ----------- 40 1223 2015-02-23 00:00:00.000 2015-02-27 00:00:00.000 1 4 43 1223 2015-02-19 00:00:00.000 2015-02-27 00:00:00.000 4 4

Résultat attendu: 

 PlanificatorPozitieID JalonID DataStart DataFinal --------------------- ----------- ----------------------- ----------------------- 39 1223 2015-02-16 00:00:00.000 2015-02-27 00:00:00.000 42 1223 2015-03-09 00:00:00.000 2015-03-13 00:00:00.000

Je ne sais pas si cela fonctionnera bien avec datatables réelles car je ne l'ai pas testé rigoureusement, mais voici une solution: 

 DECLARE @YourTable TABLE(PlanificatorPozitieID INT, JalonID INT,DataStart DATETIME, DataFinal DATETIME) INSERT INTO @YourTable VALUES (26,46,'2012-05-21 00:00:00.000','2012-05-31 00:00:00.000'), (28,48,'2012-06-01 00:00:00.000','2012-06-01 00:00:00.000'), (27,60,'2012-06-02 00:00:00.000','2012-06-02 00:00:00.000'), (29,60,'2012-06-07 00:00:00.000','2012-06-08 00:00:00.000'), (37,60,'2012-06-08 00:00:00.000','2012-06-10 00:00:00.000'), (30,65,'2012-06-10 00:00:00.000','2012-06-13 00:00:00.000'), (31,65,'2012-06-18 00:00:00.000','2012-06-24 00:00:00.000'), (32,65,'2012-06-23 00:00:00.000','2012-07-01 00:00:00.000'), (33,66,'2012-07-02 00:00:00.000','2012-07-02 00:00:00.000'), (34,66,'2012-07-02 00:00:00.000','2012-07-05 00:00:00.000'), (36,66,'2012-07-06 00:00:00.000','2012-07-10 00:00:00.000') ;WITH cte AS ( SELECT a.PlanificatorPozitieID, a.JalonID, a.DataStart, COALESCE(b.DataFinal,a.datafinal) AS [DataFinal], ROW_NUMBER() OVER (PARTITION BY a.JalonID ORDER BY DATEDIFF(dd,a.datastart, COALESCE(b.DataFinal,a.datafinal))) [rn], COUNT(*) OVER (PARTITION BY a.JalonID) [cnt] FROM @YourTable a LEFT JOIN @YourTable b ON a.JalonID = b.JalonID AND b.DataStart BETWEEN a.DataStart AND a.DataFinal AND a.PlanificatorPozitieID <> b.PlanificatorPozitieID AND DATEDIFF(dd,a.DataStart,a.DataFinal) < DATEDIFF(dd,a.DataStart,b.DataFinal) ) SELECT * FROM cte WHERE rn= 1 OR rn=cnt

J'ai trouvé un solutin, ce n'est pas très efficace car il utilise 2 sliders. Mais ça marche au cas où quelqu'un aurait besoin d'un exemple 

 DROP TABLE #DateTEst DROP TABLE #pozitii drop table #PozitiiJaloaneStandard CREATE TABLE #DateTest (JalonStandardID int,DataStart datetime,DataFinal datetime) INSERT INTO #DateTest VALUES (1,'2015-05-05','2015-05-08') INSERT INTO #DateTest VALUES (1,'2015-05-09','2015-05-13') INSERT INTO #DateTest VALUES (1,'2015-05-12','2015-05-15') INSERT INTO #DateTest VALUES (1,'2015-05-16','2015-05-18') INSERT INTO #DateTest VALUES (1,'2015-05-14','2015-05-19') INSERT INTO #DateTest VALUES (2,'2015-05-05','2015-05-06') INSERT INTO #DateTest VALUES (2,'2015-05-06','2015-05-07') INSERT INTO #DateTest VALUES (2,'2015-05-06','2015-05-09') INSERT INTO #DateTest VALUES (3,'2015-05-05','2015-05-07') INSERT INTO #DateTest VALUES (3,'2015-05-08','2015-05-10') INSERT INTO #DateTest VALUES (4,'2015-05-05','2015-05-08') INSERT INTO #DateTest VALUES (5,'2015-05-07','2015-05-07') INSERT INTO #DateTest VALUES (5,'2015-05-08','2015-05-08') INSERT INTO #DateTest VALUES (5,'2015-05-09','2015-05-12') INSERT INTO #DateTest VALUES (5,'2015-05-11','2015-05-12') INSERT INTO #DateTest VALUES (6,'2015-05-05','2015-05-20') INSERT INTO #DateTest VALUES (6,'2015-05-15','2015-05-18') CREATE TABLE #Pozitii (DataStart datetime, DataFinal datetime) CREATE TABLE #PozitiiJaloaneStandard ( JalonStandardID int, DataStart datetime, DataFinal datetime) Declare @JalonStandarID int DEclare @PlanificatorPozitieID int Declare @DataStartPozitie datetime Declare @DataFinalPozitie datetime DEclare @DataStartMin datetime Declare @PozitieMinima int Declare @DataFinalMin datetime Declare @DataStartJalonStandard datetime Declare @DataFinalJalonStandard datetime Declare Crs_JaloaneStandard Cursor For Select JalonStandardID From #DateTest ORDER BY JalonStandardID Open Crs_JaloaneStandard Fetch Next From Crs_JaloaneStandard Into @JalonStandarID While @@Fetch_Status = 0 Begin INSERT INTO #Pozitii SELECT pp.DataStart,pp.DataFinal FROM #DateTest pp WHERE pp.JalonStandardID = @JalonStandarID GROUP BY pp.DataStart,pp.DataFinal SELECT @DataStartMin = MIN(DataStart) FROM #Pozitii SELECT @DataFinalMin = DataFinal FROM #Pozitii WHERE DataStart = @DataStartMin Declare Crs_Pozitii Cursor For SELECT p.DataStart,p.DataFinal FROM #Pozitii p ORDER by p.DataStart ASC Open Crs_Pozitii Fetch Next From Crs_Pozitii Into @DataStartPozitie,@DataFinalPozitie while @@FETCH_STATUS = 0 begin if (@DataFinalMin > @DataStartPozitie) and (@DataFinalMin <= @DataFinalPozitie ) begin set @DataFinalMin = @DataFinalPozitie end if (@DataFinalMin <= @DataStartPozitie) begin INSERT INTO #PozitiiJaloaneStandard VALUES (@JalonStandarID,@DataStartMin,@DataFinalMin) set @DataFinalMin = @DataFinalPozitie set @DataStartMin = @DataStartPozitie print @DataStartPozitie print @DataFinalPozitie end Fetch Next From Crs_Pozitii Into @DataStartPozitie,@DataFinalPozitie End INSERT INTO #PozitiiJaloaneStandard VALUES (@JalonStandarID,@DataStartMin,@DataFinalMin) DELETE FROM #Pozitii Close Crs_Pozitii Deallocate Crs_Pozitii Fetch Next From Crs_JaloaneStandard Into @JalonStandarID End Close Crs_JaloaneStandard Deallocate Crs_JaloaneStandard SELECT * FROM #PozitiiJaloaneStandard GROUP BY JalonStandardID,DataStart,DataFinal

https://msdn.microsoft.com/en-us/library/hh231256.aspx

Je cherchais la fonction LAG () qui permet d'accéder aux lignes précédentes dans la requête. Ma pensée était de calculer la différence entre la rangée actuelle et la rangée précédente pour get une colonne à grouper avec; 

 with a as ( select 1 as ID, 1 as A, 2 as B union all select 1 as ID, 2 as A, 3 as B union all select 1 as ID, 3 as A, 4 as B union all select 1 as ID, 4 as A, 5 as B union all select 1 as ID, 6 as A, 7 as B ) select ID, A, B, A-LAG(B,1,0) OVER (order by ID) as koe from a where B > A

Si vous exécutez cette requête, vous obtenez le résultat en tant que; 

 ID AB koe ----------- ----------- ----------- ----------- 1 1 2 1 1 2 3 0 1 3 4 0 1 4 5 0 1 6 7 1

Imaginez A est DataStart et B est DataFinal, et koe calculé est la différence, comme vous pouvez le voir, il fonctionne sur tout sauf la première rangée … la première rangée obtient la différence entre la ligne nonexisting (donc son 0). Mais c'est la direction que je commencerais à essayer.