Home

TSQL Challenge 16 - Logic Testing

Tue, 02/09/2010 - 14:14 — jacob

Here is the data we used for the logic testing of TSQL Challenge 16. 

/*
PersonID Surname FirstName Description StartDate  EndDate   
-------- ------- --------- ----------- ---------- ----------
18       Smith   John      Swimming    2006-01-01 NULL
18       Smith   John      Golf        2006-01-02 2010-01-01
18       Smith   John      Golf        2010-02-15 2010-03-30
18       Smith   John      Poker Club  2008-01-01 2008-02-02
18       Smith   John      Poker Club  2009-01-01 NULL
18       Smith   John      Library     2009-01-05 2009-01-18
18       Smith   John      Library     2009-01-25 2009-01-28
18       Smith   John      Gym         2009-01-10 2009-01-28
26       Adams   Jane      Pilates     2009-01-03 2009-02-16
26       Adams   Jane      Golf        2008-12-17 2009-02-16
26       Adams   Jane      Swimming    2009-01-05 2009-05-12
26       Adams   Jane      Swimming    2009-05-15 2010-01-07
26       Adams   Jane      Gym         2010-01-01 NULL
*/

Here is the expected results based on the above sample data. 

/*
PersonID Surname FirstName Description                          StartDate  EndDate   
-------- ------- --------- ------------------------------------ ---------- ----------
26       Adams   Jane      Golf                                 2008-12-17 2009-01-02
26       Adams   Jane      Golf/Pilates                         2009-01-03 2009-01-04
26       Adams   Jane      Golf/Pilates/Swimming                2009-01-05 2009-02-16
26       Adams   Jane      Swimming                             2009-02-17 2009-05-12
26       Adams   Jane      Swimming                             2009-05-15 2009-12-31
26       Adams   Jane      Swimming/Gym                         2010-01-01 2010-01-07
26       Adams   Jane      Gym                                  2010-01-08 NULL
18       Smith   John      Swimming                             2006-01-01 2006-01-01
18       Smith   John      Swimming/Golf                        2006-01-02 2007-12-31
18       Smith   John      Swimming/Golf/Poker Club             2008-01-01 2008-02-02
18       Smith   John      Swimming/Golf                        2008-02-03 2008-12-31
18       Smith   John      Swimming/Golf/Poker Club             2009-01-01 2009-01-04
18       Smith   John      Swimming/Golf/Poker Club/Library     2009-01-05 2009-01-09
18       Smith   John      Swimming/Golf/Poker Club/Library/Gym 2009-01-10 2009-01-18
18       Smith   John      Swimming/Golf/Poker Club/Gym         2009-01-19 2009-01-24
18       Smith   John      Swimming/Golf/Poker Club/Gym/Library 2009-01-25 2009-01-28
18       Smith   John      Swimming/Golf/Poker Club             2009-01-29 2010-01-01
18       Smith   John      Swimming/Poker Club                  2010-01-02 2010-02-14
18       Smith   John      Swimming/Poker Club/Golf             2010-02-15 2010-03-30
18       Smith   John      Swimming/Poker Club                  2010-03-31 NULL
*/

Note: In both the examples given below, we have 'shortened' the column titles to save space.

Here is the script to generate the sample data. 

SET DATEFORMAT dmy
DECLARE @Membership TABLE ( 
	PersonID	int, 
	Surname		nvarchar(16), 
	FirstName	nvarchar(16), 
	Description nvarchar(16), 
	StartDate	datetime, 
	EndDate		datetime)
	
INSERT INTO @Membership VALUES (18, 'Smith', 'John',
 'Swimming', '01/01/2006', NULL)
INSERT INTO @Membership VALUES (18, 'Smith', 'John',
 'Golf', '02/01/2006', '01/01/2010')	
INSERT INTO @Membership VALUES (18, 'Smith', 'John',
 'Golf', '15/02/2010', '30/03/2010')	
INSERT INTO @Membership VALUES (18, 'Smith', 'John',
 'Poker Club', '01/01/2008', '02/02/2008')
INSERT INTO @Membership VALUES (18, 'Smith', 'John',
 'Poker Club', '01/01/2009', NULL)
INSERT INTO @Membership VALUES (18, 'Smith', 'John', 
'Library', '05/01/2009', '18/01/2009')
INSERT INTO @Membership VALUES (18, 'Smith', 'John', 
'Library', '25/01/2009', '28/01/2009')
INSERT INTO @Membership VALUES (18, 'Smith', 'John', 
'Gym', '10/01/2009', '28/01/2009')
INSERT INTO @Membership VALUES (26, 'Adams', 'Jane', 
'Pilates', '03/01/2009', '16/02/2009')
INSERT INTO @Membership VALUES (26, 'Adams', 'Jane', 
'Golf', '17/12/2008', '16/02/2009')
INSERT INTO @Membership VALUES (26, 'Adams', 'Jane', 
'Swimming', '05/01/2009', '12/05/2009')
INSERT INTO @Membership VALUES (26, 'Adams', 'Jane', 
'Swimming', '15/05/2009', '07/01/2010')
INSERT INTO @Membership VALUES (26, 'Adams', 'Jane', 
'Gym', '01/01/2010', NULL)

SELECT * FROM @Membership