4

Let's say I have the following (contrived) table of monitoring data:

RowID       DateStamp            Success         Error
-----       -----------------    -------         -----------
1001        5/24/2019 11:23am    1               None 
1004        5/24/2019 11:24am    1               None 
1005        5/24/2019 11:25am    1               None 
1009        5/24/2019 11:26am    0               SQL Timeout
1018        5/24/2019 11:27am    0               SQL Timeout 
1019        5/24/2019 11:28am    1               None 
1026        5/24/2019 11:29am    1               None 
1035        5/24/2019 11:30am    0               Planned Maintenance
1100        5/24/2019 11:31am    0               Planned Maintenance
1111        5/24/2019 11:32am    1               None 

I'd like to group rows with the same status and error, but only if they fall next to each other in sequence by date or by ID (ID will be ordered correctly, but is not guaranteed to be sequential), as follows:

Starting             Ending               Polls  Success   Error
-----------------    -----------------    -----  -------   -----------
5/24/2019 11:23am    5/24/2019 11:25am    3      1         None
5/24/2019 11:26am    5/24/2019 11:27am    2      0         SQL Timeout
5/24/2019 11:28am    5/24/2019 11:29am    2      1         None
5/24/2019 11:30am    5/24/2019 11:31am    2      0         Planned Maintenance
5/24/2019 11:32am    5/24/2019 11:32am    1      1         None

A simple GROUP BY doesn't work:

SELECT MIN(DateStamp) as Starting, MAX(DateStamp) as Ending, 
      Count(*) as Polls, Success, Error
FROM myTable
GROUP BY Success, Error
ORDER BY Starting

Since that combines all rows with the same status/error, regardless of when they occur:

Starting             Ending               Polls  Success   Error
-----------------    -----------------    -----  -------   -----------
5/24/2019 11:23am    5/24/2019 11:32am    6      1         None
5/24/2019 11:26am    5/24/2019 11:27am    2      0         SQL Timeout
5/24/2019 11:30am    5/24/2019 11:31am    2      0         Planned Maintenance

Is there an easy way to do this in SQL Server 2012?

2

Here is one example of creating groups with the use of ROW_NUMBER()

;WITH CTE_GROUP AS (
  SELECT 
  Success, 
  Error,
  DateStamp,
  ROW_NUMBER() OVER (PARTITION BY Success, Error ORDER BY DateStamp)
      - ROW_NUMBER() OVER (ORDER BY DateStamp) as SuccesErrorDateStamp,
FROM dbo.myTable
)
SELECT MIN(DateStamp) as Starting, MAX(DateStamp) as Ending, 
      Count(*) as Polls, Success, Error
FROM CTE_GROUP
GROUP BY Success, Error,SuccesErrorDateStamp
ORDER BY Starting;

Resulting in

Starting                    Ending                      Polls   Success Error
2019-05-24 11:23:00.0000000 2019-05-24 11:25:00.0000000 3       1       None
2019-05-24 11:26:00.0000000 2019-05-24 11:27:00.0000000 2       0       SQL Timeout
2019-05-24 11:28:00.0000000 2019-05-24 11:29:00.0000000 2       1       None
2019-05-24 11:30:00.0000000 2019-05-24 11:31:00.0000000 2       0       Planned Maintenance
2019-05-24 11:32:00.0000000 2019-05-24 11:32:00.0000000 1       1       None

DB<>Fiddle

Thanks @AndriyM for pointing out that I only need one ROW_NUMBER() column

3

An alternative way to identify the islands is using the LAG analytic function and the SUM() window aggregate function:

WITH
  sentinels AS
  (
    SELECT
      DateStamp,
      Success,
      Error,
      Grp = CASE
              WHEN Success = LAG(Success) OVER (ORDER BY DateStamp ASC)
               AND Error   = LAG(Error  ) OVER (ORDER BY DateStamp ASC)
              THEN 0
              ELSE 1
            END
    FROM
      dbo.myTable
  ),
  fullyMarked AS
  (
    SELECT
      DateStamp,
      Success,
      Error,
      Grp = SUM(Grp) OVER (ORDER BY DateStamp ASC ROWS UNBOUNDED PRECEDING)
    FROM
      sentinels
  )
SELECT
  Starting  = MIN(DateStamp),
  Ending    = MAX(DateStamp),
  Polls     = COUNT(*),
  Success,
  Error
FROM
  fullyMarked
GROUP BY
  Success,
  Error,
  Grp
ORDER BY
  Starting ASC
;

The sentinels CTE marks the first row of each island with a 1 and the others with a 0:

RowID  DateStamp          Success  Error                Grp
-----  -----------------  -------  -------------------  ---
1001   5/24/2019 11:23am  1        None                 1
1004   5/24/2019 11:24am  1        None                 0
1005   5/24/2019 11:25am  1        None                 0
1009   5/24/2019 11:26am  0        SQL Timeout          1
1018   5/24/2019 11:27am  0        SQL Timeout          0
1019   5/24/2019 11:28am  1        None                 1
1026   5/24/2019 11:29am  1        None                 0
1035   5/24/2019 11:30am  0        Planned Maintenance  1
1100   5/24/2019 11:31am  0        Planned Maintenance  0
1111   5/24/2019 11:32am  1        None                 1

The fullyMarked CTE takes the output of sentinels and calculates the running total of the Grp column, thus effectively assigning a unique ID to each island:

RowID  DateStamp          Success  Error                Grp
-----  -----------------  -------  -------------------  ---
1001   5/24/2019 11:23am  1        None                 1    -- 1
1004   5/24/2019 11:24am  1        None                 1    -- 1+0
1005   5/24/2019 11:25am  1        None                 1    -- 1+0+0
1009   5/24/2019 11:26am  0        SQL Timeout          2    -- 1+0+0+1
1018   5/24/2019 11:27am  0        SQL Timeout          2    -- 1+0+0+1+0
1019   5/24/2019 11:28am  1        None                 3    -- 1+0+0+1+0+1
1026   5/24/2019 11:29am  1        None                 3    -- 1+0+0+1+0+1+0
1035   5/24/2019 11:30am  0        Planned Maintenance  4    -- 1+0+0+1+0+1+0+1
1100   5/24/2019 11:31am  0        Planned Maintenance  4    -- 1+0+0+1+0+1+0+1+0
1111   5/24/2019 11:32am  1        None                 5    -- 1+0+0+1+0+1+0+1+0+1

The final step is to just group by Success, Error, Grp and aggregate data of other columns as required, which gives you the expected result.

You can use this live demo at dbfiddle logodb<>fiddle.uk for playing with this solution.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.