-
SQL Server 2000 - Parsing a varchar column
I have a column in an SQL Server 2000 table. When running a SELECT against this table, this column is always returned as it is a very intregal part of the rest of the query.
I now need to include a filter on this column. Here is an example of the data in the column.
00002063020092R
00002063020091R
00002063020090O
00003063020091R
00003063020090O
00004093020096R
00004093020093R
00004093020091R
00004093020090O
00005123120096R
00005123120095R
00005123120094R
00005123120091R
00005123120090O
00007063020091R
00007063020090O
00008063020092R
00008063020091R
00008063020090O
00011093020096R
00011093020092R
00011093020091R
00011093020090O
00014063020092R
00014063020091R
00014063020090O
00016123120093R
00016123120091R
00016123120090O
00016123120090M
This goes on for several thousand rows.
The filter I need to implement deals with the first 5 characters and their relationship to the 14th character. From a group I need to extract the largest number (character 14) from within that group (the first five characters) within a single SELECT query.
Example:
This
00002063020092R
00002063020091R
00002063020090O
00003063020091R
00003063020090O
00004093020096R
00004093020093R
00004093020091R
00004093020090O
00005123120096R
00005123120095R
00005123120094R
00005123120091R
00005123120090O
Should return only
00002063020092R
00003063020091R
00004093020096R
00005123120096R
The return represents this highest revision of its particular group (as represented by the first five characters.)
I don't even know where to start.
-
Moe1950, I don't have SQL Server 2000 available for testing but something like this may work:
Code:
SELECT COL
FROM table-name A
, (SELECT SUBSTRING(col, 1,5) as first5
, MAX(SUBSTRING(col,14,1)) as max14
FROM table-name
GROUP BY SUBSTRING(COL,1,5)
) AS B
WHERE SUBSTRING(A.col, 1,5) = B.first5
AND SUBSTRING(A.col,1,14) = B.max14
NOTE: You may be able to GROUP BY first5.
-
Thanks SDas!
As is, it returns 0 records but at least I have something to play around with now.
-
I got it...it was actually a typo on your part
Code:
SELECT COL
FROM table-name A
, (SELECT SUBSTRING(col, 1,5) as first5
, MAX(SUBSTRING(col,14,1)) as max14
FROM table-name
GROUP BY SUBSTRING(COL,1,5)
) AS B
WHERE SUBSTRING(A.col, 1,5) = B.first5
AND SUBSTRING(A.col,1,14) = B.max14
'should be
AND SUBSTRING(A.col,14,1)
With that one change, it all works.
Thank you very much.
-
I spoke too soon...further testing reveals that many rows that should be included are not being included.
Back to the drawing board, but, as I said, at least I have something to work with.
(Of course, any suggestions are most welcome. )
-
I've been playing around with this for days. Here is what I have
Code:
SELECT col
FROM table-name A,
(SELECT SUBSTRING(col, 1,5) AS pcode,
MAX(SUBSTRING(col, 14, 1)) AS rev_number
FROM table-name A
GROUP BY col, prov_cd) AS B
WHERE SUBSTRING(A.col, 1, 5) = B.pcode
AND SUBSTRING(A.col,14,1) = B.rev_number
GROUP BY col
ORDER BY col
It does do some filtering. For example, in my table I have:
00003063020090O
00003063020091R
00003063020092R
00003063020093R
00003063020094R
00003063020095R
00003063020096R
What is should return is:
00003063020096R
What it does return is:
00003063020090O
00003063020091R
00003063020093R
00003063020096R
Does anyone have any suggestions at all?
-
I think this line is causing your problem:
Code:
GROUP BY col, prov_cd) AS B
Replace it with the line in RED:
Code:
SELECT col
FROM table-name A,
(SELECT SUBSTRING(col, 1,5) AS pcode,
MAX(SUBSTRING(col, 14, 1)) AS rev_number
FROM table-name A
GROUP BY SUBSTRING(COL, 1, 5) ) AS B
WHERE SUBSTRING(A.col, 1, 5) = B.pcode
AND SUBSTRING(A.col,14,1) = B.rev_number
GROUP BY col
ORDER BY col
In the derived table B you are trying to get the MAX Revision Number for each Distinct P code value, so you need to Group by PCODE (or SUBSTRING(COL1,1,5) ). I don't know what PROV_CD is but it shouldn't be in the GROUP BY clause.
If you have to have it, it will have to explain its function for the query to make sense.
-
I think I figured out what is happening. I just don't know what to do to fix it.
Here is a breakdown of a typical entry:
00003063020091R
00003 = pcode
06302009 = date - typically a fiscal year end date
0O = last two digits indicate revision number
Here is what is happening.
When I run the code and ask for a specific year (which I would always do), certain pcodes got skipped and I just now figured out why.
Lets say for 2009 and 2008 I had the following entries:
00003063020090O
00003063020091R
00003063020092R
00003063020080O
00003063020081R
00003063020082R
00003063020083R
00003063020084R
00003063020085R
When I ask for the highest one of the group I get 00003063020085R which is correct with no fiscal year end filter. However, when I throw in a fiscal year filter = 2009, 00003 got skipped altogether.
-
So the question is what do you really want.
Do you want the highest revision regardless of year?
Do you want the highest revision within a particular year (or year range)?
Since you mentioned you will always supply a year, I will assume within a supplied year. I am hard-coding '2009' but you will have to make this a string variable:
Code:
SELECT col
FROM table-name A,
(SELECT SUBSTRING(col, 1,5) AS pcode,
MAX(SUBSTRING(col, 14, 1)) AS rev_number
FROM table-name A
AND SUBSTRING(COL,10,4) = '2009'
GROUP BY SUBSTRING(COL, 1, 5) ) AS B
WHERE SUBSTRING(A.col, 1, 5) = B.pcode
AND SUBSTRING(A.col,14,1) = B.rev_number
AND SUBSTRING(A.col,10,4) = '2009'
GROUP BY col
ORDER BY col
-
That was the missing piece. Thank you!
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|