need some help with stored proc (comlicated problem) 1

[belcom125]

I have a recordset of about 5500 customers, they have their own unique customer numbers (it is not the autoincrement primary key field) which don't follow any convention at the moment. Management came up with new convention for the number. Six characters, first 3 are first 3 customer name chars and last 3 are unique number starting from 001 and must increment if 3 letters repeat again for whichever customer with similar starting name. I must update all cutomer numbers to that new convention. about 85 customers start with "THE" which should be handled as exception and trimmed to remove "THE" and use the 3 chars from the next word in the customer name. there are some more exceptions but I think I can probably do it manually. Customer number is not really hooked on anything else but the customer table so I'm safe there.

I'm looking for a good way to handle this efficietnly and accurately. Lots of suggestions would be much appreciated.
Thanks.

 

[JamesLean]

Well, here's one way. Looks a bit clunky but works on my dummy data.

DECLARE @customer table (
	custid varchar(6),
	custname varchar(30)
)

INSERT @customer
SELECT '1', 'Microsoft' UNION
SELECT '2', 'Micro corp' UNION
SELECT '3', 'Dell' UNION
SELECT '4', 'Apple' UNION
SELECT '5', 'The Computer Company' UNION
SELECT '6', 'Commodore Inc' UNION
SELECT '7', 'Theatrical Computing' UNION
SELECT '8', 'IBM'

UPDATE @customer
SET custid = CASE WHEN custname LIKE 'the %' THEN SUBSTRING(custname, 5, 3) ELSE LEFT(custname, 3) END
	+ RIGHT('000' + (
		SELECT CAST(COUNT(*) AS varchar)
		FROM @customer
		WHERE CASE WHEN custname LIKE 'the %' THEN SUBSTRING(custname, 5, 3) ELSE LEFT(custname, 3) END = CASE WHEN c1.custname LIKE 'the %' THEN SUBSTRING(c1.custname, 5, 3) ELSE LEFT(c1.custname, 3) END
			AND custid <= c1.custid), 3
	)
FROM @customer c1

SELECT * FROM @customer

--James

 

[belcom125]

I think you just saved me a lot of time, Thank a lot!

 

[belcom125]

I am having hard time understanding the where clause. What are you comparing to what? could you please explain? Thanks.
It works pretty good on my 5000 record table however I need to exclude some records by certain criteria. For example there are customer numbers that were already update therefore I don't wanna touch them with update, and some other ones. I am not too sure how to do that at the moment. Could please give me a hint?

It looks like this now:

BEGIN TRANSACTION

UPDATE CustomersTry
SET CustomerNumber =
CASE WHEN CustomerName LIKE 'the %' THEN SUBSTRING(CustomerName, 5, 3)
WHEN CustomerName LIKE '%retire%' THEN SUBSTRING(CustomerName,10,3)
ELSE LEFT(CustomerName, 3) END

+ RIGHT('000' + (SELECT CAST(COUNT(*) AS varchar)

FROM CustomersTry

WHERE CASE WHEN CustomerName LIKE 'the %' THEN SUBSTRING(CustomerName, 5, 3)
ELSE LEFT(CustomerName, 3) END =

CASE WHEN Cust.CustomerName LIKE 'the %' THEN SUBSTRING(Cust.CustomerName, 5, 3)
ELSE LEFT(Cust.CustomerName, 3) END

AND CustomerNumber <= Cust.CustomerNumber), 3)

FROM CustomersTry Cust

SELECT * FROM CustomersTry
Order by CustomerName,CustomerNumber

 

[sunila7]

You could add addtional conditions to your SQL as below (Highlighted in bold). The Inner Where clause in James's SQL is being used to get the Number part of code as in your requirement( '001','002' etc)
So basically what it is doing is, It looks at the current row from Table declared as c1 and finds outs the count of rows having lesser customer ID than the row that is being processed and that generated the no as in your requirement.

UPDATE @customer
SET custid = CASE WHEN custname LIKE 'the %' THEN SUBSTRING(custname, 5, 3) ELSE LEFT(custname, 3) END
+ RIGHT('000' + (
SELECT CAST(COUNT(*) AS varchar)
FROM @customer
WHERE CASE WHEN custname LIKE 'the %' THEN SUBSTRING(custname, 5, 3) ELSE LEFT(custname, 3) END = CASE WHEN c1.custname LIKE 'the %' THEN SUBSTRING(c1.custname, 5, 3) ELSE LEFT(c1.custname, 3) END
AND custid <= c1.custid), 3
)
FROM @customer c1 Where c1.custid not in ('1','2')

Sunil

 

[belcom125]

I was looking at it but wasn't seeing it )
I got what I needed! Excellent!
Thanks guys!

 

[belcom125]

I am faced with a different problem now, basically the script works if all values have to be changed no matter what they are. However there are already about 2000 of those that's been changed manually over time. Therefore when it is counting the records to concatinate with the letters the count is correct however in many cases it is not right because the value already exists with number (manually entered before), management says I can't change the ones already in the database. Therefore I have to come up with a new way... any ideas ?