Accumuler avec le temps (sans chevauchement) - technique?

Question

Je suis en train de chercher une meilleure façon de faire un rapport Crystal (celui de quelqu'un d'autre)... Additionner des temps non chevauchants en groupes. Il s'agit clairement d'un problème ancien... Existe-t-il une technique pour obtenir

• Les temps ajustés (début/fin), par enregistrement, pour supprimer les temps communs/chevauchants,
• au sein des sous-groupes
• --en utilisant du SQL pur (bien que je puisse utiliser des CTE)

Supposez un ordre initial par Heure de Début (et/ou Groupe, Sous-groupe) et Début et Fin sont des champs séparés.

Un exemple graphique :

Groupe 1
  Sous-groupe A
    Tkt 1    |--début&fin---|                      "16"
    Tkt 2        |----début&fin----|                "18"
    Tkt 3          |--début&fin---|                  "14"
    Tkt 4                            |--D & F -|   "11"

  Sous-groupe B
    Tkt 5  |-D&F-|                                  "7" 
    Tkt 6          |-D&F-|                          "7" 
    Tkt 7            |-D&F-|                        "7"  
    ...

(équivalent de début/fin ajusté dans le sous-groupe) :
Groupe 1
  Sous-groupe A                         (avec un "temps écoulé" de "33"   )
    Tkt 1    |--début&fin---|         <- Régulier             "16"
    Tkt 2                   |-----|   <- Début ajusté         "6"
    Tkt 3                         |   <- Début ajusté & Fin ajustée   "0"
    Tkt 4                            |--D & F -|  <- Régulier "11"

  Sous-groupe B                         (avec un "temps écoulé"  de "17"   )
    Tkt 5  |-D&F-|                    <- Régulier              "7"  
    Tkt 6          |-D&F-|            <- Régulier (pas de chevauchement) "7"
    Tkt 7                |-|          <- Début ajusté     "3"
    ...

Je ne parle pas encore de calculs à ce stade, juste d'illustrer l'ajustement du début/fin en fonction de l'enregistrement précédent.

Dans le rapport, ils font plusieurs formules pour chaque enregistrement, par rapport à deux variables qui sont définies sur le premier enregistrement du groupe, puis setting/mettant à jour les valeurs pour DébutAjusté, FinAjustée en fonction de l'enregistrement courant, et en passant le DébutAjusté, FinAjustée à une autre formule pour calculer la différence de temps, à sommer plus tard. La technique actuelle est lente, et je ne peux pas faire un joli tableau croisé dynamique souhaité.

Je pense/j'espère que cela a été résolu en SQL uniquement, car nous ne pouvons pas mettre de procédures, de tables temporaires, etc. sur le serveur de la base de données. J'essaie de trouver un moyen avec CTE et de (re-)cursing, en utilisant le Groupe/Sous-groupe comme parenté, et CASE pour comparer les valeurs courantes avec les dernières valeurs parentes. Est-ce que ça vous dit quelque chose ou vous semble plausible ?

Les capacités de Crystal sont nombreuses, mais celle-ci semble ne pas être faite pour cela.

Marc

Answer 1

Je fais ça de mémoire...

SELECT
    CUR.subgroup,
    CUR.ticket,
    COALESCE(MAX(PARENT.end_time), CUR.start_time) AS start_time,
    CASE
        WHEN CUR.end_time < MAX(PARENT.end_time) THEN MAX(PARENT.end_time)
        ELSE CUR.end_time
    END
FROM
    Ma_Table CUR
LEFT OUTER JOIN Ma_Table PARENT ON
    PARENT.start_time <= CUR.start_time AND
    PARENT.end_time > CUR.start_time AND
    PARENT.ticket <> CUR.ticket AND
    PARENT.subgroup = CUR.subgroup
GROUP BY
    CUR.subgroup,
    CUR.ticket,
    CUR.start_time,
    CUR.end_time

Answer 2

Je vous remercie d'avoir posé cette question il y a longtemps, mais cela m'a intéressé, donc j'ai fait des recherches et trouvé une solution par Jeff Moden; il a écrit un article sur comment regrouper des îlots de dates que vous pouvez trouver ici : Group Islands of Contiguous Dates (connexion requise mais inscription gratuite).

J'ai supposé que vous avez une table avec des lignes pour chaque jour dans un sous-groupe, donc il y a 16 lignes pour "Tkt1", 18 pour "Tkt2", etc. Si ce n'est pas le cas, et que vous avez seulement des dates de début et de fin pour chaque "Tkt", vous devrez utiliser une table de Calendrier pour extrapoler les lignes pour chaque plage.

La solution de Jeff utilise un stratagème de ROW_NUMBER et de DATEDIFF pour regrouper les îlots de dates.

WITH Grouped_Dates AS
( -- Trouver les dates uniques et les attribuer à un groupe.
  -- Le groupe ressemble à une date mais la date ne signifie rien sauf que les dates adjacentes feront partie du même groupe.
 SELECT nom_groupe,
        date_unique = date_tkt,
        groupe_date  = DATEADD(dd, -ROW_NUMBER() OVER (PARTITION BY nom_groupe ORDER BY nom_groupe, date_tkt), date_tkt)
  FROM t
  GROUP BY nom_groupe, date_tkt
)
-- Maintenant, si nous trouvons la date MIN et MAX pour chaque date_groupe, nous aurons les dates de début et de fin de chaque groupe de dates contiguës. En même temps, nous pouvons également déterminer combien de jours sont dans chaque plage de jours.
SELECT nom_groupe,
       date_debut = MIN(date_unique),
       date_fin   = MAX(date_unique),
       jours       = DATEDIFF(dd,MIN(date_unique),MAX(date_unique))+1
FROM Grouped_Dates
GROUP BY nom_groupe, groupe_date
ORDER BY nom_groupe, date_debut

Le résultat de cette requête est

nom\_groupe  date\_debut  date\_fin    jours
----------  ----------  ----------  ----
Groupe1      2012-01-01  2012-01-22    22
Groupe1      2012-01-24  2012-02-03    11
Groupe2      2012-01-09  2012-01-15     7
Groupe2      2012-01-18  2012-01-27    10

J'ai créé un SQL Fiddle avec des données d'exemple basées sur votre question.

Vous pouvez ensuite additionner chaque groupe pour donner le total global du temps passé.