Generating a number in a column.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Generating a number in a column.

Author

Topic

ben_53
Yak Posting Veteran

67 Posts

Posted - 2011-09-16 : 15:48:54

Here is the situation that I am currently struggling with.

I have two tables: Pro1 , Pro2 (a reference table)

Pro1: ID int, ProviderID1 varchar(25)

Pro2: ID int, ProviderID2 varchar(25)

Here is the thing. Both ProviderIDs (1,2) are always ABCxxxxxxxxxxxx (total 15) with ABC prefix. pro2 is pre-populated with other prefixes as well but we are not interested in that.

Now I need to populate pro1.ProviderID according to:

* check in pro2 maximum value where ProviderID2 like 'ABC%'
* now for providerID1 add 1 to that existing max providerid2
* if not found in pro2, then generate new providerid1 with prefix abc- "ABC000000000100"
* keep adding 1 for next providerid1.

Example:
lets say in pro2, MAX(providerID2) found is ABC000000000111
so in pro1, providerid1 it should generate ABC000000000112 and ABC000000000113 for next ID1 and so on.

If there is NO providerID2 in pro2 where providerid2 like 'ABC%' then generate ABC000000000100 , ABC000000000101 for next and so on.

Please note that providerid ALWAYS has to be ABC followed by 12 integers (total 15).

Thanks a Lot !!

ben_53
Yak Posting Veteran

67 Posts

Posted - 2011-09-16 : 15:49:58

Here is what I am using which is obviously wrong:

IF (SELECT count(providerid2) FROM pro2 WHERE providerid2 like 'ABC%')>0

BEGIN

IF object_id('tempdb..#te') is not null
DROP TABLE #te
CREATE TABLE #te (id int,pid int,pid2 varchar(15),providerid1 varchar(20))
INSERT INTO #te (id)
SELECT id FROM pro1

DECLARE @oldID int
SET @oldID = (SELECT right (MAX(providerID2),12) FROM pro2 WHERE providerid2 like 'ABC%')
UPDATE #te
SET pID = new
FROM ( SELECT pid,new =
ROW_NUMBER() OVER(PARTITION BY ID ORDER BY ID) + @oldID end
FROM #te) #te

DECLARE @pid TABLE (pid int)
INSERT INTO @pid (pid) SELECT pid FROM #te

UPDATE #te
SET pid2 = (SELECT stuff(replicate('0',12),12 - len(pid) +1,12,pid))FROM #te
DECLARE @pid2 TABLE (pid2 int)
INSERT INTO @pid2 (pid2) SELECT pid2 FROM #te
UPDATE #te
SET providerID1 = 'ABC' + CONVERT(VARCHAR(15), pid2, 15) FROM #te

UPDATE pro1
SET providerID1 = #te.providerid1
FROM pro1
INNER JOIN #te on #te.id = pro1.ID

END

ELSE

IF object_id('tempdb..#tempr') is not null
DROP TABLE #tempr
CREATE TABLE #tempr (id int,pid int,pid2 varchar(15), providerID1 varchar(15))
INSERT INTO #tempr (id)
SELECT id FROM pro1

UPDATE #tempr
SET pID = new
FROM ( SELECT pid,new =
ROW_NUMBER() OVER(PARTITION BY ID ORDER BY ID) + 99 end
FROM #tempr) #tempr

DECLARE @pi TABLE (pid int)
INSERT INTO @pi (pid) SELECT pid FROM #tempr
UPDATE #tempr
SET pid2 = (SELECT stuff(replicate('0',12),12 - len(pid) +1,12,pid)) FROM #tempr

DECLARE @pi2 TABLE (pid2 int)
INSERT INTO @pi2 (pid2) SELECT pid2 FROM #tempr
UPDATE #tempr
SET providerID1 = 'ABC' + CONVERT(VARCHAR(15), pid2, 15) FROM #tempr

UPDATE pro1
SET providerID1 = #tempr.providerid1
FROM pro1
INNER JOIN #tempr on #tempr.id = pro1.ID

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2011-09-16 : 16:05:37

Hello,

Does the following query help to stimulate some thought towards another direction?

SELECT providerID2, 'ABC' + RIGHT('0000000000000' + CAST(SUBSTRING(providerID2,4,LEN(providerID2)) + 1 AS VARCHAR(12)),12) AS sequence 
FROM
(
	SELECT MAX(providerID2) providerID2
	FROM Pro2 
	WHERE LEFT(providerID2,3) = 'ABC'
) m

ben_53
Yak Posting Veteran

67 Posts

Posted - 2011-09-16 : 16:13:05

@ehorn: It is not following the above mentioned bulleted points.

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2011-09-16 : 16:20:25

quote:
Originally posted by ben_53

@ehorn: It is not following the above mentioned bulleted points.

I see. Sorry I could not be more help. I wish you well in finding a solution which fits your needs.

ben_53
Yak Posting Veteran

67 Posts

Posted - 2011-09-16 : 16:33:20

I guess the solution that I posted is 95% correct. I will work on it, thanks :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-17 : 00:43:48

[code]INSERT INTO Provider1
SELECT 'ABC'+RIGHT('000000000000'+CAST(COALESCE(MaxID2,100) AS varchar(10)),12)
FROM
(
SELECT MAX(REPLACE(ProviderID2,'ABC','')*1) AS MaxID2
FROM Provider2
WHERE ProviderID2 LIKE 'ABC%'
)t
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources