TSQL Challenge 13 - Logic Testing

Solutions that passed the Basic Testing are tested with a 'tricky' set of data, which we call "Logic Testing". Here is the tricky data set used for the evaluation of TSQL Challenge 13

Script to generate the Logic Testing Data

DECLARE @t TABLE(
    InvID INT IDENTITY,
    BatchNumber INT,
    InvoiceNumber INT,
    VisitDate DATETIME,
    Amount MONEY )

INSERT INTO @t(BatchNumber, InvoiceNumber, VisitDate, Amount)
SELECT 10000001,20001,'2009-01-01',50.00 UNION ALL
SELECT 10000001,20001,'2009-01-02',50.00 UNION ALL
SELECT 10000001,20001,'2009-01-03',50.00 UNION ALL
SELECT 10000001,20001,'2009-01-04',50.00 UNION ALL
SELECT 10000001,20002,'2009-01-01',50.00 UNION ALL
SELECT 10000001,20002,'2009-01-02',50.00 UNION ALL
SELECT 10000001,20002,'2009-01-03',50.00 UNION ALL
SELECT 10000001,20003,'2009-01-01',50.00 UNION ALL
SELECT 10000001,20003,'2009-01-02',50.00 UNION ALL
SELECT 10000001,20004,'2009-01-01',50.00 UNION ALL
SELECT 10000001,20004,'2009-01-02',50.00 UNION ALL
SELECT 10000001,20004,'2009-01-03',50.00 UNION ALL
SELECT 10000001,20004,'2009-01-04',50.00 UNION ALL
SELECT 10000001,20004,'2009-01-05',50.00 UNION ALL
SELECT 10000001,20004,'2009-01-06',50.00 UNION ALL
SELECT 10000001,20005,'2009-01-01',50.00 UNION ALL
SELECT 10000001,20005,'2009-01-02',50.00 UNION ALL
SELECT 10000001,20005,'2009-01-13',50.00 UNION ALL
SELECT 10000001,20005,'2009-01-14',50.00 UNION ALL
SELECT 10000001,20005,'2009-01-15',50.00 UNION ALL
SELECT 10000001,20005,'2009-01-06',50.00 UNION ALL
SELECT 10000001,20005,'2009-01-07',50.00 UNION ALL
SELECT 10000001,20005,'2009-01-08',50.00 UNION ALL
SELECT 10000001,20006,'2009-01-01',50.00 UNION ALL
SELECT 10000002,20007,'2009-01-01',50.00 UNION ALL
SELECT 10000002,20007,'2009-01-02',50.00 UNION ALL
SELECT 10000002,20007,'2009-01-03',50.00 UNION ALL
SELECT 10000002,20008,'2009-01-01',50.00 UNION ALL
SELECT 10000002,20008,'2009-01-02',50.00 UNION ALL
SELECT 10000002,20008,'2009-01-03',50.00;

INSERT INTO @t(BatchNumber, InvoiceNumber, VisitDate, Amount)
SELECT 90000001,30001,'2009-01-01',50.00 UNION ALL
SELECT 90000001,30001,'2009-01-02',50.00 UNION ALL
SELECT 90000001,30001,'2009-01-03',50.00 UNION ALL
SELECT 90000001,30001,'2009-01-04',50.00 UNION ALL
SELECT 90000001,30001,'2009-01-05',50.00 UNION ALL
SELECT 90000001,30001,'2009-01-06',50.00 UNION ALL
SELECT 90000001,30001,'2009-01-07',50.00 UNION ALL
SELECT 90000001,30001,'2009-01-08',50.00 UNION ALL
SELECT 90000001,30001,'2009-01-09',50.00 UNION ALL
SELECT 90000001,30001,'2009-01-10',50.00 UNION ALL

SELECT 90000001,30002,'2009-01-01',50.00 UNION ALL
SELECT 90000001,30002,'2009-01-02',50.00 UNION ALL
SELECT 90000001,30002,'2009-01-03',50.00 UNION ALL
SELECT 90000001,30002,'2009-01-04',50.00 UNION ALL
SELECT 90000001,30002,'2009-01-05',50.00 UNION ALL
SELECT 90000001,30002,'2009-01-06',50.00 UNION ALL

SELECT 90000001,30003,'2009-01-01',50.00 UNION ALL
SELECT 90000001,30003,'2009-01-02',50.00 UNION ALL
SELECT 90000001,30003,'2009-01-03',50.00 UNION ALL
SELECT 90000001,30003,'2009-01-04',50.00 UNION ALL
SELECT 90000001,30003,'2009-01-05',50.00 UNION ALL
SELECT 90000001,30003,'2009-01-06',50.00 UNION ALL

SELECT 90000001,30004,'2009-01-01',50.00 UNION ALL
SELECT 90000001,30004,'2009-01-02',50.00 UNION ALL
SELECT 90000001,30004,'2009-01-03',50.00 UNION ALL
SELECT 90000001,30004,'2009-01-04',50.00 UNION ALL
SELECT 90000001,30004,'2009-01-05',50.00 UNION ALL
SELECT 90000001,30004,'2009-01-06',50.00 UNION ALL

SELECT 90000001,30015,'2009-01-03',90.00 UNION ALL
SELECT 90000001,30015,'2009-01-02',90.00 UNION ALL
SELECT 90000001,30015,'2009-01-01',90.00 UNION ALL
SELECT 90000001,30006,'2009-01-01',50.00 UNION ALL
SELECT 90000001,30006,'2009-01-02',50.00 UNION ALL
SELECT 90000001,30006,'2009-01-03',50.00 UNION ALL
SELECT 90000001,30007,'2009-01-01',50.00 UNION ALL
SELECT 90000001,30007,'2009-01-02',50.00 UNION ALL
SELECT 90000001,30007,'2009-01-03',50.00 UNION ALL
SELECT 90000001,30007,'2009-01-04',50.00 UNION ALL

SELECT 90000001,30008,'2009-01-01',50.00 UNION ALL
SELECT 90000001,30008,'2009-01-02',50.00 UNION ALL
SELECT 90000001,30008,'2009-01-03',50.00 UNION ALL
SELECT 90000001,30008,'2009-01-04',50.00 UNION ALL
SELECT 90000001,30008,'2009-01-05',50.00 UNION ALL
SELECT 90000001,30009,'2009-01-01',50.00 UNION ALL
SELECT 90000001,30009,'2009-01-02',50.00 UNION ALL
SELECT 90000001,30009,'2009-01-03',50.00 UNION ALL
SELECT 90000001,30009,'2009-01-04',50.00 UNION ALL
SELECT 90000001,30009,'2009-01-05',50.00 UNION ALL

SELECT 90000001,30010,'2009-01-01',50.00 UNION ALL

SELECT 30000001,40001,'2009-01-01',50.00 UNION ALL
SELECT 30000001,40002,'2009-01-01',50.00 UNION ALL
SELECT 30000001,40003,'2009-01-01',50.00 UNION ALL
SELECT 30000001,40004,'2009-01-01',50.00 UNION ALL
SELECT 30000001,40005,'2009-01-01',50.00 UNION ALL
SELECT 30000001,40006,'2009-01-01',50.00 UNION ALL
SELECT 30000001,40007,'2009-01-01',50.00 UNION ALL
SELECT 30000001,40008,'2009-01-01',50.00 UNION ALL
SELECT 30000001,40009,'2009-01-01',50.00 UNION ALL
SELECT 30000001,40010,'2009-01-01',50.00 UNION ALL
SELECT 30000001,40011,'2009-01-01',50.00 UNION ALL
SELECT 30000001,40012,'2009-01-01',50.00 UNION ALL
SELECT 30000001,40013,'2009-01-01',50.00 UNION ALL
SELECT 30000001,40014,'2009-01-01',50.00 UNION ALL
SELECT 30000001,40015,'2009-01-01',50.00 UNION ALL
SELECT 30000001,40016,'2009-01-01',50.00 UNION ALL
SELECT 30000001,40017,'2009-01-01',50.00 UNION ALL
SELECT 30000001,40018,'2009-01-01',50.00 UNION ALL
SELECT 30000001,40019,'2009-01-01',50.00 UNION ALL
SELECT 30000001,40020,'2009-01-01',50.00 UNION ALL
SELECT 30000001,40021,'2009-01-01',50.00 ;

Expected Output

InvID BatchNumber InvoiceNumber VisitDate  Amount Set
----- ----------- ------------- ---------- ------ ---
1     10000001    20001         2009-01-01 50.00  1
2     10000001    20001         2009-01-02 50.00  1
3     10000001    20001         2009-01-03 50.00  1
4     10000001    20001         2009-01-04 50.00  1
5     10000001    20002         2009-01-01 50.00  1
6     10000001    20002         2009-01-02 50.00  1
7     10000001    20002         2009-01-03 50.00  1
8     10000001    20003         2009-01-01 50.00  1
9     10000001    20003         2009-01-02 50.00  1
10    10000001    20004         2009-01-01 50.00  2
11    10000001    20004         2009-01-02 50.00  2
12    10000001    20004         2009-01-03 50.00  2
13    10000001    20004         2009-01-04 50.00  2
14    10000001    20004         2009-01-05 50.00  2
15    10000001    20004         2009-01-06 50.00  2
16    10000001    20005         2009-01-01 50.00  3
17    10000001    20005         2009-01-02 50.00  3
18    10000001    20005         2009-01-13 50.00  3
19    10000001    20005         2009-01-14 50.00  3
20    10000001    20005         2009-01-15 50.00  3
21    10000001    20005         2009-01-06 50.00  3
22    10000001    20005         2009-01-07 50.00  3
23    10000001    20005         2009-01-08 50.00  3
24    10000001    20006         2009-01-01 50.00  3
25    10000002    20007         2009-01-01 50.00  1
26    10000002    20007         2009-01-02 50.00  1
27    10000002    20007         2009-01-03 50.00  1
28    10000002    20008         2009-01-01 50.00  1
29    10000002    20008         2009-01-02 50.00  1
30    10000002    20008         2009-01-03 50.00  1
31    90000001    30001         2009-01-01 50.00  1
32    90000001    30001         2009-01-02 50.00  1
33    90000001    30001         2009-01-03 50.00  1
34    90000001    30001         2009-01-04 50.00  1
35    90000001    30001         2009-01-05 50.00  1
36    90000001    30001         2009-01-06 50.00  1
37    90000001    30001         2009-01-07 50.00  1
38    90000001    30001         2009-01-08 50.00  1
39    90000001    30001         2009-01-09 50.00  1
40    90000001    30001         2009-01-10 50.00  1
41    90000001    30002         2009-01-01 50.00  2
42    90000001    30002         2009-01-02 50.00  2
43    90000001    30002         2009-01-03 50.00  2
44    90000001    30002         2009-01-04 50.00  2
45    90000001    30002         2009-01-05 50.00  2
46    90000001    30002         2009-01-06 50.00  2
47    90000001    30003         2009-01-01 50.00  3
48    90000001    30003         2009-01-02 50.00  3
49    90000001    30003         2009-01-03 50.00  3
50    90000001    30003         2009-01-04 50.00  3
51    90000001    30003         2009-01-05 50.00  3
52    90000001    30003         2009-01-06 50.00  3
53    90000001    30004         2009-01-01 50.00  4
54    90000001    30004         2009-01-02 50.00  4
55    90000001    30004         2009-01-03 50.00  4
56    90000001    30004         2009-01-04 50.00  4
57    90000001    30004         2009-01-05 50.00  4
58    90000001    30004         2009-01-06 50.00  4
59    90000001    30015         2009-01-03 90.00  6
60    90000001    30015         2009-01-02 90.00  6
61    90000001    30015         2009-01-01 90.00  6
62    90000001    30006         2009-01-01 50.00  4
63    90000001    30006         2009-01-02 50.00  4
64    90000001    30006         2009-01-03 50.00  4
65    90000001    30007         2009-01-01 50.00  5
66    90000001    30007         2009-01-02 50.00  5
67    90000001    30007         2009-01-03 50.00  5
68    90000001    30007         2009-01-04 50.00  5
69    90000001    30008         2009-01-01 50.00  5
70    90000001    30008         2009-01-02 50.00  5
71    90000001    30008         2009-01-03 50.00  5
72    90000001    30008         2009-01-04 50.00  5
73    90000001    30008         2009-01-05 50.00  5
74    90000001    30009         2009-01-01 50.00  6
75    90000001    30009         2009-01-02 50.00  6
76    90000001    30009         2009-01-03 50.00  6
77    90000001    30009         2009-01-04 50.00  6
78    90000001    30009         2009-01-05 50.00  6
79    90000001    30010         2009-01-01 50.00  6
80    30000001    40001         2009-01-01 50.00  1
81    30000001    40002         2009-01-01 50.00  1
82    30000001    40003         2009-01-01 50.00  1
83    30000001    40004         2009-01-01 50.00  1
84    30000001    40005         2009-01-01 50.00  1
85    30000001    40006         2009-01-01 50.00  1
86    30000001    40007         2009-01-01 50.00  1
87    30000001    40008         2009-01-01 50.00  1
88    30000001    40009         2009-01-01 50.00  1
89    30000001    40010         2009-01-01 50.00  1
90    30000001    40011         2009-01-01 50.00  2
91    30000001    40012         2009-01-01 50.00  2
92    30000001    40013         2009-01-01 50.00  2
93    30000001    40014         2009-01-01 50.00  2
94    30000001    40015         2009-01-01 50.00  2
95    30000001    40016         2009-01-01 50.00  2
96    30000001    40017         2009-01-01 50.00  2
97    30000001    40018         2009-01-01 50.00  2
98    30000001    40019         2009-01-01 50.00  2
99    30000001    40020         2009-01-01 50.00  2
100   30000001    40021         2009-01-01 50.00  3

Only a very small number of solutions passed the Logic Testing. Many solutions that worked well with the basic sample data, broke when tested with the tricky data. Here are the list of submissions that passed the Logic Testing.

Database Challenges

Contribute

Challenge Categories

Navigation

User login

TSQL Challenge 13 - Logic Testing

Subscribe

TOP 10 SQL Stars

Most Popular

Today's:

All time:

Last viewed: