MS SQL Server
  1. MS SQL Server
  3. Requête SQL pour renvoyer un jeu de résultats pour une ligne de time
Intereting Posts
Échec de l'ouverture de session sur la database BizTalk lors de l'access via le service Windows, alors qu'il fonctionne via l'API Web (dans la même bibliothèque) Comment accorder l'autorisation d'IMPERSONNER un autre user? Erreur survenant sur la table tronquée dans la procédure stockée SQL: mise à jour des lignes uniques en fonction d'une colonne SqlCommand timeout même si dans SQL Studio même requête est rapide La database d'infrastructure de l'entité peut-elle d'abord générer des templates à partir de n'importe quelle database SQL Server? SQL Server générant une géographie spatiale random autour du point? Comment returnner les résultats de la procédure stockée SQL Server à Excel Un triggersur DDL a-t-il une visibilité sur l'état précédent? Exécuter un grand script sql Comment classr les heures en tant qu'In et Out pour plusieurs horodatages dans une journée de travail dans SQL Server Besoin d'exécuter une requête SQL dans AutoIt context.Database.Exists renvoie toujours true, même si le file de database est manquant Masquer le menu dans SRSS T-SQL programmatique dans les scripts sql ad-hoc

Requête SQL pour renvoyer un jeu de résultats pour une ligne de time

Je pense que la meilleure façon de décrire ce que je cherche est de montrer une table de données et ce que je veux renvoyer de ma requête. Ceci est une table de données simple dans SQL Server: 

JobNumber TimeOfWeigh 100 01/01/2014 08:00 100 01/01/2014 09:00 100 01/01/2014 10:00 200 01/01/2014 12:00 200 01/01/2014 13:00 300 01/01/2014 15:00 300 01/01/2014 16:00 100 02/01/2014 08:00 100 02/01/2014 09:00 100 03/01/2014 10:00

Je veux une requête qui regroupera le travail et returnnera le premier et le dernier DateTime de chaque groupe. Cependant, comme vous pouvez le voir ici, il y a 2 séries de 100 numéros d'emplois. Je ne veux pas que le second set soit joint au premier.

Au lieu de cela je voudrais ceci: 

 JobNumber First Weigh Last Weigh 100 01/01/2014 08:00 01/01/2014 10:00 200 01/01/2014 12:00 01/01/2014 13:00 300 01/01/2014 15:00 01/01/2014 16:00 100 02/01/2014 08:00 03/01/2014 10:00

J'ai lutté avec cela pendant des heures. Toute aide serait appréciée.

ÉDITÉ

La date et l'heure sont toutes des données randoms fictives. Les données réelles ont des milliers de pesées en un jour. Je veux que le premier et le dernier poids de chaque travail déterminent la durée du travail afin que je puisse représenter la durée sur un calendar. Mais je veux afficher Job 100 deux fois, indiquant qu'il a été mis en pause et repris après que 200 et 300 aient été terminés

Voici ma tentative à ceci, en utilisant row_number () avec une partition. Je l'ai cassé en étapes pour le rendre facile à suivre. Si votre table contient déjà une colonne avec des identificateurs d'entiers, vous pouvez omettre le premier CTE. Même après cela, vous pourriez peut-être simplifier cela davantage, mais cela semble fonctionner.

(Edité pour append un drapeau indiquant les travaux avec plusieurs plages comme demandé dans un commentaire.) 

 declare @sampleData table (JobNumber int, TimeOfWeigh datetime); insert into @sampleData values (100, '01/01/2014 08:00'), (100, '01/01/2014 09:00'), (100, '01/01/2014 10:00'), (200, '01/01/2014 12:00'), (200, '01/01/2014 13:00'), (300, '01/01/2014 15:00'), (300, '01/01/2014 16:00'), (100, '02/01/2014 08:00'), (100, '02/01/2014 09:00'), (100, '03/01/2014 10:00'); -- The first CTE assigns an ordering to the records according to TimeOfWeigh, -- producing the row numbers you gave in your example. with JobsCTE as ( select row_number() over (order by TimeOfWeigh) as RowNumber, JobNumber, TimeOfWeigh from @sampleData ), -- The second CTE orders by the RowNumber we created above, but restarts the -- ordering every time the JobNumber changes. The difference between RowNumber -- and this new ordering will be constant within each group. GroupsCTE as ( select RowNumber - row_number() over (partition by JobNumber order by RowNumber) as GroupNumber, JobNumber, TimeOfWeigh from JobsCTE ), -- Join by JobNumber alone to determine which jobs appear multiple times. DuplicatedJobsCTE as ( select JobNumber from GroupsCTE group by JobNumber having count(distinct GroupNumber) > 1 ) -- Finally, we use GroupNumber to get the mins and maxes from contiguous ranges. select G.JobNumber, min(G.TimeOfWeigh) as [First Weigh], max(G.TimeOfWeigh) as [Last Weigh], case when D.JobNumber is null then 0 else 1 end as [Multiple Ranges] from GroupsCTE G left join DuplicatedJobsCTE D on G.JobNumber = D.JobNumber group by G.JobNumber, G.GroupNumber, D.JobNumber order by [First Weigh];

vous devez utiliser des jointures auto pour créer des tables pseudo contenant la première et dernière rangée de chaque set. 

 Select F.JobNumber, f.TimeOfWeigh FirstWeigh, l.TimeOfWeigh LastWeigh From table f -- for first record join table l -- for last record on l.JobNumber = f.JobNumber And Not exists (Select * from table Where JobNumber = f.JobNumber And id = f.id-1) And Not exists (Select * from table Where JobNumber = f.JobNumber And id = l.id+1) And Not Exists (Select * from table Where JobNumber <> f.JobNumber And id Between f.Id and l.Id)

Celui-ci m'a fasciné quand je l'ai vu, et je me suis demandé comment j'allais y arriver. J'étais trop occupé pour répondre d'abord, et je l'ai fait fonctionner plus tard, mais je suis resté assis dessus pendant quelques jours! Après quelques jours je comprends encore ce que j'ai imaginé, ce qui est un bon signe 🙂

J'ai ajouté des données supplémentaires à la fin pour démontrer que cela fonctionne avec des inputs JobNumber à une rangée, plutôt que de supposer que les pesées seront toujours par lots, mais les premières lignes dans les résultats correspondent à la solution d'origine.

Cette approche utilise également des CTE en cascade (une de plus que la réponse acceptée ici mais je ne laisserai pas cela me décourager!), La première étant la configuration des données de test: 

 With Weighs AS -- sample data ( SELECT 100 AS JobNumber, '01/01/2014 08:00' AS TimeOfWeigh UNION ALL SELECT 100 AS JobNumber, '01/01/2014 09:00' AS TimeOfWeigh UNION ALL SELECT 100 AS JobNumber, '01/01/2014 10:00' AS TimeOfWeigh UNION ALL SELECT 200 AS JobNumber, '01/01/2014 12:00' AS TimeOfWeigh UNION ALL SELECT 200 AS JobNumber, '01/01/2014 13:00' AS TimeOfWeigh UNION ALL SELECT 300 AS JobNumber, '01/01/2014 15:00' AS TimeOfWeigh UNION ALL SELECT 300 AS JobNumber, '01/01/2014 16:00' AS TimeOfWeigh UNION ALL SELECT 100 AS JobNumber, '02/01/2014 08:00' AS TimeOfWeigh UNION ALL SELECT 100 AS JobNumber, '02/01/2014 09:00' AS TimeOfWeigh UNION ALL SELECT 100 AS JobNumber, '03/01/2014 10:00' AS TimeOfWeigh UNION ALL SELECT 400 AS JobNumber, '04/01/2014 14:00' AS TimeOfWeigh UNION ALL SELECT 300 AS JobNumber, '04/01/2014 14:30' AS TimeOfWeigh ) , Numbered AS -- add on a unique consecutive row number ( SELECT *, ROW_NUMBER() OVER (ORDER BY TimeOfWeigh) AS ID FROM Weighs ) , GroupEnds AS -- add on a 1/0 flag for whether it's the first or last in a run ( SELECT *, CASE WHEN -- next row is different JobNumber? (SELECT ID FROM Numbered n2 WHERE n2.ID=n1.ID+1 AND n2.JobNumber=n1.JobNumber) IS NULL THEN 1 ELSE 0 END AS GroupEnd, CASE WHEN -- previous row is different JobNumber? (SELECT ID FROM Numbered n2 WHERE n2.ID=n1.ID-1 AND n2.JobNumber=n1.JobNumber) IS NULL THEN 1 ELSE 0 END AS GroupBegin FROM Numbered n1 ) , Begins_and_Ends AS -- make sure there are always matching pairs ( SELECT * FROM GroupEnds WHERE GroupBegin=1 UNION ALL SELECT * FROM GroupEnds WHERE GroupEnd=1 ) , Pairs AS -- give matching pairs the same ID number for GROUPing next.. ( SELECT *, (1+Row_Number() OVER (ORDER BY ID))/2 AS PairID FROM Begins_and_Ends ) SELECT Min(JobNumber) AS JobNumber, Min(TimeOfWeigh) as [First Weigh], Max(TimeOfWeigh) as [Last Weigh] FROM Pairs GROUP BY PairID ORDER BY PairID

Le CTE Numbered est assez évident, donnant un numéro d'identification ordonné à chaque rangée.

CTE GroupEnds ajoute une paire de booleans – un 1 ou 0 si la ligne est la première ou la dernière dans une exécution de JobNumbers – en essayant de voir si la ligne suivante ou précédente est le même JobNumber.

De là, j'ai simplement besoin d'un moyen de coupler les GroupBegins et GroupEnds adjacents. J'ai joué avec la fonction de classment N-tile NTILE () pour générer ces nombres en divisant le rowcount par 2 en comptant les GroupEnds et en sélectionnant ce résultat comme paramètre pour NTILE () – mais cela s'est cassé quand il y avait un nombre impair de lignes aux lots à une seule rangée où la même rangée est un début et une fin d'un lot.

J'ai contourné ceci en garantissant un nombre égal de rangs de début et de fin: un rang de début et un rang de fin, même si certains sont les mêmes rangs. Ceci est CTE Begins_and_Ends .

Les Pairs CTE ajoute des numéros de paire en utilisant Row_Number () divisé par deux – le résultat entier PairID étant le même pour les paires de lignes.

Cela nous donne ce qui suit – toutes les lignes au milieu des lots JobNumber ont été filtrés maintenant: 

 JOBNUMBER TIMEOFWEIGH ID End? Begin PairID 100 01/01/2014 08:00 1 0 1 1 100 01/01/2014 10:00 3 1 0 1 200 01/01/2014 12:00 4 0 1 2 200 01/01/2014 13:00 5 1 0 2 300 01/01/2014 15:00 6 0 1 3 300 01/01/2014 16:00 7 1 0 3 100 02/01/2014 08:00 8 0 1 4 100 03/01/2014 10:00 10 1 0 4 400 04/01/2014 14:00 11 1 1 5 400 04/01/2014 14:00 11 1 1 5 300 04/01/2014 14:30 12 1 1 6 300 04/01/2014 14:30 12 1 1 6

De là, c'est maintenant un dernier morceau de gâteau à GROUP BY le PairID et saisir les premiers et derniers time de pesée. J'ai apprécié le défi, je me request si quelqu'un d'autre le trouve utile dans n'importe quel poids !
http://sqlfiddle.com/#!3/b4f39/48