Results 1 to 8 of 8

Thread: join 2 tables

  1. #1
    Join Date
    Nov 2008
    Posts
    3

    Unanswered: join 2 tables

    Hi gurus,

    I have 2 tables --
    Table 1
    CLAIM ID ACTIVITY CODE ACTAMOUNT Denial Code
    55041-0004 12 0 C01
    55041-0004 14653 18 NULL
    55041-0004 10 130 NULL
    100991-0001 76856 230 NULL
    100991-0001 81001 0 A01
    100991-0001 87209 25 P01
    100991-0001 76700 230 NULL


    Table 2 -

    Denial Code Description
    A01 Administrative information missing
    C01 Clinical information Missing
    E01 Eligibility
    P01 Price List related


    THe desired output is ---

    CLAIMID TOTAL AMT REJECTION REASON
    55041-0004 148 Clinical information Missing
    100991-0001 485 Administrative information missing +
    Price List Related.


    Can some one giude me how to do this.When I group comes as separate line.
    Thanks a lot.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10

  3. #3
    Join Date
    Nov 2008
    Posts
    3

    not clear

    George,

    it not very clea to eme.i tried to run the same code as you pasted and the following error messages

    Msg 156, Level 15, State 1, Line 13
    Incorrect syntax near the keyword 'WITH'.
    Msg 170, Level 15, State 1, Line 16
    Line 16: Incorrect syntax near 'Max'.
    Msg 170, Level 15, State 1, Line 23
    Line 23: Incorrect syntax near 'Max'

    that is the ; WITH is it to be modified?
    also Convert(varchar(Max), name) As [name] is it a valid ms sql statement?

    please clear this to me please

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Are you using SQL 2000? The solution George sent you to is for SQL Server 2005+
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Nov 2008
    Posts
    3

    yes its 2000

    well yes it is ms sql 2000

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Here's the sample set up code for anyone else willing to take a stab at it (I've not cracked it with a 2000 solution yet).
    Code:
    DECLARE @table_1 table (
       claim_id      char(15)
     , activity_code int
     , act_amount    int
     , denial_code   char(3)
    )
    
    INSERT INTO @table_1 (claim_id, activity_code, act_amount, denial_code)
          SELECT '55041-0004' , 12   , 0  , 'C01'
    UNION SELECT '55041-0004' , 14653, 18 , NULL
    UNION SELECT '55041-0004' , 10   , 130, NULL
    UNION SELECT '100991-0001', 76856, 230, NULL
    UNION SELECT '100991-0001', 81001, 0  , 'A01'
    UNION SELECT '100991-0001', 87209, 25 , 'P01'
    UNION SELECT '100991-0001', 76700, 230, NULL
    
    DECLARE @table_2 table (
       denial_code char(3)
     , description varchar(35)
    )
    
    INSERT INTO @table_2 (denial_code, description)
          SELECT 'A01', 'Administrative information missing'
    UNION SELECT 'C01', 'Clinical information Missing'
    UNION SELECT 'E01', 'Eligibility'
    UNION SELECT 'P01', 'Price List related'
    
    /** Desired output (2000!)
    +-------------+--------------+----------------------------------------------------------+
    | claim_id    | total_amount | rejection_reason                                         |
    +=============+==============+==========================================================+
    | 55041-0004  | 148          | Clinical information Missing                             |
    | 100991-0001 | 485          | Administrative information missing + Price List Related. |
    +-------------+--------------+----------------------------------------------------------+
    */
    To get the current output:
    Code:
    SELECT t1.claim_id
         , Sum(act_amount) As [total_amount]
         , d.description As [rejection_reason]
    FROM   @table_1 As [t1]
     INNER
      JOIN (
            SELECT t1.claim_id
                 , t2.description
            FROM   @table_1 As [t1]
             INNER
              JOIN @table_2 As [t2]
                ON t1.denial_code = t2.denial_code
            WHERE  t1.denial_code IS NOT NULL
           ) As [d]
        ON t1.claim_id = d.claim_id
    GROUP
        BY t1.claim_id
         , d.description
    George
    Home | Blog

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You need to take this and put into a scalar function that accepts the claim_id as a parameter and returns @s:
    Code:
    DECLARE @table_1 table (
       claim_id      char(15)
     , activity_code int
     , act_amount    int
     , denial_code   char(3)
    )
    
    INSERT INTO @table_1 (claim_id, activity_code, act_amount, denial_code)
          SELECT '55041-0004' , 12   , 0  , 'C01'
    UNION SELECT '55041-0004' , 14653, 18 , NULL
    UNION SELECT '55041-0004' , 10   , 130, NULL
    UNION SELECT '100991-0001', 76856, 230, NULL
    UNION SELECT '100991-0001', 81001, 0  , 'A01'
    UNION SELECT '100991-0001', 87209, 25 , 'P01'
    UNION SELECT '100991-0001', 76700, 230, NULL
    
    DECLARE @table_2 table (
       denial_code char(3)
     , description varchar(35)
    )
    
    INSERT INTO @table_2 (denial_code, description)
          SELECT 'A01', 'Administrative information missing'
    UNION SELECT 'C01', 'Clinical information Missing'
    UNION SELECT 'E01', 'Eligibility'
    UNION SELECT 'P01', 'Price List related'
    
    DECLARE      @s    AS VARCHAR(300)
            , @id    AS CHAR(15)
    
    SELECT    @id    = '100991-0001'
    
    SELECT    @s    = COALESCE(@s + ' + ', '') + description
    FROM    @table_2 AS t2
    INNER JOIN 
            @table_1 AS t1
    ON    t1.denial_code    = t2.denial_code
    WHERE    t1.claim_id    = @id
    
    SELECT    @s   
    
    /** Desired output (2000!)
    +-------------+--------------+----------------------------------------------------------+
    | claim_id    | total_amount | rejection_reason                                         |
    +=============+==============+==========================================================+
    | 55041-0004  | 148          | Clinical information Missing                             |
    | 100991-0001 | 485          | Administrative information missing + Price List Related. |
    +-------------+--------------+----------------------------------------------------------+
    */
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Just a slight modification of your code Poots (nice btw!)

    Code:
        SELECT @s = Coalesce(@s + ' + ', '') + t2.description
        FROM   dbo.table_2 As [t2]
         INNER
          JOIN (
                SELECT DISTINCT denial_code
                FROM   dbo.table_1
                WHERE  claim_id = @claim_id
               ) As [t1]
            ON t1.denial_code = t2.denial_code
    Just in case the same denial code is repeated for a single claim_id
    George
    Home | Blog

Posting Permissions

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