auto increment alphanumeric value from 2 tables

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

SQL Server 2000 Forums

Transact-SQL (2000)

auto increment alphanumeric value from 2 tables

Author

Topic

challa_praveena
Starting Member

7 Posts

Posted - 2005-06-16 : 22:22:15

HI guys,

this is very urgent -any help is gr8tly appreciated.

my table structures
--------------------
Hierarchy
---------
VENDOR table
---VENDOR_OFFICE table
---VENDOR_OFFICE_SERVICE table
---VENDOR_OFFICE_SERVICE_SITE table

vendor table
------------
vendor_id assist_prov_loc_code
-------------------------------
10023 01
10034 01
23333 01

the above scenario will always be the same any vendor id will have assist_prov_loc_code as 01 period.

vendor_office table
-------------------
vendor_office_ID vendor_id assist_prov_loc_code
---------------------------------------------------
100 10023 02
101 10023 A1
102 23333 C1

vendor_office_service table
----------------------------
vendor_office_service_ID vendor_office_id
--------------------------------------------
300 100
301 100
302 102

vendor_office_service_site table
---------------------------------
vendor_office_site_ID vend_off_service_id assist_prov_loc_code
------------------------------------------------------------------
10 300 03
11 300 A2
12 302 C2

autoincrementing the alphanumeric value for vendor_office table
and vendor_office_site table has to start from 02-99 and A1-Z9
based on the max(assist_prov_loc_code) of both the tables. becuase they have the same field called assist_prov_loc_code into which the autoincrement has to be done.

we have to pick the max(assis_prov_loc_code) from vendor_office and vendor_office_site tables based on VENDOR_ID, VENDOR_OFFICE_ID and
VENDOR_OFFICE_SERVICE_SITE_ID and find the next autoincrement alphanumeric number or numeric number if it hasn't reached 99.

i have a sample code that does but I need to put it into a loop and make it more dynamic. i need to store numbers 02-99 and A-Z into an array and loop around and increment these

CASE
WHEN (MAX(vo.assists_provider_location_code) = NULL OR MAX(vo.assists_provider_location_code) = '') AND
(MAX(voss.assists_provider_location_code) = NULL OR MAX(voss.assists_provider_location_code) = '') THEN
'02'
WHEN (MAX(vo.assists_provider_location_code) <> '' AND MAX(vo.assists_provider_location_code) <> '99') AND
(MAX(voss.assists_provider_location_code) <> '' AND MAX(voss.assists_provider_location_code) <> '99') THEN
'0' + CONVERT(CHAR(2),MAX(vo.assists_provider_location_code) + 1)
WHEN MAX(vo.assists_provider_location_code) = '99' OR MAX(voss.assists_provider_location_code) = '99' THEN
'A1'
WHEN CHAR(ASCII(LEFT(MAX(vo.assists_provider_location_code),1))) + CHAR(ASCII(RIGHT(MAX(vo.assists_provider_location_code),1))) >= 'A1' and CHAR(ASCII(LEFT(MAX(vo.assists_provider_location_code),1))) + CHAR(ASCII(RIGHT(MAX(vo.assists_provider_location_code),1))) <= 'A9' AND
CHAR(ASCII(LEFT(MAX(voss.assists_provider_location_code),1))) + CHAR(ASCII(RIGHT(MAX(voss.assists_provider_location_code),1))) >= 'A1' and CHAR(ASCII(LEFT(MAX(vo.assists_provider_location_code),1))) + CHAR(ASCII(RIGHT(MAX(vo.assists_provider_location_code),1))) <= 'A9' THEN
CASE
WHEN CHAR(ASCII(LEFT(MAX(vo.assists_provider_location_code),1))) + CHAR(ASCII(RIGHT(MAX(vo.assists_provider_location_code),1))) = 'A9' OR CHAR(ASCII(LEFT(MAX(vo.assists_provider_location_code),1))) + CHAR(ASCII(RIGHT(MAX(voss.assists_provider_location_code),1))) = 'A9' THEN
CASE
WHEN CHAR(ASCII(LEFT(MAX(vo.assists_provider_location_code),1))) + CHAR(ASCII(RIGHT(MAX(vo.assists_provider_location_code),1))) = 'A9' THEN
CHAR(ASCII(left(MAX(vo.assists_provider_location_code),1)) + 1) + '1'
WHEN CHAR(ASCII(LEFT(MAX(voss.assists_provider_location_code),1))) + CHAR(ASCII(RIGHT(MAX(vo.assists_provider_location_code),1))) = 'A9' THEN
CHAR(ASCII(left(MAX(voss.assists_provider_location_code),1)) + 1) + '1'
END
ELSE
CASE
WHEN CHAR(ASCII(LEFT(MAX(vo.assists_provider_location_code),1))) + CHAR(ASCII(RIGHT(MAX(vo.assists_provider_location_code),1))) = 'A9' THEN
'A' + CONVERT(char(2),CHAR(ASCII(right(MAX(vo.assists_provider_location_code),1))) + 1)
WHEN CHAR(ASCII(LEFT(MAX(voss.assists_provider_location_code),1))) + CHAR(ASCII(RIGHT(MAX(vo.assists_provider_location_code),1))) = 'A9' THEN
'A' + CONVERT(char(2),CHAR(ASCII(right(MAX(voss.assists_provider_location_code),1))) + 1)
END
END
END AS 'MXT'
FROM
DDDCAVES_DB.DBO.tblVENDOR_OFFICE vo
JOIN dddcaves_db.dbo.tblVENDOR_OFFICE_SERVICE vos
ON vos.VENDOR_OFFICE_ID = vo.VENDOR_OFFICE_ID
JOIN dddcaves_db.dbo.tblVENDOR_OFFICE_SERVICE_SITE voss
ON voss.VENDOR_OFFICE_SERVICE_ID = vos.VENDOR_OFFICE_SERVICE_ID
WHERE
vo.VENDOR_ID ='10023'

guys please help -its urgent

praveena

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-06-17 : 06:50:51

will this be of any help?
http://www.sqlteam.com/item.asp?ItemID=1417

Go with the flow & have fun! Else fight the flow

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources