Getting Started with Adobe After Effects - Part 6: Motion Blur
First Time? You can support us by signing up. It takes only 5 seconds. Click here to sign up. If you already have an account, click here to login.

TSQL Challenge 75 - Find the Episode and Sequence based on incomplete data

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.

Database Source Control in just 5 minutes
It takes just 5 minutes to connect your SQL databases to source control. Got 5 minutes to spare? Bring your database development process forward by 5 years. Get started now.

Sample Data

    
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

Expected Results

    
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

Rules

  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.

Sample Script

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

Restrictions

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

Notes

  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.
Database Source Control in just 5 minutes
It takes just 5 minutes to connect your SQL databases to source control. Got 5 minutes to spare? Bring your database development process forward by 5 years. Get started now.

puzzles
13 · 13% · 4057
Submit a Solution
Previous Challenge
Next Challenge
1
Liked
39
Comments
69
Solutions



Submit

TSQL Challenge 75 - Find the Episode and Sequence based on incomplete data

Managed Windows Shared Hosting by OrcsWeb

Copyright © Rivera Informatic Private Ltd.