TSQL Challenge 24 - Logic Testing Sandbox

Note that this sandbox is a draft and still being modified.
/***********************************************************************
 ---------------------------------------------- 
 LOGIC Testing Sandbox for TSQL Challenge 24
 Copyright © beyondrelational.com
 ---------------------------------------------- 

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

 2. Insert your query (solution for TSQL Challenge 24) 
    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. 

 Revision History:
 Rev 00 - 21 June 2010 - Lutz Müller 			- Initial Release
***********************************************************************/
/*
	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('TC24_Schedules','U') IS NOT NULL BEGIN
		DROP TABLE TC24_Schedules
	END
	;

	CREATE TABLE TC24_Schedules (
		SchID INT IDENTITY PRIMARY KEY,
		EmpName VARCHAR(10),
		Activity VARCHAR(15),
		StartTime SMALLDATETIME,
		Duration VARCHAR(5)
	)
	;

	SET IDENTITY_INSERT TC24_Schedules ON
	INSERT INTO TC24_Schedules (SchID,EmpName,Activity,StartTime,Duration) 
	SELECT 100, 'Tricky','24hr meeting', '20100201 00:00','23:59' UNION ALL
	SELECT 102, 'Tricky1','triple block1', '20100201 08:50','00:25' UNION ALL
	SELECT 103, 'Tricky1','triple block2', '20100201 09:20','00:05' UNION ALL
	SELECT 104, 'Tricky1','triple block3', '20100201 09:25','00:10' UNION ALL
	SELECT 99, 'Tricky1','one_day_off', '20100131 10:00','00:20' UNION ALL
	SELECT 98, 'Tricky1','one_year_off', '20090201 10:30','00:50' UNION ALL
	SELECT 105, 'Tricky1','at_the_hr_cont1', '20100201 12:30','00:15' UNION ALL
	SELECT 106, 'Tricky1','at_the_hr_cont2', '20100201 12:45','00:25' UNION ALL
	SELECT 107, 'Tricky1','bef_the_hr_end1', '20100201 13:25','00:15' UNION ALL
	SELECT 108, 'Tricky1','bef_the_hr_end2', '20100201 13:40','00:20' UNION ALL
	SELECT 109, 'Tricky','orphane_sched', '20100201 13:40','00:20' UNION ALL
	SELECT 110, 'Tricky_a','same_act_dif_em', '20100201 15:00','00:30' UNION ALL
	SELECT 111, 'Tricky_b','same_act_dif_em', '20100201 15:00','00:30' UNION ALL
	SELECT 112, 'Tricky1','FAQ12_test1', '20100201 23:30','00:20' UNION ALL
	SELECT 113, 'Tricky2','FAQ12_test2', '20100201 23:30','00:10' 
	SET IDENTITY_INSERT TC24_Schedules OFF

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

	CREATE TABLE TC24_ActivityLog (
		LogID INT IDENTITY PRIMARY KEY,
		EmpName VARCHAR(10),
		Activity VARCHAR(15),
		StartTime SMALLDATETIME,
		Duration VARCHAR(5)
	)
	;

	 SET IDENTITY_INSERT TC24_ActivityLog ON
	 INSERT INTO TC24_ActivityLog (LogID,EmpName,Activity,StartTime,Duration) 
	 SELECT 1000, 'Tricky','24hr meeting', '20100201 00:00','00:30' UNION ALL
	 SELECT 1001, 'Tricky','24hr meeting', '20100201 00:30','00:30' UNION ALL
	 SELECT 1002, 'Tricky','24hr meeting', '20100201 01:00','00:30' UNION ALL
	 SELECT 1003, 'Tricky','24hr meeting', '20100201 01:30','00:30' UNION ALL
	 SELECT 1004, 'Tricky','24hr meeting', '20100201 02:00','00:30' UNION ALL
	 SELECT 1005, 'Tricky','24hr meeting', '20100201 02:30','00:30' UNION ALL
	 SELECT 1006, 'Tricky','24hr meeting', '20100201 03:00','00:30' UNION ALL
	 SELECT 1007, 'Tricky','24hr meeting', '20100201 03:30','00:30' UNION ALL
	 SELECT 1008, 'Tricky','24hr meeting', '20100201 04:00','00:30' UNION ALL
	 SELECT 1009, 'Tricky','24hr meeting', '20100201 04:30','00:30' UNION ALL
	 SELECT 1010, 'Tricky','24hr meeting', '20100201 05:00','00:30' UNION ALL
	 SELECT 1011, 'Tricky','24hr meeting', '20100201 05:30','00:30' UNION ALL
	 SELECT 1012, 'Tricky','24hr meeting', '20100201 06:00','00:30' UNION ALL
	 SELECT 1013, 'Tricky','24hr meeting', '20100201 06:30','00:30' UNION ALL
	 SELECT 1014, 'Tricky','24hr meeting', '20100201 07:00','00:30' UNION ALL
	 SELECT 1015, 'Tricky','24hr meeting', '20100201 07:30','00:30' UNION ALL
	 SELECT 1016, 'Tricky','24hr meeting', '20100201 08:00','00:30' UNION ALL
	 SELECT 1017, 'Tricky','24hr meeting', '20100201 08:30','00:30' UNION ALL
	 SELECT 1018, 'Tricky','24hr meeting', '20100201 09:00','00:30' UNION ALL
	 SELECT 1019, 'Tricky','24hr meeting', '20100201 09:30','00:30' UNION ALL
	 SELECT 1020, 'Tricky','24hr meeting', '20100201 10:00','00:30' UNION ALL
	 SELECT 1021, 'Tricky','24hr meeting', '20100201 10:30','00:30' UNION ALL
	 SELECT 1022, 'Tricky','24hr meeting', '20100201 11:00','00:30' UNION ALL
	 SELECT 1023, 'Tricky','24hr meeting', '20100201 11:30','00:30' UNION ALL
	 SELECT 1024, 'Tricky','24hr meeting', '20100201 12:00','00:30' UNION ALL
	 SELECT 1025, 'Tricky','24hr meeting', '20100201 12:30','00:30' UNION ALL
	 SELECT 1026, 'Tricky','24hr meeting', '20100201 13:00','00:30' UNION ALL
	 SELECT 1027, 'Tricky','24hr meeting', '20100201 13:30','00:30' UNION ALL
	 SELECT 1028, 'Tricky','24hr meeting', '20100201 14:00','00:30' UNION ALL
	 SELECT 1029, 'Tricky','24hr meeting', '20100201 14:30','00:30' UNION ALL
	 SELECT 1030, 'Tricky','24hr meeting', '20100201 15:00','00:30' UNION ALL
	 SELECT 1031, 'Tricky','24hr meeting', '20100201 15:30','00:30' UNION ALL
	 SELECT 1032, 'Tricky','24hr meeting', '20100201 16:00','00:30' UNION ALL
	 SELECT 1033, 'Tricky','24hr meeting', '20100201 16:30','00:30' UNION ALL
	 SELECT 1034, 'Tricky','24hr meeting', '20100201 17:00','00:30' UNION ALL
	 SELECT 1035, 'Tricky','24hr meeting', '20100201 17:30','00:30' UNION ALL
	 SELECT 1036, 'Tricky','24hr meeting', '20100201 18:00','00:30' UNION ALL
	 SELECT 1037, 'Tricky','24hr meeting', '20100201 18:30','00:30' UNION ALL
	 SELECT 1038, 'Tricky','24hr meeting', '20100201 19:00','00:30' UNION ALL
	 SELECT 1039, 'Tricky','24hr meeting', '20100201 19:30','00:30' UNION ALL
	 SELECT 1040, 'Tricky','24hr meeting', '20100201 20:00','00:30' UNION ALL
	 SELECT 1041, 'Tricky','24hr meeting', '20100201 20:30','00:30' UNION ALL
	 SELECT 1042, 'Tricky','24hr meeting', '20100201 21:00','00:30' UNION ALL
	 SELECT 1043, 'Tricky','24hr meeting', '20100201 21:30','00:30' UNION ALL
	 SELECT 1044, 'Tricky','24hr meeting', '20100201 22:00','00:30' UNION ALL
	 SELECT 1045, 'Tricky','24hr meeting', '20100201 22:30','00:30' UNION ALL
	 SELECT 1046, 'Tricky','24hr meeting', '20100201 23:00','00:30' UNION ALL
	 SELECT 1047, 'Tricky','24hr meeting', '20100201 23:30','00:29' UNION ALL
	 SELECT 1051, 'Tricky1','triple block1','20100201 08:30','00:10' UNION ALL
	 SELECT 1052, 'Tricky1','triple block1','20100201 09:00','00:20' UNION ALL
	 SELECT 1053, 'Tricky1','triple block2','20100201 09:00','00:05' UNION ALL
	 SELECT 1054, 'Tricky1','triple block3','20100201 09:00','00:05' UNION ALL
	 SELECT 1055, 'Tricky1','triple block3','20100201 09:30','00:05' UNION ALL
	 SELECT 1056, 'Tricky1','one_day_off','20100201 09:30','00:01' UNION ALL
	 SELECT 1057, 'Tricky1','one_day_off','20100201 10:00','00:19' UNION ALL
	 SELECT 1058, 'Tricky1','one_year_off','20100201 10:00','00:01' UNION ALL
	 SELECT 1059, 'Tricky1','one_year_off','20100201 10:30','00:20' UNION ALL
	 SELECT 1060, 'Tricky1','at_the_hr_cont1', '20100201 12:30','00:15' UNION ALL
	 SELECT 1061, 'Tricky1','at_the_hr_cont2', '20100201 12:30','00:15' UNION ALL
	 SELECT 1062, 'Tricky1','at_the_hr_cont2', '20100201 13:00','00:10' UNION ALL
	 SELECT 1063, 'Tricky1','bef_the_hr_end1', '20100201 13:00','00:05' UNION ALL
	 SELECT 1064, 'Tricky1','bef_the_hr_end1', '20100201 13:30','00:10' UNION ALL
	 SELECT 1065, 'Tricky1','bef_the_hr_end2', '20100201 13:30','00:20' UNION ALL
	 SELECT 1066, 'Tricky1','orphaned_act', '20100201 14:00','00:10' UNION ALL
	 SELECT 1067, 'Tricky_a','same_act_dif_em', '20100201 15:00','00:30' UNION ALL
	 SELECT 1068, 'Tricky_b','same_act_dif_em', '20100201 15:00','00:30' UNION ALL
	 SELECT 1069, 'Tricky1','FAQ12_test1', '20100201 23:30','00:20' UNION ALL
	 SELECT 1070, 'Tricky2','FAQ12_test2', '20100201 23:30','00:10'
	 SET IDENTITY_INSERT TC24_ActivityLog OFF
	;
	/* Insert your query below */

	/* Insert your query above */
END
GO

SET NOCOUNT ON
/*
	Start the testing phase
*/

DECLARE @x TABLE(
	AutoID INT IDENTITY,
	Date DATETIME,
	EmpName VARCHAR(50),
	Activity VARCHAR(50),
	SchSt VARCHAR(10),
	SchDur VARCHAR(10),
	ActSt VARCHAR(10),
	ActDur VARCHAR(10)
)

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

DECLARE @z TABLE(
	AutoID INT IDENTITY,
	Date DATETIME,
	EmpName VARCHAR(50),
	Activity VARCHAR(50),
	SchSt VARCHAR(10),
	SchDur VARCHAR(10),
	ActSt VARCHAR(10),
	ActDur VARCHAR(10)
)

INSERT INTO @z(Date, EmpName, Activity, SchSt, SchDur, ActSt, ActDur)
SELECT '2010-02-01','Tricky','24hr meeting','00:00','23:59','00:00','23:59'
UNION ALL
SELECT '2010-02-01','Tricky_a','same_act_dif_em','15:00','00:30','15:00','00:30'
UNION ALL
SELECT '2010-02-01','Tricky_b','same_act_dif_em','15:00','00:30','15:00','00:30'
UNION ALL
SELECT '2010-02-01','Tricky1','triple block1','08:50','00:25','08:50','00:30'
UNION ALL
SELECT '2010-02-01','Tricky1','triple block2','09:20','00:05','09:20','00:05'
UNION ALL
SELECT '2010-02-01','Tricky1','triple block3','09:25','00:10','09:25','00:10'
UNION ALL
SELECT '2010-02-01','Tricky1','at_the_hr_cont1','12:30','00:15','12:30','00:15'
UNION ALL
SELECT '2010-02-01','Tricky1','at_the_hr_cont2','12:45','00:25','12:45','00:25'
UNION ALL
SELECT '2010-02-01','Tricky1','bef_the_hr_end1','13:25','00:15','13:25','00:15'
UNION ALL
SELECT '2010-02-01','Tricky1','bef_the_hr_end2','13:40','00:20','13:40','00:20'
UNION ALL
SELECT '2010-02-01','Tricky1','FAQ12_test1','23:30','00:20','23:30','00:20'
UNION ALL
SELECT '2010-02-01','Tricky2','FAQ12_test2','23:30','00:10','23:30','00:10'


-- 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
Database Challenges

Contribute

Challenge Categories

Navigation

User login

TSQL Challenge 24 - Logic Testing Sandbox

Subscribe

Most Popular

Today's:

All time:

Last viewed:
