RE: How to make column dymanic to recognise 1 character or 2 characters

Question

Post reply

RE: How to make column dymanic to recognise 1 character or 2 characters

Jnrstevej

SSCrazy

Points: 2343
More actions
December 14, 2011 at 4:28 am

#248117

Hello,
Background
The piece of code that i have created concatenates 'Vendor', 'ShipTo' and the current datetime in the format year(4), month(2) and days(02) e.g 20111214.
'PL01'+ RTRIM(Vendor) + '' + RTRIM(Shipto)+'_'+'0'+LTRIM(OrderPeriod)+''+RIGHT(RTRIM(CONVERT(char(20),GETDATE(),112)),6)+''+CONVERT(CHAR(8),SYSDATETIME(),114)AS[DocumentNo]
I've replaced the first two characters of the year(2011) with the Order Period(which go's up to 75). So it could return the results such as PL013700091001_0911121410:46:56(32 characters correct) or PL013700091001_07511121410:46:56(33 characters wrong)
Where i need help with
I attempted to hard code 0 so if the order period number is between 1-9 it will display the results as 09, 08, etc. The problem that i am having is that if the order period is 10, 11 or greater then it will produce results such as PL013700091001_01011121410:46:56 which is still including the hard coded 0 which i dont want.
Can some help me alter my code so that it dynamic to know when the order period number is 1 character show 01 or if it is 45 just show 45?? I'm not really sure how to do this and have spent a quite awhile trying to figure this out
I've also attached a compressed copy of the database called Example as well.

WITH PL01_CTE(DocumentNo,DocumentItemNo,Vendor,SupplierType,Item,ShipTo,Quantity,ExtEquivUnit,RqdDate,OrderPeriod)
AS (
SELECT'PL01'+ RTRIM(Vendor) + '' + RTRIM(Shipto)+'_'+'0'+LTRIM(OrderPeriod)+''+RIGHT(RTRIM(CONVERT(char(20),GETDATE(),112)),6)+''+CONVERT(CHAR(8),SYSDATETIME(),114)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
, ltrim(SupplierType)SupplierType
, Item
, ShipTo
, Ltrim(Quantity)Quantity
, Ltrim(ExtEquivUnit)ExtEquivUnit
, RqdDate
, OrderPeriod
FROM dbo.PL01
)
SELECT DocumentNo,
Ltrim((RIGHT(REPLICATE('0', 2) + CONVERT(VARCHAR(10), ROW_NUMBER()over(PARTITION BY DocumentNo ORDER BY Vendor)+0) + '0', 2)))AS[DocumentItemNo],
Vendor,
SupplierType,
Item,
ShipTo,
cast(Quantity as decimal(10,2)) as Quantity,
cast(ExtEquivUnit as decimal(10,2)) as ExtEquivUnit,
RqdDate,
OrderPeriod
FROM PL01_CTE;

Thank you in advance
__________________________________________________________________________________
Steve J
Jnr Developer
BSc(Hon)

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

okbangas SSChampion Points: 11773 More actions · Answer 1

If I understand your requirement correctly:

RIGHT('0'+LTRIM(OrderPeriod),2)

Ole Kristian Velstadbråten Bangås - Virinco - Facebook - Twitter

Concatenating Row Values in Transact-SQL[/url]

Cadavre SSC-Forever Points: 41690 More actions · Answer 2

Use RIGHT('0' + yourData, 2)

e.g.

SELECT RIGHT('0' + test,2), test

FROM (SELECT '1' UNION ALL SELECT '2' UNION ALL

SELECT '3' UNION ALL SELECT '4' UNION ALL

SELECT '5' UNION ALL SELECT '6' UNION ALL

SELECT '7' UNION ALL SELECT '8' UNION ALL

SELECT '9' UNION ALL SELECT '10') a(test)

Returns

test

---- ----

01 1

02 2

03 3

04 4

05 5

06 6

07 7

08 8

09 9

10 10

--EDIT--

Arrggh, too slow 🙂

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Jnrstevej SSCrazy Points: 2343 More actions · Answer 3

Hi okbangas and Cadavre

I've tested the code you guys suggested and I'm getting the results i want. I didn't expect it to be a simple change. Thanks a lot for your help and the explanation.

__________________________________________________________________________________
Steve J
Jnr Developer
BSc(Hon)

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 4

Post withdrawn... didn't read the requirements quite right.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

drew.allen SSC Guru Points: 76985 More actions · Answer 5

CELKO (12/15/2011)
Oh, we also used to put commas at the start of punch cards to make the deck easier to re-arrange – just like you have! I am not kidding about your COBOL in SQL.

That doesn't mean that it doesn't still serve a legitimate purpose. I use it because it makes (un)commenting code so much easier. Before I started using this method, I would comment out a line and then have to go and comment out the comma separately. Then when I was done troubleshooting, I could uncomment out the whole section at one time, but then I would have to manually go back and uncomment the comma at the end of every single line. With the commas at the beginning of the lines, I no longer have this problem.

Both goals derive from the fact that the comma is more closely associated with the following code than the preceding code. It is hardly surprising that the same method is used to accomplish both goals.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA