clear markdown compare help donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 497270 fiddles created (9498 in the last week).

CREATE TABLE #Monitoring(RowID int,DateStamp datetime2,Success bit,Error varchar(20))
 hidden batch(es)


INSERT INTO #Monitoring(RowID,DateStamp,Success,Error) VALUES (1001 ,'5/24/2019 11:23am', 1 ,'None') ,(1004 ,'5/24/2019 11:24am', 1 ,'bla') ,(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')
10 rows affected
 hidden batch(es)


;WITH CTE_GROUP AS ( SELECT Success, Error, DateStamp, ROW_NUMBER() OVER (PARTITION BY Success ORDER BY DateStamp) - ROW_NUMBER() OVER (ORDER BY DateStamp) as SuccesDateStamp, ROW_NUMBER() OVER (PARTITION BY Error ORDER BY DateStamp) - ROW_NUMBER() OVER (ORDER BY DateStamp) as ErrorDateStamp FROM #Monitoring ) SELECT MIN(DateStamp) as Starting, MAX(DateStamp) as Ending, Count(*) as Polls, Success, Error FROM CTE_GROUP GROUP BY Success, Error,SuccesDateStamp,ErrorDateStamp ORDER BY Starting;
Starting Ending Polls Success Error
24/05/2019 11:23:00 24/05/2019 11:23:00 1 True None
24/05/2019 11:24:00 24/05/2019 11:24:00 1 True bla
24/05/2019 11:25:00 24/05/2019 11:25:00 1 True None
24/05/2019 11:26:00 24/05/2019 11:27:00 2 False SQL Timeout
24/05/2019 11:28:00 24/05/2019 11:29:00 2 True None
24/05/2019 11:30:00 24/05/2019 11:31:00 2 False Planned Maintenance
24/05/2019 11:32:00 24/05/2019 11:32:00 1 True None
 hidden batch(es)


;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 #Monitoring ) 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;
Starting Ending Polls Success Error
24/05/2019 11:23:00 24/05/2019 11:23:00 1 True None
24/05/2019 11:24:00 24/05/2019 11:24:00 1 True bla
24/05/2019 11:25:00 24/05/2019 11:25:00 1 True None
24/05/2019 11:26:00 24/05/2019 11:27:00 2 False SQL Timeout
24/05/2019 11:28:00 24/05/2019 11:29:00 2 True None
24/05/2019 11:30:00 24/05/2019 11:31:00 2 False Planned Maintenance
24/05/2019 11:32:00 24/05/2019 11:32:00 1 True None
 hidden batch(es)