Count consecutive failures - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Count consecutive failures

Author

Topic

neoblasted
Starting Member

6 Posts

Posted - 2011-07-20 : 15:59:14

Hi, All

I have a server table with a status and a date.

like so (FALSE=NO ERROR, TRUE=ERROR
Date targetName ERROR

2011/02/01 serverX FALSE
2011/02/02 serverX FALSE
2011/02/04 serverX FALSE

2011/02/01 servery TRUE
2011/02/02 servery FALSE
2011/02/04 servery TRUE

2011/02/01 serverW FALSE
2011/02/02 serverW TRUE
2011/02/04 serverW TRUE

2011/02/01 serverA TRUE
2011/02/02 serverA TRUE
2011/02/04 serverA TRUE

With this table im trying to create a view that will display this

TargetName Consecutive Failure (If the last one has failed)

serverX 0
servery 1
serverW 2
serverA 3

Iv been trying for days, cannot found it.

Thanks in advance

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-20 : 20:31:07

This is one way to do it:

-- TEST DATA
create table #tmp (report_date datetime, server_name varchar(32),	has_error varchar(32));
insert into #tmp

select '2011/02/01','serverX',' FALSE' union all
select '2011/02/02','serverX',' FALSE' union all
select '2011/02/04','serverX',' FALSE' union all

select '2011/02/01','servery',' TRUE' union all
select '2011/02/02','servery',' FALSE' union all
select '2011/02/04','servery',' TRUE' union all

select '2011/02/01','serverW',' FALSE' union all
select '2011/02/02','serverW',' TRUE' union all
select '2011/02/04','serverW',' TRUE' union all

select '2011/02/01','serverA',' TRUE' union all
select '2011/02/02','serverA',' TRUE' union all
select '2011/02/04','serverA',' TRUE';

-- QUERY
;with cte1 as
(
	select *,
		row_number() over (order by server_name,report_date)-
		row_Number() over (partition by server_name,has_error 
	order by server_name,report_date) as group_number
	from #tmp
),
cte2 as
(
	select server_name, count(*) group_count
	from cte1 
	where has_error = ' TRUE' 
	group by server_name,group_number
)
select t.server_name, isnull(max(group_count),0)
from #tmp t left join cte2 c on c.server_name = t.server_name
group by t.server_name

-- CLEANUP
drop table #tmp;

This probably is more complicated than it needs to be. When I started writing it, I thought this would be the easier method, but now I am thinking that it would be more compact and simpler to use a recursive CTE.

neoblasted
Starting Member

6 Posts

Posted - 2011-07-21 : 09:01:27

Hi,
thanks for your help, you example is kind of working, you calculate all consecutive failures. But what i need exactly, is the count the consecutive failures when THE LAST ONE has failed.

example

Server Name InError

serverX TRUE
serverX TRUE
Serverx FALSE

ServerA TRUE
serverA False
ServerA True

I should get

serverx 0 (Because the last one has no error)
servera 1

Thanks

Sachin.Nand

2937 Posts

Posted - 2011-07-21 : 09:06:28

Whatshould be the o/p of this ?

ServerA TRUE
ServerA TRUE
ServerA TRUE
serverA False
ServerA True

PBUH

neoblasted
Starting Member

6 Posts

Posted - 2011-07-21 : 09:13:45

ServerA TRUE
ServerA TRUE
ServerA TRUE
serverA False
ServerA True

OUTPUT is

serverA 1

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-21 : 09:57:21

[code]-- TEST DATA
create table #tmp (report_date datetime, server_name varchar(32), has_error varchar(32));
insert into #tmp

select '2011/02/01','serverX',' FALSE' union all
select '2011/02/02','serverX',' FALSE' union all
select '2011/02/04','serverX',' FALSE' union all

select '2011/02/01','servery',' TRUE' union all
select '2011/02/02','servery',' FALSE' union all
select '2011/02/04','servery',' TRUE' union all

select '2011/02/01','serverW',' FALSE' union all
select '2011/02/02','serverW',' TRUE' union all
select '2011/02/04','serverW',' TRUE' union all

select '2011/02/01','serverA',' TRUE' union all
select '2011/02/02','serverA',' TRUE' union all
select '2011/02/04','serverA',' TRUE';

-- QUERY
; WITH cte1 AS
(
SELECT *, ROW_NUMBER() OVER (PARTITION BY server_name ORDER BY report_date DESC) AS RN
FROM #tmp
),
cte2 AS
(
SELECT *,1 AS consecutive_errors FROM cte1 WHERE rn=1 AND has_error = ' TRUE'
UNION ALL
SELECT c1.report_date,c1.server_name,c1.has_error,c1.RN, c2.consecutive_errors+1
FROM cte1 c1 INNER JOIN cte2 c2 ON c2.rn+1 = c1.rn AND c2.server_name = c1.server_name
WHERE c1.has_error = ' TRUE'
)
SELECT t1.server_name,ISNULL ( MAX(consecutive_errors),0) AS Errors
FROM #tmp t1 LEFT JOIN cte2 c2 ON c2.server_name = t1.server_name
GROUP BY t1.server_name;

-- CLEANUP
drop table #tmp;[/code]

neoblasted
Starting Member

6 Posts

Posted - 2011-07-21 : 10:04:42

OMG It works like a charms, thanks man you really helped me sunitabeck!

neoblasted
Starting Member

6 Posts

Posted - 2011-07-21 : 10:39:56

Too bad, the queries is timing out. Its taking too much time with 18000 Entries..

thanks anyway

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-21 : 11:20:31

See if this is any faster (and if it gives the right answers!):

SELECT
	server_name, MAX(report_date),
	COUNT(*) AS consecutive_errors
FROM
	#tmp t1
WHERE
	NOT EXISTS
	(
		SELECT * FROM #tmp t2
		WHERE t2.server_name = t1.server_name
		AND t2.report_date >= t1.report_date
		AND t2.has_error = ' FALSE'
	)
GROUP BY
	server_name
HAVING
	MAX(report_date) = MAX(CASE WHEN has_error = ' TRUE' THEN report_date END)

neoblasted
Starting Member

6 Posts

Posted - 2011-07-21 : 13:28:42

yep, it works. and its fast. Thanks a million!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources