Results 1 to 10 of 10

Thread: SQL Server 2000 - Parsing a varchar column

  1. #1
    Join Date
    Dec 2009
    Posts
    79

    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.

  2. #2
    Join Date
    Apr 2009
    Posts
    86
    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.

  3. #3
    Join Date
    Dec 2009
    Posts
    79
    Thanks SDas!

    As is, it returns 0 records but at least I have something to play around with now.

  4. #4
    Join Date
    Dec 2009
    Posts
    79

    Thumbs up

    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.

  5. #5
    Join Date
    Dec 2009
    Posts
    79
    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. )

  6. #6
    Join Date
    Dec 2009
    Posts
    79
    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?

  7. #7
    Join Date
    Apr 2009
    Posts
    86
    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.

  8. #8
    Join Date
    Dec 2009
    Posts
    79
    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.

  9. #9
    Join Date
    Apr 2009
    Posts
    86
    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

  10. #10
    Join Date
    Dec 2009
    Posts
    79
    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
  •