Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2013
    Posts
    8

    Unanswered: Capture the MIN ClaimID-SQL Server

    I need to capture records with the minimum claim ID when the prov, diag, and the billed are the same, but the claim id and check id are different.
    EXAMPLE:
    CLAIM DOS PROV DIAG BILLED CHECK#
    2001 02/05/11 500 252.5 30.00 35
    2015 02/05/11 500 252.5 30.00 56

    On this example, I would need to capture the record with Claims ID 2001 and exclude the other.

    Any help is greatly appreciated.

    rb

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    DECLARE @t TABLE (
       [CLAIM]      VARCHAR(9)  NOT NULL
    ,  [DOS]        DATE        NOT NULL
    ,  [PROV]       VARCHAR(9)  NOT NULL
    ,  [DIAG]       VARCHAR(9)  NOT NULL
    ,  [BILLED]     MONEY       NOT NULL
    ,  [CHECK#]     VARCHAR(9)  NOT NULL
       )
    
    INSERT INTO @t VALUES
       ('2001', '02/05/11', '500', '252.5', 30.00, '35')
    ,  ('2015', '02/05/11', '500', '252.5', 30.00, '56');
    
    WITH cte AS (SELECT
       [CLAIM],  [DOS],     [PROV]
    ,  [DIAG],   [BILLED],  [CHECK#]
    ,  Row_Number() OVER (
          PARTITION BY [PROV], [DIAG], [BILLED]
          ORDER BY [CLAIM]) AS [PTP]
       FROM @t
    )
    SELECT
       [CLAIM],  [DOS],     [PROV]
    ,  [DIAG],   [BILLED],  [CHECK#]
       FROM cte
       WHERE  1 = [PTP]
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Jul 2013
    Posts
    8
    Hi Pat -
    I tested the code you wrote in SQL Server and got great results, thank you. Now I need to figure out how to plug this code to my existing query. My query has about 20 columns and captures about 3000 records.
    Am I adding the following at the end of the SELECT section? I am a beginner at writing SQL code. Thank you.

    WITH cte AS (SELECT
    [CLAIM], [DOS], [PROV]
    , [DIAG], [BILLED], [CHECK#]
    , Row_Number() OVER (
    PARTITION BY [PROV], [DIAG], [BILLED]
    ORDER BY [CLAIM]) AS [PTP]
    FROM @t
    )
    SELECT
    [CLAIM], [DOS], [PROV]
    , [DIAG], [BILLED], [CHECK#]
    FROM cte
    WHERE 1 = [PTP]

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    You need to bring your columns in to the "cte" part to be able to access them on the outer/lower query.
    George
    Home | Blog

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Without seeing your actual query, the underlying schema, and your data I can't say for certain, but without them I'd say that George has answered your question.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •