TSQL Challenge 79 - Finding the Islands

The following TSQL template may be used to perform the basic testing of your solutions for TSQL Challenge 79 - Finding the Islands.

  /**********************************************************************
  ----------------------------------------------
  BASIC Testing Sandbox for TSQL Challenge 79
  Copyright © beyondrelational.com
  ----------------------------------------------

  Notes:
  1. Copy this template and paste in your SSMS Query editor

  2. Insert your query (solution for TSQL Challenge 79)
  in the Section between "Insert your query below"
  and "Insert your query above"

  3. Run the whole batch. If you see
  "Invalid Results...Failed!" getting printed in output
  window, there is something wrong in the query.

  ***********************************************************************/
  /*
  Create a wrapper procedure around the solution
  */
  IF OBJECT_ID('tempdb..#TestSolution') IS NOT NULL BEGIN
	DROP PROCEDURE #TestSolution
END
GO
CREATE PROCEDURE #TestSolution AS
BEGIN

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

	CREATE TABLE TC79(
		PatientID INT,	
		AdmissionDate DATETIME,
		DischargeDate DATETIME,
		Cost MONEY
	)

	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  
  ;

  /* Insert your query below */

  /* Insert your query above */
  END
  GO

  SET NOCOUNT ON
  /*
  Start the testing phase
  */

 DECLARE @x TABLE (
		AutoID INT IDENTITY,
		PatientID INT,
		AdmissionDate DATETIME,
		DischargeDate DATETIME,
		Cost MONEY
	)

  -- Execute the code and
  INSERT @x EXEC #TestSolution

  DECLARE @z TABLE (
		AutoID INT IDENTITY,
		PatientID INT,
		AdmissionDate DATETIME,
		DischargeDate DATETIME,
		Cost MONEY
	)

  INSERT INTO @z(PatientID,AdmissionDate,DischargeDate,Cost)
  SELECT 709,'2011-07-27','2011-08-23',2520.00 UNION ALL
  SELECT 709,'2011-08-31','2011-09-14',1350.00 UNION ALL
  SELECT 709,'2011-12-01','2011-12-31',2790.00 UNION ALL
  SELECT 1624,'2011-06-07','2011-08-02',5130.00


  -- Match the output with expected result.
  IF EXISTS(
  (SELECT * FROM @x EXCEPT SELECT * FROM @z)
   UNION ALL
  (SELECT * FROM @z EXCEPT SELECT * FROM @x))
  BEGIN
  PRINT 'Invalid Results...Failed!'
  END ELSE BEGIN
  PRINT 'Congratulations...Passed!'
END