SQL Server 2000 - Parsing a varchar column

[SDas]

I think this line is causing your problem:

      GROUP BY col, prov_cd) AS B

Replace it with the line in RED:

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.

[Moe1950]

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.