April 28, 2010 at 6:09 am
SwePeso (4/28/2010)
Here are two problems?The original and the "Excel column numbering" question?
For the original question, to keep the result within VARCHAR(8000) the maximum line number to convert is 208000 which gives you 8000 Z's.
Oh, right - I see now: I was pursuing Jeff's idea (the Excel-type scheme, yes):
If 27 = AA, then why would 28 = BB??? Shouldn't 28 = AB?
Your neat solution goes with the original question - understand now 🙂
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 28, 2010 at 6:48 am
Cool my first interesting problem! 😀
So if I may piggy back on this, I have a semi related incrementing issue... I have need for a second counter (sub counter) based on the row number. The problem is that this counter will need to be generated at the design level in the report (BIDS 2008).
I have a recursive grouping at the design level then based on the row number generates 5 paragraphs for each row. Each of the paragraphs will be incremented by 1 so it will look this:
Row# 1: Request 1 <text>
Request 2 <text>
...
Request 5 <text>
Row# 2: Request 6 <text>
Request 7 <text>
...
Request 10 <text>
...
Row# r: r + (n +1) except when (r + n) mod 5 = 0 then n = n
The "n = n" is to cover the transition between each row ( row 1 + 4 = 5 and row 2 + 4 = 6, etc...).
My problem is not sure how to approach this since it is at the design level... Am I over thinking this... maybe just use some code to create the number and pass via parameter?
April 28, 2010 at 7:48 am
I posted a generic example here http://weblogs.sqlteam.com/peterl/archive/2010/04/28/The-Excel-Column-Name-assigment-problem.aspx
if someone is interested about a generic solution...
The solution is a one-statement query and works really well for any number under INT range.
N 56°04'39.16"
E 12°55'05.25"
April 28, 2010 at 8:02 am
Nice! Was wondering if anyone could find a way to get the Nth value without having to add in extra code for every new character needed. Although that code you posted seemed to throw up a little issue Peso.
If you check the following:
701 = ZY (Correct)
702 = AZZ (Incorrect)
703 = AAA (Correct)
Seems to add in that extra A when it should be just ZZ
April 28, 2010 at 8:12 am
That should work Peso... the sequence would very rarely exceed 200. 99.9% will range from 50 - 150.
So comes my rookie question... how would I incorporate that into my report?
April 28, 2010 at 9:01 am
You can make the code as a scalar function in your database and add the result of the query to your resultset in your report.
N 56°04'39.16"
E 12°55'05.25"
April 28, 2010 at 9:19 am
Dohsan (4/28/2010)
701 = ZY (Correct)702 = AZZ (Incorrect)
703 = AAA (Correct)
Edge condition fixed!
N 56°04'39.16"
E 12°55'05.25"
April 28, 2010 at 9:52 am
For giggles, here's an extension to my previous code that supports sequnces up to 146,813,779,479,510 (way beyond INT32) - which gives ZZZZZZZZZZ (ten of them!)
SELECT N.n,
sequence =
RIGHT
(
CHAR(65 + (((((((((((N.n - 1) / 26 - 1) / 26 - 1) / 26 - 1) / 26 - 1) / 26 - 1) / 26 - 1) / 26 - 1) / 26 - 1) / 26 - 1) % 26)) +
CHAR(65 + ((((((((((N.n - 1) / 26 - 1) / 26 - 1) / 26 - 1) / 26 - 1) / 26 - 1) / 26 - 1) / 26 - 1) / 26 - 1) % 26)) +
CHAR(65 + (((((((((N.n - 1) / 26 - 1) / 26 - 1) / 26 - 1) / 26 - 1) / 26 - 1) / 26 - 1) / 26 - 1) % 26)) +
CHAR(65 + ((((((((N.n - 1) / 26 - 1) / 26 - 1) / 26 - 1) / 26 - 1) / 26 - 1) / 26 - 1) % 26)) +
CHAR(65 + (((((((N.n - 1) / 26 - 1) / 26 - 1) / 26 - 1) / 26 - 1) / 26 - 1) % 26)) +
CHAR(65 + ((((((N.n - 1) / 26 - 1) / 26 - 1) / 26 - 1) / 26 - 1) % 26)) +
CHAR(65 + (((((N.n - 1) / 26 - 1) / 26 - 1) / 26 - 1) % 26)) +
CHAR(65 + ((((N.n - 1) / 26 - 1) / 26 - 1) % 26)) +
CHAR(65 + (((N.n - 1) / 26 - 1) % 26)) +
CHAR(65 + ((N.n - 1) % 26))
, LOG(N.n - N.n / 26) / LOG(26) + 1
)
FROM (SELECT 146813779479510) N (n);
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 28, 2010 at 10:07 am
hmmm now that I am looking more closely (and if I am reading it correctly)
26 = AA
27 = AB but for my purpose it equals BB
28 = AC but for my purpose it equals CC
Allow me to explain a bit more in depth what I need to accomplish....
I need to generate a report which is actually a legal document to export into MS Word.
There are a list of bills that are returned by row by my query. Each bill is then entered into court as an Exhibit. That is where the first sequence comes in. Exhibit A, Exhibit B, ..., Exhibit n. The second sequencer is numeric. Each exhibit has 5 paragraphs that is sent to the defense attorney. Each paragraph begins with Request #n (Request #1, Request #2,...,Request #n).
At the design level I have a parent grouping based on the unique key for the case, then a second grouping on the unique key for the provider that is related to the bill. This generates recursively (hope I am using that correctly) the 5 paragraphs per bill with the correct Exhibit letter.
So the last numeric sequence number will be 5 x row_number.
Now the part that I can't seem to grasp is how to handle the numeric sequencer so the correct sequence number is maintained since there is a loop at the child grouping.
Does that help?
April 28, 2010 at 12:22 pm
Paul White NZ (4/28/2010)
For giggles, here's an extension to my previous code that supports sequnces up to 146,813,779,479,510 (way beyond INT32) - which gives ZZZZZZZZZZ (ten of them!)
Paul, what if you change
1) "RIGHT" with "REPLACE"
2) "LOG(N.n - N.n / 26) / LOG(26) + 1" with " '@', '' "
//Peter
N 56°04'39.16"
E 12°55'05.25"
April 28, 2010 at 7:15 pm
SwePeso (4/28/2010)
Paul White NZ (4/28/2010)
For giggles, here's an extension to my previous code that supports sequnces up to 146,813,779,479,510 (way beyond INT32) - which gives ZZZZZZZZZZ (ten of them!)Paul, what if you change
1) "RIGHT" with "REPLACE"
2) "LOG(N.n - N.n / 26) / LOG(26) + 1" with " '@', '' "
//Peter
Oddly, that's how I had it to begin with (though I used SPACE(1)). You might wonder why I changed it: mostly because I don't have a lot of math skills, so when I thought of the LOG thing, it seemed very cool to me. I am now wedded to it 🙂
REPLACE is probably better though, I do concede that.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 29, 2010 at 4:15 am
Here is a recursive solution:
ALTER FUNCTION ALPHA_NUM(@NUM BIGINT)
RETURNS VARCHAR(10) ASBEGIN
DECLARE @MOD AS BIGINT
DECLARE @REM AS BIGINT
DECLARE @ALPHA AS VARCHAR(10)
SET @MOD = (@NUM-1) % 26
SET @REM = (@NUM-1) / 26
IF @REM = 0
SET @ALPHA = CHAR(65 + @MOD)
ELSE
SET @ALPHA = (SELECT [dbo].[ALPHA_NUM](@REM)) + CHAR(65 + @MOD)
RETURN @ALPHA
END
May 4, 2010 at 9:23 am
Thanks everyone for the suggestions and help... Just wanted to post my solution:
I knew that my last value for the row number was going to be 5 times the last row (because there is 5 paragraph generated by each row) which I use this:
((ROW_NUMBER() OVER(ORDER BY s.service_begin_date)) * 5) AS NumCount
so my column will be 5, 10, 15, 20,..., max(rownumber) * 5
Then in the expression at the design level I just manipulate that value:
(NumCount - 4) for first paragraph
(NumCount - 3) for second paragraph
(NumCount - 2) for third paragraph
(NumCount - 1) for fourth paragraph
(NumCount ) for fifth paragraph
Not sure why I tried to make this so difficult....
Thanks again!
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply