TSQL Challenge 13 – Solution by Isham Hamin
Better and Stronger Evaluation Process
One of the challenges we, The TSQL Challenge Team has, is to come up with a robust evaluation process. As we dig deeper and deeper into it, we realize that it is not as easy as we thought. We need to come up with better and stronger evaluation process to make sure that each solution is tested from different angles.
We could not do an extensive set of tests on the solutions of the previous challenges. All the solutions were tested using the sample data provided. From the next challenge onwards, we are trying to come up with a three level evaluation process that involves the following.
- Basic Testing – This test is done on the sample data provided with the challenge. This test will filter out solutions that do not produce the expected result.
- Logic Testing – Solutions that pass the Basic Testing will be moved to Logic Testing. During this testing phase, the solutions will be tested against a tricky set of data that validates the accuracy of the solution under exceptional circumstances.
- Load Testing – Solutions that pass Logic testing will be moved to the final testing phase. During this testing phase, the solutions will be tested against a large volume of data and performance statistics will be collected and analyzed.
We are trying hard to make sure that we will be able to provide you with the test data for all the three levels of testing, at the time of publishing a challenge. However, generating this type of data (especially for Logic Testing) needs a lot of efforts and I would like to invite volunteers from the SQL Server community to come forward and help the evaluation team. If you would like to lend us a hand, please let us know.
Solution by Isham Hamin
Isham’s solution (TSQL Challenge #13) starts with a simple query that uses SQL Server Window Function, ROW_NUMBER, to generate a unique sequence number. If you are not familiar with the different Window Functions, this article might help you. He generates two sequence numbers, one that resets with each batch and the other that resets with each invoice.
SELECT
BatchNumber,
InvoiceNumber,
RunningNbr = ROW_NUMBER()
OVER (PARTITION BY BatchNumber ORDER BY InvID),
SelectedRow = ROW_NUMBER()
OVER (PARTITION BY BatchNumber,InvoiceNumber
ORDER BY InvID DESC)
FROM @t
The above query produces the following output.
BatchNumber InvoiceNumber RunningNbr SelectedRow
----------- ------------- ---------- -----------
..... removed for brevity ......................
10000001 20005 19 5
10000001 20005 20 4
10000001 20005 21 3
10000001 20005 22 2
10000001 20005 23 1<=
10000001 20006 24 <= 1<=
10000002 20007 1 3
10000002 20007 2 2
10000002 20007 3 1<=
10000002 20008 4 3
10000002 20008 5 2
10000002 20008 6 1<=
On top of this result, Isham wrote an outer query that tried to categorizes invoices into different sets.
SELECT BatchNumber,
InvoiceNumber,
SetNbr = FLOOR(RunningNbr/10)+1
FROM (
SELECT
BatchNumber,
InvoiceNumber,
RunningNbr = ROW_NUMBER()
OVER (PARTITION BY BatchNumber ORDER BY InvID),
SelectedRow = ROW_NUMBER()
OVER (PARTITION BY BatchNumber,InvoiceNumber
ORDER BY InvID DESC)
FROM @t
) t1
WHERE SelectedRow = 1
Here is the output of the above query.
BatchNumber InvoiceNumber SetNbr
----------- ------------- ------
10000001 20001 1
10000001 20002 1
10000001 20003 1
10000001 20004 2
10000001 20005 3
10000001 20006 3
10000002 20007 1
10000002 20008 1
And then, he put the previous query into a CTE and wrote the final version of the query, which produces the required output.
;WITH Inv_CTE AS
(
SELECT BatchNumber,
InvoiceNumber,
SetNbr = FLOOR(RunningNbr/10)+1
FROM (
SELECT
BatchNumber,
InvoiceNumber,
RunningNbr = ROW_NUMBER()
OVER (PARTITION BY BatchNumber ORDER BY InvID),
SelectedRow = ROW_NUMBER()
OVER (PARTITION BY BatchNumber,InvoiceNumber
ORDER BY InvID DESC)
FROM @t
) t1
WHERE SelectedRow = 1
)
SELECT t2.InvID,
t1.BatchNumber,
t1.InvoiceNumber,
t2.VisitDate,
t2.Amount,
t1.SetNbr
FROM Inv_CTE t1
INNER JOIN @t t2 ON t2.BatchNumber = t1.BatchNumber
AND t2.InvoiceNumber = t1.InvoiceNumber
The Final Solution
Here is the complete listing of Isham’s soution.
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
;WITH Inv_CTE AS
(
SELECT BatchNumber,
InvoiceNumber,
SetNbr = FLOOR(RunningNbr/10)+1
FROM (
SELECT
BatchNumber,
InvoiceNumber,
RunningNbr = ROW_NUMBER()
OVER (PARTITION BY BatchNumber ORDER BY InvID),
SelectedRow = ROW_NUMBER()
OVER (PARTITION BY BatchNumber,InvoiceNumber
ORDER BY InvID DESC)
FROM @t
) t1
WHERE SelectedRow = 1
)
SELECT t2.InvID,
t1.BatchNumber,
t1.InvoiceNumber,
t2.VisitDate,
t2.Amount,
t1.SetNbr
FROM Inv_CTE t1
INNER JOIN @t t2 ON t2.BatchNumber = t1.BatchNumber
AND t2.InvoiceNumber = t1.InvoiceNumber
/*
InvID BatchNumber InvoiceNumber VisitDate SetNbr
----- ----------- ------------- ---------- ------
1 10000001 20001 2009-01-01 1
2 10000001 20001 2009-01-02 1
3 10000001 20001 2009-01-03 1
4 10000001 20001 2009-01-04 1
5 10000001 20002 2009-01-01 1
6 10000001 20002 2009-01-02 1
7 10000001 20002 2009-01-03 1
8 10000001 20003 2009-01-01 1
9 10000001 20003 2009-01-02 1
10 10000001 20004 2009-01-01 2
11 10000001 20004 2009-01-02 2
12 10000001 20004 2009-01-03 2
13 10000001 20004 2009-01-04 2
14 10000001 20004 2009-01-05 2
15 10000001 20004 2009-01-06 2
16 10000001 20005 2009-01-01 3
17 10000001 20005 2009-01-02 3
18 10000001 20005 2009-01-13 3
19 10000001 20005 2009-01-14 3
20 10000001 20005 2009-01-15 3
21 10000001 20005 2009-01-06 3
22 10000001 20005 2009-01-07 3
23 10000001 20005 2009-01-08 3
24 10000001 20006 2009-01-01 3
25 10000002 20007 2009-01-01 1
26 10000002 20007 2009-01-02 1
27 10000002 20007 2009-01-03 1
28 10000002 20008 2009-01-01 1
29 10000002 20008 2009-01-02 1
30 10000002 20008 2009-01-03 1
*/
Please Join me to congratulate Isham Hamin!