J'utilise SQL Server 2012. J'ai un tableau avec l'historique quotidien de la somme des retards, quelque chose comme ceci:

SET DATEFORMAT YMD
GO
    CREATE TABLE [dbo].[testsum](
        [CID] [int],
        [HDATE] [date],
        [DELAYSUM] [numeric](16, 2)
)
GO
INSERT [dbo].[testsum] ([CID], [HDATE], [DELAYSUM]) VALUES
        (223,'2016-10-16',15503.80)
        ,(223,'2016-10-17',15493.82)
        ,(223,'2016-10-18',15489.25)
        ,(223,'2016-10-19',15417.08)
        ,(427,'2016-10-01',10375.89)
        ,(427,'2016-10-02',10375.89)
        ,(427,'2016-10-03',10385.91)
        ,(427,'2016-10-16',8448.57)
        ,(427,'2016-10-17',8443.13)
        ,(427,'2016-10-18',8440.64)
        ,(427,'2016-10-19',8401.31)
        ,(427,'2016-10-20',8411.20)
        ,(427,'2016-10-21',8414.58)
        ,(427,'2016-10-22',8414.58)
        ,(427,'2016-10-23',8414.58)
        ,(427,'2016-10-24',8401.23)
        ,(427,'2016-10-25',8393.92)
        ,(427,'2016-10-26',8379.14)
        ,(427,'2016-10-27',8374.57)
        ,(427,'2016-10-28',8358.67)
        ,(427,'2016-10-29',8358.67)
        ,(427,'2016-10-30',8358.67)
        ,(427,'2016-10-31',8346.61)
        ,(541,'2016-10-05',900.44)
        ,(541,'2016-10-06',832.84)
        ,(541,'2016-10-11',637.54)
        ,(541,'2016-10-15',413.89)
        ,(541,'2016-10-16',413.89)
        ,(541,'2016-10-17',413.89)
        ,(541,'2016-10-18',1728.12)
        ,(541,'2016-10-22',265.27)
        ,(541,'2016-10-23',265.27)
        ,(541,'2016-10-24',265.27)
        ,(541,'2016-10-25',787.10)
        ,(541,'2016-10-26',1222.29)

Exemple de données pour 3 identifiants en octobre:

    CID         HDATE      DELAYSUM
    ----------- ---------- ---------------------------------------
    223       2016-10-16   15503.80
    223       2016-10-17   15493.82
    223       2016-10-18   15489.25
    223       2016-10-19   15417.08
    427       2016-10-01   10375.89
    427       2016-10-02   10375.89
    427       2016-10-03   10385.91
    427       2016-10-16   8448.57
    427       2016-10-17   8443.13
    427       2016-10-18   8440.64
    427       2016-10-19   8401.31
    427       2016-10-20   8411.20
    427       2016-10-21   8414.58
    427       2016-10-22   8414.58
    427       2016-10-23   8414.58
    427       2016-10-24   8401.23
    427       2016-10-25   8393.92
    427       2016-10-26   8379.14
    427       2016-10-27   8374.57
    427       2016-10-28   8358.67
    427       2016-10-29   8358.67
    427       2016-10-30   8358.67
    427       2016-10-31   8346.61
    541       2016-10-05   900.44
    541       2016-10-06   832.84
    541       2016-10-11   637.54
    541       2016-10-15   413.89
    541       2016-10-16   413.89
    541       2016-10-17   413.89
    541       2016-10-18   1728.12
    541       2016-10-22   265.27
    541       2016-10-23   265.27
    541       2016-10-24   265.27
    541       2016-10-25   787.10
    541       2016-10-26   1222.29

Sortie requise (date minimale pour chaque section de dates dans les identifiants (CID) avec date de fin pour chaque période (section)) Les segments sont séparés par un ou plusieurs jours:

    CID         HDATE      DELAYSUM    END_DATE
    ----------- ---------- ---------------------------------------
    223       2016-10-16   15503.80    2016-10-19
    427       2016-10-01   10375.89    2016-10-03
    427       2016-10-16   8448.57     2016-10-31
    541       2016-10-05   900.44      2016-10-06
    541       2016-10-11   637.54      2016-10-11
    541       2016-10-15   413.89      2016-10-18
    541       2016-10-22   265.27      2016-10-26

Coincé avec cette tâche pour le moment. Désolé pour mon anglais.

2
A. Anatolii 17 janv. 2017 à 15:22

2 réponses

Meilleure réponse

La clé ici est de classer les enregistrements par cid dans le même groupe si la différence de date entre les lignes consécutives est 1. Cette requête utilise une logique dateadd(day,-row_number() over (partition by cid order by hdate),hdate) pour le faire. Exécutez la requête interne pour voir comment les groupes sont affectés.

Par la suite, en utilisant les fonctions de fenêtre min, max et first_value, vous pouvez obtenir le min hdate, max hdate et la première valeur de la somme des délais par cid en utilisant les groupes précédemment attribués.

SELECT DISTINCT cid,
                min(hdate) over (partition BY cid, grp) AS hdate,
                first_value(delaysum) over (partition BY cid, grp ORDER BY hdate) AS delaysum,
                max(hdate) over (partition BY cid, grp) AS end_date
FROM (SELECT t.* ,
      dateadd(DAY,-row_number() over (partition BY cid ORDER BY hdate),hdate) AS grp
      FROM testsum t ) x
ORDER BY cid,hdate

Sample Demo

1
Vamsi Prabhala 17 janv. 2017 à 13:15

Une méthode pour résoudre ce problème est la différence des numéros de ligne:

select cid, min(hdate), max(hdate), min(delaysum)
from (select t.*,
             row_number() over (order by hdate) as seqnum,
             row_number() over (partition by cid order by hdate) as seqnum_c  
      from testsum t
     ) t
group by cid, (seqnum - seqnum_c);

ÉDITER:

En regardant de plus près, il semble que vous vouliez la première valeur plutôt que la valeur minimale. SQL Server n'offre pas (encore) first_value() comme fonction d'agrégation. Alors:

select cid, min(hdate), max(hdate), min(first_delaysum)
from (select t.*,
             first_value(delaysum) over (partition by cid, seqnum - seqnum_c order by hdate) as first_delaysum
      from (select t.*,
                   row_number() over (order by hdate) as seqnum,
                   row_number() over (partition by cid order by hdate) as seqnum_c   
            from testsum t
           ) t
     ) t
group by cid, (seqnum - seqnum_c);                   
0
Kinchit Dalwani 17 janv. 2017 à 13:29