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 pubsgoif exists(select * from sys.objects where NAME = 'processes' and type = 'u') drop table processesgocreate table processes(ID int IDENTITY,ProcessName char(2),TimeStart date,Successful bit)goSET IDENTITY_INSERT processes ONgoinsert 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 OFFgo--*********************************************--**************** 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