SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 SQL Server query question		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

TomerMans
Starting Member

2 Posts
Posted - 12/18/2012 :  21:03:55  Show Profile  Reply with Quote
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
Edited by - TomerMans on 12/18/2012 21:08:29

khtan
In (Som, Ni, Yak)

Singapore
16746 Posts
Posted - 12/19/2012 :  00:25:48  Show Profile  Reply with Quote 

; 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


KH
Time is always against us
Go to Top of Page

LoztInSpace
Aged Yak Warrior

876 Posts
Posted - 12/19/2012 :  01:32:50  Show Profile  Reply with Quote
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 - 12/19/2012 :  08:58:17  Show Profile  Reply with Quote
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

India
47099 Posts
Posted - 12/19/2012 :  09:02:03  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000