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 79 - Finding the Islands

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

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

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.

Expected Results

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

Rules

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

Sample Script

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

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
5
Liked
33
Comments
54
Solutions



Submit

TSQL Challenge 79 - Finding the Islands

Managed Windows Shared Hosting by OrcsWeb

Copyright © Rivera Informatic Private Ltd.