January 10, 2012 at 12:29 pm
Hi
I was wondering if someone could help me.
Quick Background
I have an attribute called ‘Vendor’ that may contain either 4 to 7 characters in length for example, (1001) & (2017836).
This attribute vendor is concatenated with the ship to attribute and the current date/ time under the name DocumentNo. I have recently replaced the last two characters of the year to include the attribute order period.
What I need help with
The problem that I have is that when the vendor attribute contains 4 characters it will appear like this PL061001_1002_0112011016:26:31.
Expected Results
PL061001 _1002_0112011016:26:31 (4 Characters) -- How I would like it to look.
PL062017836_1001_0112011016:26:31(7 Characters)
What I need to do is slightly adjust my code to identify the number of characters in vendor attribute. I will then need to add 3 spaces or 2 depending on the number of characters in the vendor column in the DocumentNo attribute.
I'm not really too sure how to alter my code to apply the changes. Can anyone help me??
Please find below the code I’m using
WITH PL06_CTE(DocumentNo,DocumentItemNo,Vendor,SupplierType,Item,ShipTo,Quantity,ExtEquivUnit,RqdDate,OrderPeriod)
AS (
SELECT--'PL06'+ RTRIM(Vendor) + '_' + RTRIM(Shipto) + '_' + RTRIM(CONVERT(char(20),GETDATE(),112))+''+CONVERT(CHAR(8),SYSDATETIME(),114)AS[DocumentNo]
SUBSTRING('PL06'+ RTRIM(Vendor)+ '_' +RTRIM(Shipto)+'_'+RIGHT('0'+LTRIM(OrderPeriod),2)+''+RIGHT(RTRIM(CONVERT(char(20),GETDATE(),112)),6)+''+CONVERT(CHAR(8),SYSDATETIME(),114),0,35)AS[DocumentNo]
, Ltrim((RIGHT(REPLICATE('0', 5) + CONVERT(VARCHAR(10), ROW_NUMBER()over(PARTITION BY ShipTo ORDER BY Vendor)+0) + '0', 5)))AS[DocumentItemNo]
, Ltrim(Vendor)Vendor
, SupplierType
, Item
, ShipTo
, Ltrim(Quantity)Quantity
, Ltrim(ExtEquivUnit)ExtEquivUnit
, RqdDate
, OrderPeriod
FROM dbo.PL06
)
SELECT DocumentNo,
Ltrim((RIGHT(REPLICATE('0', 5) + CONVERT(VARCHAR(10), ROW_NUMBER()over(PARTITION BY DocumentNo ORDER BY Vendor)+0) + '0', 5)))AS[DocumentItemNo],
Vendor,
SupplierType,
Item,
ShipTo,
cast(Quantity as decimal(10,2)) as Quantity,
cast(ExtEquivUnit as decimal(10,2)) as ExtEquivUnit,
RqdDate
FROM PL06_CTE
WHERE OrderPeriod = 1;
Script to create the table:
CREATE TABLE [dbo].[PL06](
[P] [varchar](32) NULL,
[Secondary] [varchar](32) NULL,
[OrderPeriod] [varchar](2) NULL,
[SupplierType] [varchar](1) NULL,
[Item] [varchar](66) NULL,
[ShipTo] [varchar](12) NULL,
[Priority] [varchar](1) NULL,
[Quantity] [varchar](14) NULL,
[OrderType] [varchar](1) NULL,
[FixedMin] [varchar](8) NULL,
[ComputedMin] [varchar](8) NULL,
[Multiple] [varchar](8) NULL,
[LTDays] [varchar](3) NULL,
[RescheduleABCCode1] [varchar](1) NULL,
[ABC1] [varchar](1) NULL,
[ABC2] [varchar](1) NULL,
[ABC3] [varchar](1) NULL,
[ABC4] [varchar](1) NULL,
[ABC5] [varchar](1) NULL,
[ABC6] [varchar](1) NULL,
[ABC7] [varchar](1) NULL,
[ABC8] [varchar](1) NULL,
[Weight] [varchar](14) NULL,
[Cube] [varchar](14) NULL,
[Cost] [varchar](14) NULL,
[Price] [varchar](14) NULL,
[EquivUnit] [varchar](14) NULL,
[EquivUnit2] [varchar](14) NULL,
[EquivUnit3] [varchar](14) NULL,
[ExtWeight] [varchar](20) NULL,
[ExtCube] [varchar](20) NULL,
[ExtCost] [varchar](20) NULL,
[ExtPrice] [varchar](20) NULL,
[ExtEquivUnit] [varchar](20) NULL,
[ExtEquivUnit2] [varchar](20) NULL,
[ExtEquivUnit3] [varchar](20) NULL,
[RqdDate] [varchar](8) NULL,
[OrderDate] [varchar](8) NULL,
[Vendor] [varchar](10) NULL,
[Description] [varchar](25) NULL,
[cf] [varchar](2) NULL
) ON [PRIMARY]
GO
Inserting the data into the table.
INSERT INTO PL06(P,Secondary,OrderPeriod,SupplierType,Item,ShipTo,RqdDate,OrderDate,Vendor,Description,Quantity,ExtEquivUnit)
VALUES(1001,1002,6,'W',613369,1002,20120326,20120213,2017678,'Red',12.00,2.00)
INSERT INTO PL06(P,Secondary,OrderPeriod,SupplierType,Item,ShipTo,RqdDate,OrderDate,Vendor,Description,Quantity,ExtEquivUnit)
VALUES(1001,1002,7,'W',613369,1002,20120326,20120213,201767,'BLUE',14.00,3.00)
INSERT INTO PL06(P,Secondary,OrderPeriod,SupplierType,Item,ShipTo,RqdDate,OrderDate,Vendor,Description,Quantity,ExtEquivUnit)
VALUES(1001,1002,8,'W',613369,1002,20120326,20120213,20174,'Yellow',10.00,4.00)
INSERT INTO PL06(P,Secondary,OrderPeriod,SupplierType,Item,ShipTo,RqdDate,OrderDate,Vendor,Description,Quantity,ExtEquivUnit)
VALUES(1001,1002,7,'W',613369,1002,20120326,20120213,2017,'Purple',5.00,7.00)
Thanks in advance
__________________________________________________________________________________
Steve J
Jnr Developer
BSc(Hon)
January 10, 2012 at 11:04 pm
Steve,
That is actually not that difficult with the correct T-SQL functions:
Substitute :
rtrim(vendor) + replicate(' ', 7-len(rtrim(vendor)))
for your RTRIM(VENDOR) to get the 7 character vendor code regardless of the incoming vendor code length (being less than 7).
Here is what I used for validation:
declare @vendor varchar(7)
--set @vendor = '5'
set @vendor = '1001'
--set @vendor = '2017836'
select
@vendor + '_' as original_vendor_plus_underscore
, rtrim(@vendor) + replicate(' ', 7-len(rtrim(@vendor))) +'_' as Vendor_char7_plus_underscore
, len(@vendor) as original_data_length_without_spaces
, DATALENGTH(@vendor) as original_data_length_with_spaces
, len(@vendor + replicate(' ', 7-len(@vendor))) as new_data_length_without_spaces
, DATALENGTH(@vendor + replicate(' ', 7-len(@vendor))) as new_data_length_with_spaces
Good luck.
Jan Studenovsky
January 10, 2012 at 11:41 pm
It's even simpler than that:
DECLARE @Vendor varchar(7) = '1001'
SELECT
'PL06' + CONVERT(char(7), @Vendor) + '_1002'
Result:
PL061001 _1002
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 11, 2012 at 12:28 am
Or that ...
January 11, 2012 at 2:26 am
Many thanks for your reply
The problem that i see with the example that you have provided is that is sets the vendor to 1001.
I want the code to automatically enter blank spaces. So i have a attribute/field that can contain up to 7 characters in length. So in my example it 1001 in that field, add enough blank spaces to the front of it, to take all 7 spaces. So in this case, 3 spaces; however, if you put 10011 then would only add 2 spaces. Is that possible without setting the vendor as a variable??
I'm currently trying to adjust the code you have provided to see if i can work it out i'll post my attempt in awhile.
__________________________________________________________________________________
Steve J
Jnr Developer
BSc(Hon)
January 11, 2012 at 2:31 am
Jnrstevej (1/11/2012)
Many thanks for your replyThe problem that i see with the example that you have provided is that is sets the vendor to 1001.
I want the code to automatically enter blank spaces. So i have a attribute/field that can contain up to 7 characters in length. So in my example it 1001 in that field, add enough blank spaces to the front of it, to take all 7 spaces. So in this case, 3 spaces; however, if you put 10011 then would only add 2 spaces. Is that possible without setting the vendor as a variable??
I'm currently trying to adjust the code you have provided to see if i can work it out i'll post my attempt in awhile.
Does what Paul said not work for you? I don't understand your issue with his code. . .
BEGIN TRAN
CREATE TABLE [dbo].[PL06](
[P] [varchar](32) NULL,
[Secondary] [varchar](32) NULL,
[OrderPeriod] [varchar](2) NULL,
[SupplierType] [varchar](1) NULL,
[Item] [varchar](66) NULL,
[ShipTo] [varchar](12) NULL,
[Priority] [varchar](1) NULL,
[Quantity] [varchar](14) NULL,
[OrderType] [varchar](1) NULL,
[FixedMin] [varchar](8) NULL,
[ComputedMin] [varchar](8) NULL,
[Multiple] [varchar](8) NULL,
[LTDays] [varchar](3) NULL,
[RescheduleABCCode1] [varchar](1) NULL,
[ABC1] [varchar](1) NULL,
[ABC2] [varchar](1) NULL,
[ABC3] [varchar](1) NULL,
[ABC4] [varchar](1) NULL,
[ABC5] [varchar](1) NULL,
[ABC6] [varchar](1) NULL,
[ABC7] [varchar](1) NULL,
[ABC8] [varchar](1) NULL,
[Weight] [varchar](14) NULL,
[Cube] [varchar](14) NULL,
[Cost] [varchar](14) NULL,
[Price] [varchar](14) NULL,
[EquivUnit] [varchar](14) NULL,
[EquivUnit2] [varchar](14) NULL,
[EquivUnit3] [varchar](14) NULL,
[ExtWeight] [varchar](20) NULL,
[ExtCube] [varchar](20) NULL,
[ExtCost] [varchar](20) NULL,
[ExtPrice] [varchar](20) NULL,
[ExtEquivUnit] [varchar](20) NULL,
[ExtEquivUnit2] [varchar](20) NULL,
[ExtEquivUnit3] [varchar](20) NULL,
[RqdDate] [varchar](8) NULL,
[OrderDate] [varchar](8) NULL,
[Vendor] [varchar](10) NULL,
[Description] [varchar](25) NULL,
[cf] [varchar](2) NULL
) ON [PRIMARY]
INSERT INTO PL06(P,Secondary,OrderPeriod,SupplierType,Item,ShipTo,RqdDate,OrderDate,Vendor,Description,Quantity,ExtEquivUnit)
VALUES(1001,1002,6,'W',613369,1002,20120326,20120213,2017678,'Red',12.00,2.00)
INSERT INTO PL06(P,Secondary,OrderPeriod,SupplierType,Item,ShipTo,RqdDate,OrderDate,Vendor,Description,Quantity,ExtEquivUnit)
VALUES(1001,1002,7,'W',613369,1002,20120326,20120213,201767,'BLUE',14.00,3.00)
INSERT INTO PL06(P,Secondary,OrderPeriod,SupplierType,Item,ShipTo,RqdDate,OrderDate,Vendor,Description,Quantity,ExtEquivUnit)
VALUES(1001,1002,8,'W',613369,1002,20120326,20120213,20174,'Yellow',10.00,4.00)
INSERT INTO PL06(P,Secondary,OrderPeriod,SupplierType,Item,ShipTo,RqdDate,OrderDate,Vendor,Description,Quantity,ExtEquivUnit)
VALUES(1001,1002,7,'W',613369,1002,20120326,20120213,2017,'Purple',5.00,7.00)
SELECT 'PL06' + CONVERT(char(7), Vendor) + '_1002'
FROM PL06
ROLLBACK
The above returns: -
----------------
PL062017678_1002
PL06201767 _1002
PL0620174 _1002
PL062017 _1002
Is that no correct?
January 11, 2012 at 2:41 am
Cadavre (1/11/2012)
Jnrstevej (1/11/2012)
Many thanks for your replyThe problem that i see with the example that you have provided is that is sets the vendor to 1001.
I want the code to automatically enter blank spaces. So i have a attribute/field that can contain up to 7 characters in length. So in my example it 1001 in that field, add enough blank spaces to the front of it, to take all 7 spaces. So in this case, 3 spaces; however, if you put 10011 then would only add 2 spaces. Is that possible without setting the vendor as a variable??
I'm currently trying to adjust the code you have provided to see if i can work it out i'll post my attempt in awhile.
Does what Paul said not work for you? I don't understand your issue with his code. . .
BEGIN TRANCREATE TABLE [dbo].[PL06](
[P] [varchar](32) NULL,
[Secondary] [varchar](32) NULL,
[OrderPeriod] [varchar](2) NULL,
[SupplierType] [varchar](1) NULL,
[Item] [varchar](66) NULL,
[ShipTo] [varchar](12) NULL,
[Priority] [varchar](1) NULL,
[Quantity] [varchar](14) NULL,
[OrderType] [varchar](1) NULL,
[FixedMin] [varchar](8) NULL,
[ComputedMin] [varchar](8) NULL,
[Multiple] [varchar](8) NULL,
[LTDays] [varchar](3) NULL,
[RescheduleABCCode1] [varchar](1) NULL,
[ABC1] [varchar](1) NULL,
[ABC2] [varchar](1) NULL,
[ABC3] [varchar](1) NULL,
[ABC4] [varchar](1) NULL,
[ABC5] [varchar](1) NULL,
[ABC6] [varchar](1) NULL,
[ABC7] [varchar](1) NULL,
[ABC8] [varchar](1) NULL,
[Weight] [varchar](14) NULL,
[Cube] [varchar](14) NULL,
[Cost] [varchar](14) NULL,
[Price] [varchar](14) NULL,
[EquivUnit] [varchar](14) NULL,
[EquivUnit2] [varchar](14) NULL,
[EquivUnit3] [varchar](14) NULL,
[ExtWeight] [varchar](20) NULL,
[ExtCube] [varchar](20) NULL,
[ExtCost] [varchar](20) NULL,
[ExtPrice] [varchar](20) NULL,
[ExtEquivUnit] [varchar](20) NULL,
[ExtEquivUnit2] [varchar](20) NULL,
[ExtEquivUnit3] [varchar](20) NULL,
[RqdDate] [varchar](8) NULL,
[OrderDate] [varchar](8) NULL,
[Vendor] [varchar](10) NULL,
[Description] [varchar](25) NULL,
[cf] [varchar](2) NULL
) ON [PRIMARY]
INSERT INTO PL06(P,Secondary,OrderPeriod,SupplierType,Item,ShipTo,RqdDate,OrderDate,Vendor,Description,Quantity,ExtEquivUnit)
VALUES(1001,1002,6,'W',613369,1002,20120326,20120213,2017678,'Red',12.00,2.00)
INSERT INTO PL06(P,Secondary,OrderPeriod,SupplierType,Item,ShipTo,RqdDate,OrderDate,Vendor,Description,Quantity,ExtEquivUnit)
VALUES(1001,1002,7,'W',613369,1002,20120326,20120213,201767,'BLUE',14.00,3.00)
INSERT INTO PL06(P,Secondary,OrderPeriod,SupplierType,Item,ShipTo,RqdDate,OrderDate,Vendor,Description,Quantity,ExtEquivUnit)
VALUES(1001,1002,8,'W',613369,1002,20120326,20120213,20174,'Yellow',10.00,4.00)
INSERT INTO PL06(P,Secondary,OrderPeriod,SupplierType,Item,ShipTo,RqdDate,OrderDate,Vendor,Description,Quantity,ExtEquivUnit)
VALUES(1001,1002,7,'W',613369,1002,20120326,20120213,2017,'Purple',5.00,7.00)
SELECT 'PL06' + CONVERT(char(7), Vendor) + '_1002'
FROM PL06
ROLLBACK
The above returns: -
----------------PL062017678_1002
PL06201767 _1002
PL0620174 _1002
PL062017 _1002
Is that no correct?
Hi
I wouldn't say its a problem just slight adjustments, to the results you have provided above. The alignment in line 2, 3 and 4 do not match up with line 1. So what i would expect is that because line 2 is missing 1 character a blank space is inserted(which it has) but it doesn't seem to match up(alignment) with line 1. Does that make sense?
__________________________________________________________________________________
Steve J
Jnr Developer
BSc(Hon)
January 11, 2012 at 2:45 am
Sorry ignore my last comment i was reviewing it in 'Results to Grid' instead of 'Results to Text'
__________________________________________________________________________________
Steve J
Jnr Developer
BSc(Hon)
January 11, 2012 at 2:46 am
Jnrstevej (1/11/2012)
Sorry ignore my last comment i was reviewing it in 'Results to Grid' instead of 'Results to Text'
People generally use a fixed-width font in both Grid and Text output for exactly that reason. You can change the font in SSMS options.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 11, 2012 at 3:06 am
Hello
Just confirming that I've successfully got it working now.
Thanks a lot for your time and help everyone.
Sample
SELECT 'PL06' + CONVERT(char(7), Vendor)+'_' + ShipTo
FROM PL06
Actual
SUBSTRING('PL06'+ CONVERT(CHAR(7),RTRIM(Vendor))+ '_' +RTRIM(Shipto)+'_'+RIGHT('0'+LTRIM(OrderPeriod),2)+''+RIGHT(RTRIM(CONVERT(char(20),GETDATE(),112)),6)+''+CONVERT(CHAR(8),SYSDATETIME(),114),0,35)AS[DocumentNo]
__________________________________________________________________________________
Steve J
Jnr Developer
BSc(Hon)
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply