The challenge idea is taken from a problem discussed in the MSDN TSQL forum. The challenge is to find the Episode and Sequence based on interval. Episode is separated by IntervalDate. One episode can contain multiple Sequence number.

    
ID Interval IntervalDate
-- -------- ------------
11		  1	01-31-2012
11	  	  2	NULL
11	  	  3	NULL
11	  	  4	04-02-2012	
11	  	  5 03-02-2012
11	  	  6 NULL
11	  	  7 NULL

    
ID Interval IntervalDate Episode Sequence 
-- -------- ------------ ------- --------
11 1		01-31-2012	 1       1 
11 2		NULL         1       2
11 3		NULL		 1       3
11 4		04-02-2012   2       1	
11 5		03-02-2012   3       1
11 6		NULL         3       2  
11 7		NULL         3       3

1. The presence of a date is what starts a new episode.
2. The output should be ordered by ID, Episode, Sequence.
3. No assumption should be made about the physical order of the rows in the input table.
4. The dates within an ID set can be in any order. You are required to interpret the input rows in the order of the Interval number.
5. The Interval numbers are always unique within an ID set.
6. There can be duplicate dates within an ID set.
7. The Interval column always starts at 1 and has no gaps.

Use the TSQL Script given below to generate the source table and fill them with sample data.

 
IF OBJECT_ID('TC75','U') IS NOT NULL BEGIN   
	DROP TABLE TC75  
END  
GO   
CREATE TABLE TC75 (
	ID INT, 
	Interval INT,
    IntervalDate DATETIME
)  
GO    
INSERT INTO TC75(ID,Interval,IntervalDate)  
SELECT 11,1,'01-31-2012' UNION ALL
SELECT 11,2,NULL UNION ALL
SELECT 11,3,NULL UNION ALL
SELECT 11,4,'04-02-2012' UNION ALL	
SELECT 11,5,'03-02-2012' UNION ALL
SELECT 11,6,NULL UNION ALL
SELECT 11,7,NULL 

SELECT * FROM TC75
GO

1. The solution should be a single query that starts with a "SELECT" or “;WITH”

1. Read the Submission Guidelines and make sure that your solution follows them.
2. If you would like to use a Tally Table, you can use the script given here. Your solution should not include the script to create and populate the tally table. You can assume that the tally table will be available in the database where the evaluation team will run your Code.
3. Your solution can use features from all versions up to and including SQL Server 2012.