VARCHAR Sort Issue using MAX Function

  • Ok... I am trying to create a script that will insert new "Users" into an existing list of Users by Division. The goal is to add the Users and create a new "PC Number" for the User(s) in a lookup table, "dbo.PCList".

    The problem is with trying to get the MAX "PCName" from the dbo.PCList table. For the "Office" division, the last PC \Name was "O21" but a MAX(PCName) for that division will produce a "O9" instead, which works as intended for the MAX function but does not work with what the code is trying to do, i.e., the next PC should be "O22", not "O91"....

    Here is an example of the code:

    USE TempDB

    GO

    ----- CREATE Master Lookup TABLE of existing Users

    CREATE TABLE dbo.PCList (RowNo INT, Division varchar(50), UserName varchar(50), PCName varchar(4), PRIMARY KEY (RowNo))

    INSERT INTO dbo.PCList

    SELECT '1','Office','Carl','O1' UNION

    SELECT '2','Engineering','Bill','PC22' UNION

    SELECT '4','Production','Doris','1543' UNION

    SELECT '5','Production','Sam','1544' UNION

    SELECT '6','Office','Frank','O2' UNION

    SELECT '7','Facilities','Oscar','1' UNION

    SELECT '8','Facilities','Bob','2' UNION

    SELECT '9','Office','Joe','O3' UNION

    SELECT '10','Office','Lisa','O4' UNION

    SELECT '11','Engineering','Lisa','PC26' UNION

    SELECT '12','Engineering','Don','PC27' UNION

    SELECT '13','Maintenance','Dilbert','SS' UNION

    SELECT '14','Production','William','1545' UNION

    SELECT '15','Engineering','Wendy','PC23' UNION

    SELECT '16','Office','TJ','O12' UNION

    SELECT '17','Office','Bryan','O13' UNION

    SELECT '18','Office','Ann','O8' UNION

    SELECT '19','Office','Melissa','O9' UNION

    SELECT '20','Engineering','Samir','PC24' UNION

    SELECT '21','Engineering','Wei-Jen','PC25' UNION

    SELECT '22','Office','Dave','O10' UNION

    SELECT '23','Office','Ed','O5' UNION

    SELECT '24','Office','Edward','O6' UNION

    SELECT '25','Office','Trevor','O7' UNION

    SELECT '26','Office','Karl','O14' UNION

    SELECT '27','Office','Eric','O15' UNION

    SELECT '28','Office','Erica','O16' UNION

    SELECT '29','Office','Bill','O17' UNION

    SELECT '30','Office','William','O8' UNION

    SELECT '31','Office','Amanda','O19' UNION

    SELECT '32','Office','Nicole','O20' UNION

    SELECT '33','Office','Trevor','O21' UNION

    SELECT '34','Office','David','O11'

    ---- Get list of Last PC Name Used by Division

    ---- Issue: Office "Max" should be 021, NOT O9

    CREATE TABLE #MaxList (Division varchar(50), MaxPC varchar(4))

    INSERT INTO #MaxList

    SELECT Division, MAX(PCName)

    from dbo.PCList

    GROUP BY Division

    --SELECT * FROM #MaxList

    --- List of New Entries

    CREATE TABLE #NewPCs (RowNo INT IDENTITY(1,1), Division varchar(50), UserName varchar(50))

    INSERT INTO #NewPCs

    SELECT 'Office','Ted' UNION

    SELECT 'Engineering','Alice' UNION

    SELECT 'Engineering','Randy'

    ----- Lets try to add New Entries

    DECLARE @MaxRow int;

    SELECT @MaxRow = Max(RowNo) from PCList

    --INSERT INTO PCList

    SELECT @MaxRow +NewPC.RowNo

    ,NewPC.Division

    ,NewPC.UserName

    ,CASE WHEN ISNUMERIC(PC.MaxPC) = 1 THEN CAST(CAST(PC.MaxPC as INT)+ROW_NUMBER() OVER (PARTITION BY NewPC.Division ORDER BY NewPC.Division, NewPC.UserName) as varchar(4))

    WHEN LEN(PC.MaxPC) = 4 AND ISNUMERIC(RIGHT(PC.MaxPC,2)) = 1 THEN LEFT(PC.MaxPC,2)+CAST(CAST(RIGHT(PC.MaxPC,2) as INT)+ROW_NUMBER() OVER (PARTITION BY NewPC.Division ORDER BY NewPC.Division, NewPC.UserName) as varchar(2))

    WHEN LEN(PC.MaxPC) < 4 THEN PC.MaxPC+CAST(ROW_NUMBER() OVER (PARTITION BY NewPC.Division ORDER BY NewPC.Division, NewPC.UserName) as varchar(2))

    ELSE PC.MaxPC

    END as PCName

    FROM #NewPCs NewPC

    INNER JOIN #MaxList PC

    ON NewPC.Division = PC.Division

    --DROP TABLE dbo.PCList, #NewPCs, #MaxList

    Argue for your limitations, and sure enough they're yours (Richard Bach, Illusions)

  • Add computed columns:

    ALTER TABLE dbo.PCList

    ADD PCNamePreFix

    AS

    (

    CAST

    (

    CASE PATINDEX('%[0-9]%', PCName)

    WHEN 1 THEN ''

    WHEN 2 THEN LEFT(PCName, 1)

    WHEN 3 THEN LEFT(PCName, 2)

    ELSE LEFT(PCName, 3)

    END

    AS varchar(3)

    )

    ) --PERSISTED

    , PCNameNumber

    AS

    (

    CAST

    (

    CASE PATINDEX('%[0-9]%', PCName)

    WHEN 0 THEN 0

    WHEN 1 THEN PCName

    WHEN 2 THEN SUBSTRING(PCName, 2, 3)

    WHEN 3 THEN SUBSTRING(PCName, 3, 2)

    ELSE RIGHT(PCName, 1)

    END

    AS smallint

    )

    ) --PERSISTED

    GO

    -- Your test data has office O8 twice which would stop this from working.

    ALTER TABLE dbo.PCList

    ADD CONSTRAINT PCList_PCPrefixNumber UNIQUE (Division, PCNamePreFix, PCNameNumber)

    GO

    You code should now be something like:

    INSERT INTO dbo.PCList

    SELECT COALESCE(M.MaxRowNo, 0) + N.RowNo

    ,N.Division

    ,N.UserName

    ,COALESCE(P.PCNamePreFix, LEFT(N.Division, 1)) + CAST((COALESCE(P.PCNameNumber, 0) + N.RowNum) AS varchar(4))

    FROM

    (

    SELECT RowNo, Division, UserName

    ,ROW_NUMBER() OVER (PARTITION BY Division ORDER BY UserName) AS RowNum

    FROM #NewPCs N1

    ) N

    LEFT JOIN

    (

    SELECT Division, PCNamePreFix, PCNameNumber

    ,ROW_NUMBER() OVER (PARTITION BY Division ORDER BY PCNamePreFix DESC, PCNameNumber DESC) AS Priority

    FROM dbo.PCList WITH (UPDLOCK, SERIALIZABLE)

    ) P

    ON N.Division = P.Division

    AND P.Priority = 1

    CROSS JOIN

    (

    SELECT MAX(RowNo) AS MaxRowNo

    FROM dbo.PCList WITH (UPDLOCK, SERIALIZABLE)

    ) M

  • That is why I love this site... always other people with different perspectives and different ways to tackle a problem.

    Thanks Ken!

    Unfortunately, I cannot modify the dbo.PCList table but my solution was to take the results from that table, create a temp table with the computed columns and run the insert script(s). Works like a charm! 🙂 😀

    P.S. - Part of the challenge is there can be duplicates in the PCName field (not intentional) due to too many people making entries into the table before... thus the desire to automate the process.

    Argue for your limitations, and sure enough they're yours (Richard Bach, Illusions)

  • You may be best to do it all in one statement to avoid potential concurrency problems.

    Something like:

    ;WITH DivRows

    AS

    (

    SELECT RowNo, Division, UserName

    ,ROW_NUMBER() OVER (PARTITION BY Division ORDER BY UserName) AS DivRow

    FROM #NewPCs N1

    )

    , PCListCalc

    AS

    (

    SELECT Division

    ,CAST

    (

    CASE PATINDEX('%[0-9]%', PCName)

    WHEN 1 THEN ''

    WHEN 2 THEN LEFT(PCName, 1)

    WHEN 3 THEN LEFT(PCName, 2)

    ELSE LEFT(PCName, 3)

    END

    AS varchar(3)

    ) AS PCNamePreFix

    ,CAST

    (

    CASE PATINDEX('%[0-9]%', PCName)

    WHEN 0 THEN 0

    WHEN 1 THEN PCName

    WHEN 2 THEN SUBSTRING(PCName, 2, 3)

    WHEN 3 THEN SUBSTRING(PCName, 3, 2)

    ELSE RIGHT(PCName, 1)

    END

    AS smallint

    ) AS PCNameNumber

    FROM dbo.PCList WITH (UPDLOCK, SERIALIZABLE)

    )

    , PCListPriority

    AS

    (

    SELECT *

    ,ROW_NUMBER() OVER (PARTITION BY Division ORDER BY PCNamePreFix DESC, PCNameNumber DESC) AS Priority

    FROM PCListCalc

    )

    , MaxRowNo

    AS

    (

    SELECT MAX(RowNo) AS MaxRowNo

    FROM dbo.PCList WITH (UPDLOCK, SERIALIZABLE)

    )

    INSERT INTO dbo.PCList

    SELECT COALESCE(M.MaxRowNo, 0) + N.RowNo

    ,N.Division

    ,N.UserName

    ,COALESCE(P.PCNamePreFix, LEFT(N.Division, 1)) + CAST((COALESCE(P.PCNameNumber, 0) + N.DivRow) AS varchar(4))

    FROM DivRows N

    LEFT JOIN PCListPriority P

    ON N.Division = P.Division

    AND P.Priority = 1

    CROSS JOIN MaxRowNo M

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply