ISBNNumber_Unfinished

-- ISBN number numerically valid?
--  The ISBN-10 check digit, which is the last digit of the ten-digit ISBN, must range from 0 - 10 (X in place of 10).
--  And must be such that the sum of all the 10 digits, each multiplied by its (integer) weightm descending from 10 to 1, is a multiple of 11.

/* Example 1:
    0-306-40615-2
    = (0 * 10) + (3 * 9) + (0 * 8) + (6 * 7) + (4 * 6) + (0 * 5) + (6 * 4) + (1 * 3) + (5 * 2) + (2 * 1)
    = 0 + 27 + 0 + 42 + 24 + 0 + 24 + 3 + 10 + 2
    = 132
    = 11 * 12
*/

-- UNFINISHED:
--  Need to account for ending in X
--  Need to account for non numeric characters

-- Create and populate a test table.
CREATE TABLE #ISBNTest
(
    ISBNTestID INT IDENTITY(1, 1) CONSTRAINT [PK_ISBNTest_ISBNTestID] PRIMARY KEY CLUSTERED,
    ISBNNumberInsert VARCHAR(13) NOT NULL,
    ISBNNumber  AS (REPLACE(ISBNNumberInsert, '-', '')) -- We can remove hyphens straight away this way (computed column)
);
GO

INSERT INTO #ISBNTest (ISBNNumberInsert) VALUES ('0-306-40615-2');

/* -- If we don't want to use computed column then can do the following
CREATE TABLE #ISBNTest
(
    ISBNTestID int identity(1, 1) CONSTRAINT [PK_ISBNTest_ISBNTestID] PRIMARY KEY CLUSTERED,
    ISBNNumberInsert varchar(13) NOT NULL,
    ISBNNumber  varchar(13) NOT NULL
);
GO

INSERT INTO #ISBNTest (ISBNNumberInsert) VALUES ('0-306-40615-2');

UPDATE #ISBNTest SET ISBNNumber = REPLACE(ISBNNumberInsert, '-'. '');

*/

SELECT  *
FROM    #ISBNTest;

-- Tally table method with SUM() over.
WITH    L0 AS(SELECT 1 AS c UNION ALL SELECT 1),
        L1 AS(SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
        L2 AS(SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
        TallyTable (n) AS
        (
        SELECT  TOP(10) --  We can hard code this as we know ISBN is only 10 chars long
                ROW_NUMBER() OVER (ORDER BY (SELECT NULL) ASC)
        FROM    L2
        )
SELECT  RetVal =
                CASE
                    WHEN SUM(S.SumOfWeights) OVER (PARTITION BY S.ISBNTestID) % 11 = 0 THEN 'True'
                    ELSE 'False'
                END,
        SumOverOfWeights = SUM(S.SumOfWeights) OVER (PARTITION BY S.ISBNTestID),
        S.*
FROM    (
SELECT  I.ISBNTestID,
        I.ISBNNumberInsert,
        I.ISBNNumber,
        X.EachNumber,
        TT.n,
        SumOfWeights = X.EachNumber * TT.n
FROM    #ISBNTest AS [I]
CROSS
APPLY   (SELECT TOP (10) TallyTable.n FROM TallyTable ORDER BY TallyTable.n DESC) AS [TT]
CROSS
APPLY   (SELECT SUBSTRING(I.ISBNNumber, TT.n, 1)) AS [X] (EachNumber)
        ) AS [S];
GO

-- Clean up
DROP TABLE #ISBNTest;
GO