The challenge idea is taken from a problem discussed in the MSDN TSQL forum. The challenge is to find the Islands(gaps) in sequential dates. You need to write a query to identify continuous intervals from the start date and end date.

For example,

01/01/2012 - 01/17/2012
01/18/2010 - 02/20/2012

The above two intervals should be considered as 01/01/2012 - 02/20/2012

PatientID AdmissionDate DischargeDate Cost
--------- ------------- ------------- -------
709       2011-07-27    2011-07-31    450.00
709       2011-08-01    2011-08-23    2070.00
709       2011-08-31    2011-08-31    90.00
709       2011-09-01    2011-09-14    1260.00
709       2011-12-01    2011-12-31    2790.00
1624      2011-06-07    2011-06-28    1980.00
1624      2011-06-29    2011-07-31    2970.00
1624      2011-08-01    2011-08-02    180.00

There is a table which maintains the Patient admission and discharge information. Each admission comes as one new record. But when there is a continuous internal, you should show them as a single row in output.

PatientID AdmissionDate DischargeDate Cost
--------- ------------- ------------- -------
709       2011-07-27    2011-08-23    2520.00
709       2011-08-31    2011-09-14    1350.00
709       2011-12-01    2011-12-31    2790.00
1624      2011-06-07    2011-08-02    5130.00

1. The output should be ordered by PatientID, AdmissionDate.
2. For any patient there will be no overlapping date intervals.

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

    
IF OBJECT_ID('TC79','U') IS NOT NULL BEGIN
  DROP TABLE TC79
END
GO

CREATE TABLE TC79(
	PatientID INT,	
	AdmissionDate DATETIME,
	DischargeDate DATETIME,
    Cost MONEY
)
GO
INSERT INTO TC79(PatientID,AdmissionDate,DischargeDate,Cost)
SELECT 709,'2011-07-27','2011-07-31',450.00 UNION ALL
SELECT 709,'2011-08-01','2011-08-23',2070.00 UNION ALL
SELECT 709,'2011-08-31','2011-08-31',90.00 UNION ALL
SELECT 709,'2011-09-01','2011-09-14',1260.00 UNION ALL
SELECT 709,'2011-12-01','2011-12-31',2790.00 UNION ALL
SELECT 1624,'2011-06-07','2011-06-28',1980.00 UNION ALL
SELECT 1624,'2011-06-29','2011-07-31',2970.00 UNION ALL
SELECT 1624,'2011-08-01','2011-08-02',180.00

SELECT * FROM TC79
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.