I am trying to code a stroed procedure that creates and ID column based upon a concatenation of 3 or 4 other numbers
I need the code to represent other id info and add them together
519 - dept code
23 product code
24 - client code
001 - company sku (unknown)
so the code needs to be 519+23+24+001 or 5192324001 NOT the addition of the numbers
That is my first problem, second If I know the first three numbers then I need to search the table for the last number
example if the last item for 5192324 is 005 I need to search for the last 5192324 NOT the last record in the db but the last record that begins with 5192324 then add 1 to it. SELECT @NewProductID = Product FROM Table WHERE ProductID LIKE '5192324___' ; SET
@ProductID = @NewProductID + 1
First I would need to setect the last ID that Begins with 5192324 there could be 001 to 999
[SUMMARY]
Concat 510+23+24 So I can use that to find the last record that begins with 5102324
Is this understandable?
"Success is the Sum of Small Efforts, Repeated Day in and Day Out - Without Ceasing!"
Currently Learning: ASP, SQL, CSS, JavaScript, AJAX, XML, XSLT, C# So please be patient with me! Thanks
create procedure testpro
@deptcode int,
@productcode int,
@clientcode int,
@companycode int
as
begin
declare @id varchar(20);
declare @id1 varchar(20);
declare @len int;
declare @len1 int;
declare @NewProductID int;
set @id=cast(@deptcode as varchar)+cast(@productcode as varchar)+cast(@clientcode as varchar)+cast(@companycode as varchar)
set @len1=@len-3;
set @id1=LEFT(@id,@len1);
SELECT @NewProductID = Product FROM Table WHERE ProductID LIKE @id1+'%' ;
end
Thanks,
Amy Peng
Please mark the replies as answers if they help or unmark if not.
Feedback to us
I am trying to code a stroed procedure that creates and ID column based upon a concatenation of 3 or 4 other numbers
I need the code to represent other id info and add them together
519 - dept code
23 product code
24 - client code
001 - company sku (unknown)
so the code needs to be 519+23+24+001 or 5192324001 NOT the addition of the numbers
That is my first problem, second If I know the first three numbers then I need to search the table for the last number
example if the last item for 5192324 is 005 I need to search for the last 5192324 NOT the last record in the db but the last record that begins with 5192324 then add 1 to it. SELECT @NewProductID = Product FROM Table WHERE ProductID LIKE '5192324___' ; SET
@ProductID = @NewProductID + 1
First I would need to setect the last ID that Begins with 5192324 there could be 001 to 999
[SUMMARY]
Concat 510+23+24 So I can use that to find the last record that begins with 5102324
Is this understandable?
I understand exactly what you are trying to do. I also think it's a bad idea.
I think you would be better off storing the department, product and client in separate fields. These would be part of your primary key and would all be foreign keys. The last part you can generate on your own. However, 3 digit numeric strings limits you
to 999 records which might not be enough.
BEGIN
--CONSIDERING ALL THESE ARE INTEGERS
DECLARE @Dept_Code INT, @Product_Code INT , @Client_Code INT , @Company_Code INT
DECLARE @ID BIGINT
--TEMPORARY TABLE
DECLARE @CodesInfo AS TABLE
(
ID BIGINT,
Dept_Code INT,
Product_Code INT ,
Client_Code INT ,
Company_Code INT
)
INSERT INTO @CodesInfo VALUES(5192124001 , '519','21','24','001')
INSERT INTO @CodesInfo VALUES(5182625002 , '518','26','25','002')
INSERT INTO @CodesInfo VALUES(5172326005 , '517','23','26','005')
INSERT INTO @CodesInfo VALUES(5162521007 , '516','25','21','007')
INSERT INTO @CodesInfo VALUES(5152928004 , '515','29','28','004')
INSERT INTO @CodesInfo VALUES(5142829008 , '514','28','29','008')
INSERT INTO @CodesInfo VALUES(5132720021 , '513','27','20','021')
INSERT INTO @CodesInfo VALUES(5192124100 , '519','21','24','100')
INSERT INTO @CodesInfo VALUES(5192124026 , '519','21','24','026')
INSERT INTO @CodesInfo VALUES(5192124023 , '519','21','24','023')
INSERT INTO @CodesInfo VALUES(5192124011 , '519','21','24','011')
--SUPPOSE WE KNOW THE Dept_Code , Product_Code , Client_Code , THEN WE NEED TO FIND THE COMPANY CODE
--i.e LAST RECORD WHICH MATCHED THE ID
SET @Dept_Code = '519'
SET @Product_Code = '21'
SET @Client_Code='24'
/*SELECTING ID OF LAST RECORD THAT MATCHES Concatenation of (Dept_Code + Product_Code + Client_Code) */
SELECT TOP 1 ID FROM @CodesInfo
WHERE CONVERT(VARCHAR,ID) LIKE CONVERT(VARCHAR,@Dept_Code) + CONVERT(VARCHAR,@Product_Code) + CONVERT(VARCHAR,@Client_Code) + '%'
ORDER BY Company_Code DESC
END
GO
I am open to any ideas, All the code is really for is coupon-codes for coupons for products/services, as well as for other uses
519111
519=dept, 1 = account type, 1 = promotion (Christmas, Easter, etc...), 1 = new customer only
I was only trying to eliminate a couple of steps in case one of my admins tried to manually enter a coupon code that didn't fit with the scheme.
When adding a new coupon for use in the database several dropdownlists asks for service, promotion, account type, customer type, this is the table that gets checked when creating an invoice, so if I am trying to add a coupon to an invoice that is not for
a service that the client is paid for it checks the first three digits of the couponcode for the serviceID, then it checks the clients account type against the coupons account type so a residential client cannot use a coupon made for a commercial client, it
also checks for previous invoices that have that serviceID so a returning client cannot get a new customer only coupon.
I suppose I could break all of these up into separate columns and concat them when printing the code on the coupon, however, I am still left with successive ID's for each coupon, my desire would be this
519111001
519111002
519212003
520111001
So each successive service would start its numbering sequence at 001, with an identity column that increments, it would not restart at 001 if the serviceID was different. I would get
5191111
5191112
5192123...
520111100
and the qty of numbers for the code would be different.
So if I emplement separate columns, the second challenge still remains how to detect the last number used for a specific service and increment by 1,
If I use separate columns I would still have to figure out how to read them as one, when inserting them into a combobox for insertion into an invoice.
"Success is the Sum of Small Efforts, Repeated Day in and Day Out - Without Ceasing!"
Currently Learning: ASP, SQL, CSS, JavaScript, AJAX, XML, XSLT, C# So please be patient with me! Thanks
I am open to any ideas, All the code is really for is coupon-codes for coupons for products/services, as well as for other uses
519111
519=dept, 1 = account type, 1 = promotion (Christmas, Easter, etc...), 1 = new customer only
I was only trying to eliminate a couple of steps in case one of my admins tried to manually enter a coupon code that didn't fit with the scheme.
When adding a new coupon for use in the database several dropdownlists asks for service, promotion, account type, customer type, this is the table that gets checked when creating an invoice, so if I am trying to add a coupon to an invoice that is not for
a service that the client is paid for it checks the first three digits of the couponcode for the serviceID, then it checks the clients account type against the coupons account type so a residential client cannot use a coupon made for a commercial client, it
also checks for previous invoices that have that serviceID so a returning client cannot get a new customer only coupon.
I suppose I could break all of these up into separate columns and concat them when printing the code on the coupon, however, I am still left with successive ID's for each coupon, my desire would be this
519111001
519111002
519212003
520111001
So each successive service would start its numbering sequence at 001, with an identity column that increments, it would not restart at 001 if the serviceID was different. I would get
5191111
5191112
5192123...
520111100
and the qty of numbers for the code would be different.
So if I emplement separate columns, the second challenge still remains how to detect the last number used for a specific service and increment by 1,
If I use separate columns I would still have to figure out how to read them as one, when inserting them into a combobox for insertion into an invoice.
It's a lot easier to concatonate separate columns than it is to parse a single column into it's components. Separate columns will also make it easier to satisfy any reporting requirements that may arise.
As far as detecting the last number used and adding 1 to it, don't. First your database can do it better. Second, simultaneous use may result in duplicate value problems.
As far as the number on your coupon goes, insisting on 2 leading 0's still limits you to 999 numbers. That could be a huge problem, depending on your business requirements.
Marked as answer by Bobby-Z on Nov 26, 2012 11:27 PM
So I changed my code, instead of using the stored procedure, I created the couponcode in C# and instead of numbering, I used the coupon Value for the final number, this way there will be no duplicates
Thanks for the thoughts! Helped!
"Success is the Sum of Small Efforts, Repeated Day in and Day Out - Without Ceasing!"
Currently Learning: ASP, SQL, CSS, JavaScript, AJAX, XML, XSLT, C# So please be patient with me! Thanks
Bobby-Z
Contributor
2838 Points
1120 Posts
Number Calculations - Concatenations
Nov 21, 2012 04:38 AM|LINK
I am trying to code a stroed procedure that creates and ID column based upon a concatenation of 3 or 4 other numbers
I need the code to represent other id info and add them together
519 - dept code
23 product code
24 - client code
001 - company sku (unknown)
so the code needs to be 519+23+24+001 or 5192324001 NOT the addition of the numbers
That is my first problem, second If I know the first three numbers then I need to search the table for the last number
example if the last item for 5192324 is 005 I need to search for the last 5192324 NOT the last record in the db but the last record that begins with 5192324 then add 1 to it. SELECT @NewProductID = Product FROM Table WHERE ProductID LIKE '5192324___' ; SET @ProductID = @NewProductID + 1
First I would need to setect the last ID that Begins with 5192324 there could be 001 to 999
[SUMMARY]
Concat 510+23+24 So I can use that to find the last record that begins with 5102324
Is this understandable?
Currently Learning: ASP, SQL, CSS, JavaScript, AJAX, XML, XSLT, C# So please be patient with me! Thanks
shaiksameer
Member
32 Points
6 Posts
Re: Number Calculations - Concatenations
Nov 21, 2012 04:43 AM|LINK
visit the link below, this may help you..
http://dotnetdots.wordpress.com/2012/11/10/how-to-set-identity-keyauto-increment-on-varchar-column-in-sql-server/
-sameer
wmec
Contributor
6195 Points
3214 Posts
Re: Number Calculations - Concatenations
Nov 21, 2012 05:10 AM|LINK
Try
...
where column like cast(510 as varchar)+cast(23 as varchar)+cast(24 as varchar) +'%'
HuaMin Chen
Amy Peng - M...
Star
9968 Points
952 Posts
Microsoft
Re: Number Calculations - Concatenations
Nov 23, 2012 03:07 AM|LINK
Hi Bobby-z,
Please try to refer to the following code:
Thanks,
Amy Peng
Feedback to us
Develop and promote your apps in Windows Store
Dan Bracuk
Contributor
3970 Points
1096 Posts
Re: Number Calculations - Concatenations
Nov 23, 2012 03:18 AM|LINK
I understand exactly what you are trying to do. I also think it's a bad idea.
I think you would be better off storing the department, product and client in separate fields. These would be part of your primary key and would all be foreign keys. The last part you can generate on your own. However, 3 digit numeric strings limits you to 999 records which might not be enough.
But that's just my opinion.
Sailaja Redd...
Member
452 Points
81 Posts
Re: Number Calculations - Concatenations
Nov 23, 2012 04:06 AM|LINK
Hi,
try this .
Sailaja.
MyTechnical Blog
Bobby-Z
Contributor
2838 Points
1120 Posts
Re: Number Calculations - Concatenations
Nov 24, 2012 02:37 PM|LINK
I am open to any ideas, All the code is really for is coupon-codes for coupons for products/services, as well as for other uses
519111
519=dept, 1 = account type, 1 = promotion (Christmas, Easter, etc...), 1 = new customer only
I was only trying to eliminate a couple of steps in case one of my admins tried to manually enter a coupon code that didn't fit with the scheme.
When adding a new coupon for use in the database several dropdownlists asks for service, promotion, account type, customer type, this is the table that gets checked when creating an invoice, so if I am trying to add a coupon to an invoice that is not for a service that the client is paid for it checks the first three digits of the couponcode for the serviceID, then it checks the clients account type against the coupons account type so a residential client cannot use a coupon made for a commercial client, it also checks for previous invoices that have that serviceID so a returning client cannot get a new customer only coupon.
I suppose I could break all of these up into separate columns and concat them when printing the code on the coupon, however, I am still left with successive ID's for each coupon, my desire would be this
519111001
519111002
519212003
520111001
So each successive service would start its numbering sequence at 001, with an identity column that increments, it would not restart at 001 if the serviceID was different. I would get
5191111
5191112
5192123...
520111100
and the qty of numbers for the code would be different.
So if I emplement separate columns, the second challenge still remains how to detect the last number used for a specific service and increment by 1,
If I use separate columns I would still have to figure out how to read them as one, when inserting them into a combobox for insertion into an invoice.
Currently Learning: ASP, SQL, CSS, JavaScript, AJAX, XML, XSLT, C# So please be patient with me! Thanks
Dan Bracuk
Contributor
3970 Points
1096 Posts
Re: Number Calculations - Concatenations
Nov 24, 2012 03:32 PM|LINK
It's a lot easier to concatonate separate columns than it is to parse a single column into it's components. Separate columns will also make it easier to satisfy any reporting requirements that may arise.
As far as detecting the last number used and adding 1 to it, don't. First your database can do it better. Second, simultaneous use may result in duplicate value problems.
As far as the number on your coupon goes, insisting on 2 leading 0's still limits you to 999 numbers. That could be a huge problem, depending on your business requirements.
Bobby-Z
Contributor
2838 Points
1120 Posts
Re: Number Calculations - Concatenations
Nov 26, 2012 11:27 PM|LINK
So I changed my code, instead of using the stored procedure, I created the couponcode in C# and instead of numbering, I used the coupon Value for the final number, this way there will be no duplicates
Thanks for the thoughts! Helped!
Currently Learning: ASP, SQL, CSS, JavaScript, AJAX, XML, XSLT, C# So please be patient with me! Thanks