Plage de dates avec dates minimales et maximales d'un ensemble de données contenant des enregistrements avec une plage de dates continue

Question

Je dispose d'un jeu de données avec l'identifiant, le statut et la plage de dates des employés.
Le jeu de données d'entrée ci-dessous contient les détails d'un employé.
Les plages de dates dans les enregistrements sont continues (dans l'ordre exact) de sorte que la date de début de la deuxième ligne sera la date suivante de la date de fin de la première ligne.

Si un employé prend un congé de manière continue pendant différents mois, alors le tableau enregistre les informations avec une plage de dates séparée pour différents mois.
Par exemple : Dans l'ensemble d'entrée, l'employé a pris un congé maladie du '16-10-2016' au '31-12-2016' et est revenu le '1-1-2017'.
Il y a donc 3 enregistrements pour cet élément mais les dates sont continues. Dans la sortie, j'ai besoin de cela comme un seul enregistrement comme indiqué dans l'ensemble de sortie attendu.

ENTRÉE

Id  Status   StartDate   EndDate

1   Actif    1-9-2007    15-10-2016
1   Malade   16-10-2016  31-10-2016
1   Malade   1-11-2016   30-11-2016
1   Malade   1-12-2016   31-12-2016
1   Actif    1-1-2017    4-2-2017  
1   Non payé 5-2-2017    9-2-2017  
1   Actif    10-2-2017   11-2-2017 
1   Non payé 12-2-2017   28-2-2017 
1   Non payé 1-3-2017    31-3-2017 
1   Non payé 1-4-2017    30-4-2017 
1   Actif    1-5-2017    13-10-2017
1   Malade   14-10-2017  11-11-2017
1   Actif    12-11-2017  NULL   

ENREGISTREMENT ATTENDU

Id   Status    StartDate    EndDate

1    Actif    1-9-2007     15-10-2016
1    Malade      16-10-2016   31-12-2016
1    Actif    1-1-2017     4-2-2017  
1    Non payé    5-2-2017     9-2-2017  
1    Actif    10-2-2017    11-2-2017 
1    Non payé    12-2-2017    30-4-2017 
1    Actif    1-5-2017     13-10-2017
1    Malade      14-10-2017   11-11-2017
1    Actif    12-11-2017   NULL  

Je ne peux pas prendre min(startdate) et max(EndDate) regroupés par id, statut car si le même employé prend un autre congé maladie alors cette date de fin ('11-11-2017' dans l'exemple) sera la date de fin.

Est-ce que quelqu'un peut m'aider avec la requête dans SQL server 2014?

Answer 1

Il m'est soudainement apparu que c'était essentiellement un problème de lacunes et d'îles - donc j'ai complètement changé ma solution.
Pour que cette solution fonctionne, les dates n'ont pas à être consécutives.

Tout d'abord, créez et peuplez la table d'exemple (S'il vous plaît économisez-nous cette étape dans vos futures questions) :

DECLARE @T AS TABLE
(
    Id int,
    Status varchar(10),
    StartDate date,
    EndDate date
);

SET DATEFORMAT DMY; -- Cela est nécessaire en raison de la façon dont vous avez spécifié vos dates.

INSERT INTO @T (Id, Status, StartDate, EndDate) VALUES
(1, 'Actif', '1-9-2007', '15-10-2016'),
(1, 'Malade', '16-10-2016', '31-10-2016'),
(1, 'Malade', '1-11-2016', '30-11-2016'),
(1, 'Malade', '1-12-2016', '31-12-2016'),
(1, 'Actif', '1-1-2017', '4-2-2017'),
(1, 'Non payé', '5-2-2017', '9-2-2017'),
(1, 'Actif', '10-2-2017', '11-2-2017'),
(1, 'Non payé', '12-2-2017', '28-2-2017'),
(1, 'Non payé', '1-3-2017', '31-3-2017'),
(1, 'Non payé', '1-4-2017', '30-4-2017'),
(1, 'Actif', '1-5-2017', '13-10-2017'),
(1, 'Malade', '14-10-2017', '11-11-2017'),
(1, 'Actif', '12-11-2017',  NULL);

La (nouvelle) expression de table commune :

;WITH CTE AS
(
SELECT  Id, 
        Status, 
        StartDate, 
        EndDate,
        ROW_NUMBER() OVER(PARTITION BY Id ORDER BY StartDate)
        - ROW_NUMBER() OVER(PARTITION BY Id, Status ORDER BY StartDate) As IslandId,
        ROW_NUMBER() OVER(PARTITION BY Id ORDER BY StartDate DESC)
        - ROW_NUMBER() OVER(PARTITION BY Id, Status ORDER BY StartDate DESC) As ReverseIslandId
FROM @T
)

La (nouvelle) requête :

SELECT  DISTINCT Id,
        Status,
        MIN(StartDate) OVER(PARTITION BY IslandId, ReverseIslandId) As StartDate,
        NULLIF(MAX(ISNULL(EndDate, '9999-12-31')) OVER(PARTITION BY IslandId, ReverseIslandId), '9999-12-31') As EndDate

FROM CTE 
ORDER BY StartDate

Résultats (nouveaux) :

Id  Status  StartDate   EndDate
1   Actif   01.09.2007  15.10.2016
1   Malade  16.10.2016  31.12.2016
1   Actif   01.01.2017  04.02.2017
1   Non payé  05.02.2017  09.02.2017
1   Actif   10.02.2017  11.02.2017
1   Non payé  12.02.2017  30.04.2017
1   Actif   01.05.2017  13.10.2017
1   Malade  14.10.2017  11.11.2017
1   Actif   12.11.2017  NULL

Vous pouvez voir une démo en direct sur rextester.

Veuillez noter que la représentation sous forme de chaîne de dates en SQL doit être conforme à ISO 8601 - ce qui signifie soit aaaa-MM-jj ou aaaaMMjj car c'est non ambigu et sera toujours interprété correctement par SQL Server.

Answer 2

Voici une réponse alternative qui ne utilise pas LAG.

D'abord, j'ai besoin de prendre une copie de vos données de test:

DECLARE @table TABLE (Id INT, [Statut] VARCHAR(50), DateDebut DATE, DateFin DATE);
INSERT INTO @table SELECT 1, 'Actif', '20070901', '20161015';
INSERT INTO @table SELECT 1, 'Malade', '20161016', '20161031';
INSERT INTO @table SELECT 1, 'Malade', '20161101', '20161130';
INSERT INTO @table SELECT 1, 'Malade', '20161201', '20161231';
INSERT INTO @table SELECT 1, 'Actif', '20170101', '20170204';
INSERT INTO @table SELECT 1, 'Non payé', '20170205', '20170209';
INSERT INTO @table SELECT 1, 'Actif', '20170210', '20170211';
INSERT INTO @table SELECT 1, 'Non payé', '20170212', '20170228';
INSERT INTO @table SELECT 1, 'Non payé', '20170301', '20170331';
INSERT INTO @table SELECT 1, 'Non payé', '20170401', '20170430';
INSERT INTO @table SELECT 1, 'Actif', '20170501', '20171013';
INSERT INTO @table SELECT 1, 'Malade', '20171014', '20171111';
INSERT INTO @table SELECT 1, 'Actif', '20171112', NULL;

Ensuite, la requête est:

AVEC add_order COMME (
    SELECT
        *,
        ROW_NUMBER() OVER (ORDER BY DateDebut) AS order_id
    FROM
        @table),
liens COMME (
    SELECT
        a1.Id,
        a1.[Statut],
        a1.order_id,
        MIN(a1.order_id) AS start_order_id,
        MAX(ISNULL(a2.order_id, a1.order_id)) AS end_order_id,
        MIN(a1.DateDebut) AS DateDebut,
        MAX(ISNULL(a2.DateFin, a1.DateFin)) AS DateFin
    FROM
        add_order a1
        LEFT JOIN add_order a2 ON a2.Id = a1.Id AND a2.[Statut] = a1.[Statut] AND a2.order_id = a1.order_id + 1 AND a2.DateDebut = DATEADD(JOUR, 1, a1.DateFin)
    GROUP BY
        a1.Id,
        a1.[Statut],
        a1.order_id),
fusionne COMME (
    SELECT
        l1.Id,
        l1.[Statut],
        l1.[DateDebut],
        ISNULL(l2.DateFin, l1.DateFin) AS DateFin,
        ROW_NUMBER() OVER (PARTITION BY l1.Id, l1.[Statut], ISNULL(l2.DateFin, l1.DateFin) ORDER BY l1.order_id) AS link_id
    FROM
        liens l1
        LEFT JOIN liens l2 ON l2.order_id = l1.end_order_id)
SELECT
    Id,
    [Statut],
    DateDebut,
    DateFin
FROM
    fusionne
WHERE
    link_id = 1
ORDER BY
    DateDebut;

Les résultats sont:

Id  Statut  DateDebut   DateFin
1   Actif   2007-09-01  2016-10-15
1   Malade  2016-10-16  2016-12-31
1   Actif   2017-01-01  2017-02-04
1   Non payé    2017-02-05  2017-02-09
1   Actif   2017-02-10  2017-02-11
1   Non payé    2017-02-12  2017-04-30
1   Actif   2017-05-01  2017-10-13
1   Malade  2017-10-14  2017-11-11
1   Actif   2017-11-12  NULL

Comment ça fonctionne? Tout d'abord, j'ajoute un numéro de séquence, pour aider à fusionner les lignes contiguës ensemble. Ensuite, je détermine les lignes qui peuvent être fusionnées ensemble, ajoute un numéro pour identifier la première ligne de chaque ensemble qui peut être fusionnée, et enfin je sélectionne les premières lignes de la CTE finale. Remarquez que je dois également gérer les lignes qui ne peuvent pas être fusionnées, d'où les LEFT JOINs et les déclarations ISNULL.

Juste pour l'intérêt, voici à quoi ressemble la sortie de la CTE finale, avant que je ne filtre que les lignes avec un link_id de 1:

Id  Statut  DateDebut   DateFin link_id
1   Actif   2007-09-01  2016-10-15  1
1   Malade  2016-10-16  2016-12-31  1
1   Malade  2016-11-01  2016-12-31  2
1   Malade  2016-12-01  2016-12-31  3
1   Actif   2017-01-01  2017-02-04  1
1   Non payé    2017-02-05  2017-02-09  1
1   Actif   2017-02-10  2017-02-11  1
1   Non payé    2017-02-12  2017-04-30  1
1   Non payé    2017-03-01  2017-04-30  2
1   Non payé    2017-04-01  2017-04-30  3
1   Actif   2017-05-01  2017-10-13  1
1   Malade  2017-10-14  2017-11-11  1
1   Actif   2017-11-12  NULL        1

Answer 3

C'est un exemple de REGROUPEMENT ET DE FENÊTRE.

• Tout d'abord, vous définissez un point de réinitialisation pour chaque statut
• Sum pour définir un groupe
• Ensuite, obtenez les dates max/min de chaque groupe.

;avec x comme
(
    select Id, Status, StartDate,  EndDate,
           iif (lag(Status) over (order by Id, StartDate) = Status, null, 1) rst
    from   emp
), y comme
 (
    select Id, Status, StartDate, EndDate,
           sum(rst) over (order by Id, StartDate) grp
    from   x
 )

 select Id, 
        MIN(Status) as Status, 
        MIN(StartDate) StartDate, 
        MAX(EndDate) EndDate
 from   y
 group by Id, grp
 order by Id, grp

GO

Id | Status | StartDate           | EndDate            
-: | :----- | :------------------ | :------------------
 1 | Active | 01/09/2007 00:00:00 | 15/10/2016 00:00:00
 1 | Sick   | 16/10/2016 00:00:00 | 31/12/2016 00:00:00
 1 | Active | 01/01/2017 00:00:00 | 04/02/2017 00:00:00
 1 | Unpaid | 05/02/2017 00:00:00 | 09/02/2017 00:00:00
 1 | Active | 10/02/2017 00:00:00 | 11/02/2017 00:00:00
 1 | Unpaid | 12/02/2017 00:00:00 | 30/04/2017 00:00:00
 1 | Active | 01/05/2017 00:00:00 | 13/10/2017 00:00:00
 1 | Sick   | 14/10/2017 00:00:00 | 11/11/2017 00:00:00
 1 | Active | 12/11/2017 00:00:00 | _null_               

_dbfiddle ici_

Answer 4

Vous pouvez utiliser les fonctions lag() et lead() ensemble pour vérifier l'état précédent et suivant

WITH CTE AS
(
    select  *, 
            COALESCE(LEAD(status) OVER(ORDER BY (select 1)), '0') Nstatus,
            COALESCE(LAG(status) OVER(ORDER BY (select 1)), '0') Pstatus
   from table
)

SELECT * FROM CTE
WHERE (status <> Nstatus AND status <> Pstatus) OR
      (status <> Pstatus)