August 16, 2019 at 3:12 am
I had to write a SP to generate a string with the combination of Country(7 chars)+Province(1 char) + numeric number starts from 01. for eg: JAMAICAK01 where JAMAICA(Country),K(Province) and 01 is the numeric number that gets incremented by 1 for each transaction. The issue I have here is the generated string length max is 10,can be lesser than 10 but not >10.It should be handled in a way with certain rules like 1. the combination don't exist 2. When the numeric unit changes from tens to hundreds making the string length >10, I need to remove the right chars for eg JAMAICAKKK10 to JAMAICAK10 from the right to make the total max length 10.
Declare @Province char(2)
Declare @Country varchar(10)
declare @CounProv varchar(10)
Declare @SimilarCounPRov varchar(max) = '';
declare @FinalString nvarchar(12)
declare @s-2 varchar(50)
declare @s1 varchar(50)
declare @s2 varchar(50)
Set @Province = LEFT('KINGSTON', 1)
Set @Country = LEFT('JAMAICA', 7)
Set @CounProv = @Country+@Province
Select @SimilarCounPRov = MAX(field1) from dbo.table where field1
LIKE '%JAMAICAK%'
if @SimilarCounPRov IS NOT NULL
BEGIN
Set @s-2 = (select fn_AlphaOnly('%JAMAICAK99%')) -- returns JAMAICAK
Set @s1 = (select fn_NumericOnly('%JAMAICAK99%')) -- returns 199
set @s2= @s1 +1 -- increment by 1
if len(@FinalString) > 10
----
need help here----`
August 16, 2019 at 12:49 pm
Suppose the code you need to calculate is called the "Country Province Number" CPN. The issue here is the example you provided only declares variables for a single record and the sequence number is parsed from another CPN.
Does your procedure needs to operate on a single record where the "previous CPN" is passed in as a variable? Or does your procedure need to operate on a table with many rows?
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
August 16, 2019 at 1:18 pm
Thanks for your response. My procedure here need to operate on a table with possibility of many rows having the same ProvinceCountry combination with numeric sequence starting from 01 as below. The new CPN code should have the next sequence number with same ProvinceCountry combination with the rule of having total length of 10, removing chars from right on ProvinceCountry Combination depending on numeric unit.
Eg:
JAMAICAK01
JAMAICAK02
JAMAICAK03
JAMAICAK04
JAMAICAK05, ..........
JAMAICAK99
JAMAICA100 (here letter K is truncated to accommodate length 10 on sequence numeric value)
August 16, 2019 at 1:34 pm
Why do you declare province to be char(2) and then assign it left(col_name, 1) which is only 1 character?
Does the sequence reset for each country?
What is the needed sort order of the rows?
Could you provide the DDL for this table?
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
August 16, 2019 at 1:58 pm
You can create a tracking table and use a Quirky Update in a proc to do this
CREATE TABLE dbo.CountryProvNumber (
Country varchar(10) NOT NULL
, Province varchar(2) NOT NULL
, LastUsedNum int NOT NULL DEFAULT(0)
, PRIMARY KEY CLUSTERED (Country, Province)
);
GO
CREATE PROCEDURE dbo.pr_GetNextCountryProvCode
@Country varchar(10)
, @Province varchar(2)
, @CountryProvCode varchar(10) OUTPUT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @NextNum int;
DECLARE @TextNum varchar(10);
INSERT INTO dbo.CountryProvNumber (Country, Province)
SELECT @Country, @Province
WHERE NOT EXISTS (SELECT 1 FROM dbo.CountryProvNumber AS cpn WITH (XLOCK, HOLDLOCK)
WHERE cpn.Country = @Country
AND cpn.Province = @Province);
UPDATE dbo.CountryProvNumber
SET @NextNum = LastUsedNum = LastUsedNum +1
WHERE Country = @Country
AND Province = @Province;
SET @TextNum = CASE WHEN @NextNum < 10 THEN '0' ELSE '' END + CONVERT(varchar(10), @NextNum);
SELECT @CountryProvCode = LEFT(@Country + @Province, 10 -LEN(@TextNum)) + @TextNum;
END;
GO
DECLARE @CountryProvCode varchar(10);
EXEC dbo.pr_GetNextCountryProvCode
@Country = 'JAMAICA'
, @Province = 'K'
, @CountryProvCode = @CountryProvCode OUTPUT;
SELECT CountryProvCode = @CountryProvCode;
August 16, 2019 at 2:16 pm
Sory @province is not 2 chars it's 1 char. That's the business rule i was asked to do so. 1 char from province 7 chars from Country with seq no. for each record that contains the same combination or different provincecountry that starts with seqno. 01.
August 16, 2019 at 2:21 pm
Sory @province is not 2 chars it's 1 char. That's the business rule i was asked to do so. 1 char from province 7 chars from Country with seq no. for each record that contains the same combination or different provincecountry that starts with seqno. 01.
So change the declaration of Province in the table and @Province in the proc from varchar(2) to char(1)
August 16, 2019 at 4:39 pm
Step 1: generate some dummy data
/* create a table to hold the country and province values */
/* as well as a column to store the cntry_prov_code */
drop table if exists dbo.country_province;
go
create table dbo.country_province(
cp_pkint identity(1,1) constraint pk_country_provide primary key not null,
countryvarchar(10) not null,
provincechar(1) not null,
cntry_prov_codevarchar(10) default 'XXXXXXXY##');
go
/* country dummy data (4 values * 101 rows = 404 rows)*/
drop table if exists #cntry;
go
create table #cntry(countryvarchar(10) not null);
insert #cntry(country) select top 101 'Albania' from sysobjects;
insert #cntry(country) select top 101 'BahamasIsl' from sysobjects;
insert #cntry(country) select top 101 'Chile' from sysobjects;
insert #cntry(country) select top 101 'Denmark' from sysobjects;
go
/* province dummy data (3 unique values = 3 rows) */
drop table if exists #prov;
go
create table #prov(provincechar(1) unique not null);
insert #prov(province)
values ('A'), ('B'), ('C');
/* table to test with: (404 rows * 3 rows = 1,212 rows total) */
insert dbo.country_province(country, province)
select
c.*,
p.*
from
#cntry c
cross join
#prov p;
Step 2: Run update cte and select all records:
with cp_cte(country, province, cntry_prov_code, cntry_prov, row_sequence) as (
select
country,
province,
cntry_prov_code,
concat(left(country, 7), province) cntry_prov,
row_number() over(partition by concat(left(country, 7), province) order by concat(left(country, 7), province) asc) row_sequence
from
dbo.country_province)
update cp_cte
set
cntry_prov_code = concat(left(cntry_prov, 10-len(concat(case when len(cast(row_sequence as varchar(9)))=1 then '0' end, cast(row_sequence as varchar(9))))), concat(case when len(cast(row_sequence as varchar(9)))=1 then '0' end, cast(row_sequence as varchar(9))))
select * from dbo.country_province;
Step 3: To see the parts of how it comes together (instead of the update, run this select)
with cp_cte(country, provice, cntry_prov, row_sequence) as (
select
country,
province,
concat(left(country, 7), province) cntry_prov,
row_number() over(partition by concat(left(country, 7), province) order by concat(left(country, 7), province) asc) row_sequence
from
dbo.country_province)
select
cntry_prov,
left(cntry_prov, 10-len(concat(case when len(cast(row_sequence as varchar(9)))=1 then '0' end, cast(row_sequence as varchar(9))))) stem,
concat(case when len(cast(row_sequence as varchar(9)))=1 then '0' end, cast(row_sequence as varchar(9))) sequence_str,
concat(left(cntry_prov, 10-len(concat(case when len(cast(row_sequence as varchar(9)))=1 then '0' end, cast(row_sequence as varchar(9))))), concat(case when len(cast(row_sequence as varchar(9)))=1 then '0' end, cast(row_sequence as varchar(9)))) cntry_prov_code,
len(concat(left(cntry_prov, 10-len(concat(case when len(cast(row_sequence as varchar(9)))=1 then '0' end, cast(row_sequence as varchar(9))))), concat(case when len(cast(row_sequence as varchar(9)))=1 then '0' end, cast(row_sequence as varchar(9))))) code_length,
row_sequence
from
cp_cte c;
Pretty sure this demonstrates all of the business rules required.
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
August 22, 2019 at 11:32 am
This was removed by the editor as SPAM
August 26, 2019 at 3:39 am
Instead of inventing your own encoding scheme, did you ever consider using the ISO Standards that are already in place? Just Google them. This is as silly as using the Potrzebie System of Weights and Measures (https://en.wikipedia.org › wiki › Potrzebie).
Please post DDL and follow ANSI/ISO standards when asking for help.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply