8
votes

Créer des groupes de jours consécutifs répondant à un critère donné

J'ai une table la structure de données suivante dans SQL Server: xxx

etc.

Ce que je dois faire est d'obtenir toutes les périodes de jour consécutives où allocation = 0, et sous la forme suivante: xxx

etc.

est-il possible de le faire dans SQL, et si oui comment?

sql sql-server-2008

3 commentaires

@ Istari est la date de fin une colonne de votre structure de table

Avez-vous essayé d'utiliser le curseur? ou vous n'avez pas besoin de curseurs

Voulez-vous dire «consécutif» comme en «un jour à distance» ou comme dans «adjacent lorsque des rangées sont triées par date»? C'est-à-dire que chaque date unique semble-t-elle exactement une fois dans la colonne "Date"?

6 Réponses :

0
votes

une solution sans CTE: xxx

exemple

1 commentaires

Lorsque vous allumez cela, je reçois le message d'erreur suivant: MSG 530, niveau 16, état 1, ligne 1 La déclaration s'est terminée. La récursion maximale 100 a été épuisée avant la déclaration C

3
votes

suivi serait une façon de le faire. Le gist de cette solution est

Utilisez un CTE code> pour obtenir une liste de tous les démarrages consécutifs et enddates avec allocation = 0 code> li>
Utilisez la fonction Row_Number code> de la fenêtre pour attribuer des rownumbers en fonction des deux start-et des findates. LI>
Sélectionnez uniquement les enregistrements où Row_numbers CODE> EGAL 1. LI>

 Utilisez  datrodiff  code> pour calculer le  jourCOUNT  code>  li>
 ul>   SQL SMSL  STRY>  P> 
;WITH q (ID, Date, Allocation) AS (
  SELECT * FROM (VALUES
    (1, '2012-01-01', 0)
    , (2, '2012-01-02', 2)
    , (3, '2012-01-03', 0)
    , (4, '2012-01-04', 0)
    , (5, '2012-01-05', 0)
    , (6, '2012-01-06', 5)
  ) a (a, b, c)
)
, r AS (
  SELECT  StartDate = Date, EndDate = Date
  FROM    q
  WHERE   Allocation = 0
  UNION ALL
  SELECT  r.StartDate, q.Date
  FROM    r
          INNER JOIN q ON DATEDIFF(dd, r.EndDate, q.Date) = 1
  WHERE   q.Allocation = 0          
)
SELECT  s.StartDate, s.EndDate, DATEDIFF(dd, s.StartDate, s.EndDate) + 1
FROM    (
          SELECT  *
                  , rn1 = ROW_NUMBER() OVER (PARTITION BY StartDate ORDER BY EndDate DESC)
                  , rn2 = ROW_NUMBER() OVER (PARTITION BY EndDate ORDER BY StartDate ASC)
          FROM    r          
        ) s
WHERE   s.rn1 = 1
        AND s.rn2 = 1
OPTION  (MAXRECURSION 0)

1 commentaires

@Istari - J'ai amplifié une option maxrecursion pour corriger le message d'erreur.

1
votes

voie alternative avec CTE mais sans Row_Number (),

échantillons de données: xxx

requête: xxx

Exemple en direct sur data.se .

0 commentaires

1
votes

Utilisation de ce exemple de données:

ID          Date                    Allocation  SeedID
----------- ----------------------- ----------- -----------
1           2012-01-01 00:00:00.000 0           1
3           2012-01-03 00:00:00.000 0           3
4           2012-01-04 00:00:00.000 0           3
5           2012-01-05 00:00:00.000 0           3

0 commentaires

1
votes

Essayez si cela fonctionne pour vous Ici sdate pour votre date reste identique à votre table.

SELECT SDATE,
CASE WHEN (SELECT COUNT(*)-1 FROM TABLE1 WHERE ID BETWEEN TBL1.ID AND (SELECT MIN(ID) FROM TABLE1 WHERE ID > TBL1.ID AND ALLOCATION!=0)) >0 THEN(
CASE WHEN (SELECT SDATE FROM TABLE1 WHERE ID =(SELECT MAX(ID) FROM TABLE1 WHERE ID >TBL1.ID AND ID<(SELECT MIN(ID) FROM TABLE1 WHERE ID > TBL1.ID AND ALLOCATION!=0))) IS NULL THEN SDATE
ELSE (SELECT SDATE FROM TABLE1 WHERE ID =(SELECT MAX(ID) FROM TABLE1 WHERE ID >TBL1.ID AND ID<(SELECT MIN(ID) FROM TABLE1 WHERE ID > TBL1.ID AND ALLOCATION!=0))) END
)ELSE (SELECT SDATE FROM TABLE1 WHERE ID = (SELECT MAX(ID) FROM TABLE1 WHERE ID > TBL1.ID ))END AS EDATE
,CASE WHEN (SELECT COUNT(*)-1 FROM TABLE1 WHERE ID BETWEEN TBL1.ID AND (SELECT MIN(ID) FROM TABLE1 WHERE ID > TBL1.ID AND ALLOCATION!=0)) <0 THEN 
(SELECT COUNT(*) FROM TABLE1 WHERE ID BETWEEN TBL1.ID AND (SELECT MAX(ID) FROM TABLE1 WHERE ID > TBL1.ID )) ELSE
(SELECT COUNT(*)-1 FROM TABLE1 WHERE ID BETWEEN TBL1.ID AND (SELECT MIN(ID) FROM TABLE1 WHERE ID > TBL1.ID AND ALLOCATION!=0)) END AS DAYCOUNT
FROM TABLE1 TBL1 WHERE ALLOCATION = 0
AND (((SELECT ALLOCATION FROM TABLE1 WHERE ID=(SELECT MAX(ID) FROM TABLE1  WHERE ID < TBL1.ID))<> 0 ) OR (SELECT MAX(ID) FROM TABLE1  WHERE ID < TBL1.ID)IS NULL);

0 commentaires

3
votes

Dans cette réponse, je suppose que le champ "ID" Numérite les lignes consécutivement lorsqu'elles sont triées en augmentant la date d'augmentation, comme dans les exemples de données. (Une telle colonne peut être créée si elle n'existe pas).

Ceci est un exemple de technique décrite ici et ici . p>

1) rejoignez la table sur des valeurs" ID "adjacentes. Ces paires de rangées adjacentes. Sélectionnez des lignes dans lesquelles le champ «Allocation» a changé. Stocker le résultat dans une table temporaire, maintenez également un index de fonctionnement. P>

SELECT
  boundaries2.next_start AS start,
  boundaries.prev_end AS end,
  allocation
FROM boundaries
JOIN boundaries2
USING(idx);

+------------+------------+------------+
| start      | end        | allocation |
+------------+------------+------------+
| 2012-01-02 | 2012-01-02 |          2 |
| 2012-01-03 | 2012-01-05 |          0 |
+------------+------------+------------+

0 commentaires