January 12, 2011 at 7:45 am
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)
January 12, 2011 at 9:46 am
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
January 12, 2011 at 10:35 am
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)
January 13, 2011 at 3:10 am
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