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
 General SQL Server Forums
 New to SQL Server Programming
 SQL Server query question

Author  Topic 

TomerMans
Starting Member

2 Posts
Posted - 2012-12-18 : 21:03:55
Hi All, I would really appreciate any help with the below:

I have a table containing a log of processes which have run.
The table fields are:
[ID] ( - unique identifier of a process instance run),
[ProcessName] ( - name of process),
[TimeStart] ( - date at which the process started),
[Successful] ( - bit column indicating whether the process run was successful)

I need assistance in writing a query which returns a summary of all those processes that have failed at least three times in sequence. Each row should contain the ProcessName, Number of sequential failures, The date of the first failure.

Note that the processes do not run in a any particular order and the ID of the run is not in the order of the runs either. Also, it is not known at the outset how many processes there are or what their names are.

The following script generates a table containing sample process log data in database pubs:


--***********************************************
--**************** Start SCRIPT *****************
--***********************************************
use pubs
go

if exists(select * from sys.objects where NAME = 'processes' and type = 'u') 
drop table processes
go

create table processes
(
ID int IDENTITY,
ProcessName char(2),
TimeStart date,
Successful bit
)
go

SET IDENTITY_INSERT processes ON
go

insert into processes (ID, ProcessName, TimeStart, Successful) values (23,'AA','1 December 2012',0)
insert into processes (ID, ProcessName, TimeStart, Successful) values (5,'BB','15 December 2012',1)
insert into processes (ID, ProcessName, TimeStart, Successful) values (14,'BB','1 December 2012',1)
insert into processes (ID, ProcessName, TimeStart, Successful) values (32,'AA','15 November 2012',0)
insert into processes (ID, ProcessName, TimeStart, Successful) values (4,'AA','1 November 2012',0)
insert into processes (ID, ProcessName, TimeStart, Successful) values (26,'CC','1 November 2012',1)
insert into processes (ID, ProcessName, TimeStart, Successful) values (34,'CC','15 October 2012',1)
insert into processes (ID, ProcessName, TimeStart, Successful) values (35,'CC','1 October 2012',1)
insert into processes (ID, ProcessName, TimeStart, Successful) values (22,'BB','1 October 2012',1)
insert into processes (ID, ProcessName, TimeStart, Successful) values (25,'BB','15 September 2012',0)
insert into processes (ID, ProcessName, TimeStart, Successful) values (31,'CC','1 September 2012',1)
insert into processes (ID, ProcessName, TimeStart, Successful) values (18,'BB','1 September 2012',1)
insert into processes (ID, ProcessName, TimeStart, Successful) values (6,'AA','15 August 2012',0)
insert into processes (ID, ProcessName, TimeStart, Successful) values (15,'CC','1 August 2012',1)
insert into processes (ID, ProcessName, TimeStart, Successful) values (13,'AA','1 August 2012',1)
insert into processes (ID, ProcessName, TimeStart, Successful) values (33,'AA','1 July 2012',0)
insert into processes (ID, ProcessName, TimeStart, Successful) values (30,'BB','1 July 2012',0)
insert into processes (ID, ProcessName, TimeStart, Successful) values (21,'CC','1 July 2012',1)
insert into processes (ID, ProcessName, TimeStart, Successful) values (19,'AA','25 June 2012',0)
insert into processes (ID, ProcessName, TimeStart, Successful) values (3,'AA','15 June 2012',1)
insert into processes (ID, ProcessName, TimeStart, Successful) values (24,'AA','1 June 2012',1)
insert into processes (ID, ProcessName, TimeStart, Successful) values (16,'BB','1 May 2012',0)
insert into processes (ID, ProcessName, TimeStart, Successful) values (29,'CC','15 May 2012',1)
insert into processes (ID, ProcessName, TimeStart, Successful) values (36,'CC','1 May 2012',0)
insert into processes (ID, ProcessName, TimeStart, Successful) values (27,'BB','15 April 2012',0)
insert into processes (ID, ProcessName, TimeStart, Successful) values (8,'CC','1 April 2012',0)
insert into processes (ID, ProcessName, TimeStart, Successful) values (11,'BB','1 April 2012',0)
insert into processes (ID, ProcessName, TimeStart, Successful) values (20,'BB','1 March 2012',0)
insert into processes (ID, ProcessName, TimeStart, Successful) values (12,'CC','15 March 2012',0)
insert into processes (ID, ProcessName, TimeStart, Successful) values (7,'CC','1 March 2012',1)
insert into processes (ID, ProcessName, TimeStart, Successful) values (10,'AA','1 February 2012',0)
insert into processes (ID, ProcessName, TimeStart, Successful) values (1,'BB','15 February 2012',0)
insert into processes (ID, ProcessName, TimeStart, Successful) values (28,'BB','1 February 2012',0)
insert into processes (ID, ProcessName, TimeStart, Successful) values (17,'CC','1 January 2012',0)
insert into processes (ID, ProcessName, TimeStart, Successful) values (9,'AA','15 January 2012',0)
insert into processes (ID, ProcessName, TimeStart, Successful) values (2,'AA','1 January 2012',0)

SET IDENTITY_INSERT processes OFF
go
--*********************************************
--**************** END SCRIPT *****************
--*********************************************


The required query should return the following result (for the case of the sample data provided above):


ProcessName         SuccessiveFailures          Date of First Failure
     AA                     4                       15 Aug 2012
     AA                     3                       01 Jan 2012
     BB                     7                       01 Feb 2012
     CC                     3                       15 Mar 2012


Pls can you tell me what query returns the above result? Thanks everyone..

Cheers!
TomerMans

khtan
In (Som, Ni, Yak)

17689 Posts
Posted - 2012-12-19 : 00:25:48
[code]
; with cte as
(
select row_no = row_number() over (partition by ProcessName order by TimeStart),
*
from processes
),
cte2 as
(
select *,
rank = rank() over ( partition by ProcessName order by Successful, row_no),
grp = row_no - rank() over ( partition by ProcessName order by Successful, row_no)
from cte
)
select ProcessName,
SuccessiveFailures = count(case when Successful = 0 then 1 end),
DateofFirstFailure = min(TimeStart)
from cte2
group by ProcessName, grp
having count(case when Successful = 0 then 1 end) >= 3
order by ProcessName
[/code]

KH
[spoiler]Time is always against us[/spoiler]
Go to Top of Page

LoztInSpace
Aged Yak Warrior

940 Posts
Posted - 2012-12-19 : 01:32:50
I am in a rush, but try this:

select
processname,MIN(timestart) earliest, COUNT(*)
from
(
select
processname, timestart,
(select min(timestart) from processes p3 where p1.processname=p3.processname and p3.timestart > p1.timestart and successful=1) SuccessDate
,successful
from
processes p1
where successful=0
) cd
group by processname,SuccessDate
having COUNT(*)>=3
order by processname

Great example of a question BTW - love the create scripts.
Go to Top of Page

TomerMans
Starting Member

2 Posts
Posted - 2012-12-19 : 08:58:17
Thank you LoztInSpace and khtan both of your queries worked well and return the desired results! :)

How does one learn to solve this level of SQL problems??

TomerMans
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-12-19 : 09:02:03
quote:
Originally posted by TomerMans

Thank you LoztInSpace and khtan both of your queries worked well and return the desired results! :)

How does one learn to solve this level of SQL problems??

TomerMans

By experience - either self or learning from others

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site