catalyph catalyph - 8 months ago 85
SQL Question

For Loop or while loop SQL consecutive dates

I have a table that tells us the date something ran and if it failed or not.
I need a query that will get the last failed date and then the last consecutive date that it failed, so basically the starting date of the failure to the end date of the failures where the dates are consecutive in between.

Table

EntityID | Date | Entities | Loaded | Status |
B0034 2017-11-16 54 full Success
B0033 2017-11-16 54 full Success
B0034 2017-11-15 54 full Success
B0033 2017-11-15 58 full Failed
B0035 2017-11-15 88 full Success
B0033 2017-11-14 56 full Failed
B0036 2017-11-15 50 full Success
B0033 2017-11-13 56 full Failed
B0037 2017-11-15 50 full Success
B0033 2017-11-12 34 full Success
B0034 2017-11-14 50 full Success
B0035 2017-11-14 38 full Success
B0033 2017-11-11 50 full Success
B0037 2017-11-14 59 full Success
B0033 2017-11-10 11 full Failed
B0037 2017-11-13 67 full Success
B0037 2017-11-12 78 full Success
B0033 2017-11-09 32 full Failed
B0033 2017-11-08 99 full Failed
B0033 2017-11-17 33 full Success


in this case For ID B0033 I would need:

EntityID | Date | Entities | Loaded | Status |
B0033 2017-11-15 58 full Failed
B0033 2017-11-13 56 full Failed


As B0033 Failed consecutively from the 13th to the 15th

Sample DDL as below:

CREATE TABLE #Sample (EntityID varchar(5),
[Date] date,
[Entities] int,
Loaded varchar(4),
[Status] varchar(7));

INSERT INTO #Sample
VALUES
('B0034','20171116',54,'full','Success'),
('B0033','20171116',54,'full','Success'),
('B0034','20171115',54,'full','Success'),
('B0033','20171115',58,'full','Failed'),
('B0035','20171115',88,'full','Success'),
('B0033','20171114',56,'full','Failed'),
('B0036','20171115',50,'full','Success'),
('B0033','20171113',56,'full','Failed'),
('B0037','20171115',50,'full','Success'),
('B0033','20171112',34,'full','Success'),
('B0034','20171114',50,'full','Success'),
('B0035','20171114',38,'full','Success'),
('B0033','20171111',50,'full','Success'),
('B0037','20171114',59,'full','Success'),
('B0033','20171110',11,'full','Failed'),
('B0037','20171113',67,'full','Success'),
('B0037','20171112',78,'full','Success'),
('B0033','20171109',32,'full','Failed'),
('B0033','20171108',99,'full','Failed'),
('B0033','20171117',33,'full','Success');
GO

SELECT *
FROM #Sample;
GO

DROP TABLE #Sample;

Answer Source

I think this is what you're after:

WITH Groups AS(
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY EntityID ORDER BY Date ASC) - 
           ROW_NUMBER() OVER (PARTITION BY EntityID, [Status] ORDER BY Date ASC) AS Grp
    FROM #Sample),
TopBottom AS(
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY EntityID, Grp ORDER BY Date ASC) AS RNAsc,
           ROW_NUMBER() OVER (PARTITION BY EntityID, Grp ORDER BY Date DESC) AS RNDesc,
           DENSE_RANK() OVER (PARTITION BY EntityID ORDER BY Grp DESC) AS Ranking
    FROM Groups
    WHERE [Status] = 'Failed')
SELECT EntityID,
       [Date],
       Entities,
       Loaded,
       [Status]
FROM TopBottom
WHERE (RNAsc = 1 OR RNDesc = 1)
  AND Ranking = 1;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download