Results 1 to 11 of 11

Thread: Record Counter

  1. #1
    Join Date
    Jun 2004
    Posts
    57

    Unanswered: Record Counter

    I need to add a ClaimCount column to a table. I am having a bit of trouble with the SQL to make sure it populates correctly.

    Current Table

    claim# name Paid
    123 George Washington 50
    123 George W Washingon 50
    124 Sam Adams 100
    126 George Washington 75
    128 Rich Cheney 69
    128 Dick Cheney 69
    128 Richard CHeney 69



    Want to add a column (ClaimCount) as follows

    Claim# name Paid ClaimCount
    123 George Washington 50 1
    123 George W Washingon 50 2
    124 Sam Adams 100 1
    126 George Washington 75 1
    128 Rich Cheney 69 1
    128 Dick Cheney 69 2
    128 Richard CHeney 69 3

    Rules for ClaimCount.
    1) there is no order by, It doesn't matter which record with the same Claim# is 1 or 2 or 3...

    Appreciate the help.

    Ray

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    By definition, the count does not belong to a particular row, but to the set of data...and if you still need to do this, and there is no order by, it is totally arbitrary....

    Use a Cursor
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jun 2004
    Posts
    57
    Brett.
    Thanks. Cursor it is then, but I don't know how to do one.

    Let me back up a step. Here's the problem we're trying to solve.

    In healthcare, claims that get paid wrong, get 'adjusted'. For complicated reasons associated with ETL logic, the claims often end up in tables twice, once with the field as originally paid 'George Washington' and then again 'George W Washington'. Claims have upwards of 100 fields -- any one of which can change. I don't care which record we get, (with or without the 'W') -- just that we end up with one record per claim number.

    The amature SQL translation, i want one record for each distinct claim#, and I don't care what is in the other fields...

    Regards

    Ray

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Well the first thing to do is identify the population of duplicates

    SELECT Claim# FROM table GROUP BY Claim# HAVING COUNT(*) > 1

    you don't need a sequence #, what would you use it for?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Jun 2004
    Posts
    57
    Brett.
    You got it. My crappy SQL 'skills' notwithstanding. I can find them.

    I was going to use the ClaimCount to weed out the dupes -- SELECT *
    FROM tblClaim WHERE ClaimCount = 1 and goodbye dupes (any records where claimcount = 2,3,4...).

    Ray

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Nah

    Identify them

    create a temp table of the dups creating a single row per id, use scalar function to "pick" one of the values

    Delete the dupes

    insert the data back in

    Put a unique key on the claim# so this doesn't happen again

    Do you need the code?

    Read the sticky at the top of the board to help us out
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Jun 2004
    Posts
    57
    Brett.
    Thanks. Read Sticky. Will do better job with question next time.

    The SQL would help, in particular I have a question about -- use scalar function to "pick" one of the values. I'm not sure what that means.

    As far as a unique key and ongoing future reoccurences, There needs to be new logic added to the ETL steps that populate the tables to screen the adjusted 'dupe' claims at that point. i'm ok figuring that out later. The near term 'ugly' fix is to create a separate table and 'crude' logic to remove the dupes in a new table (not tblClaim).

    Regards

    Ray

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Got the DDL for the table?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Jun 2004
    Posts
    57
    Table DLL attached.

  10. #10
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    yeah I work on some healthcare billing \claims software too and I will tell you this. You do not want to delete the bad data. auditors get upset by broken sequences of numbers. i hate this crap most days.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  11. #11
    Join Date
    Jan 2005
    Location
    Green Bay
    Posts
    201
    I work in healthcare also kind of fun isn't it

    What is you city of headquarters because if it the same as mine I can help wtih this development offline.

Posting Permissions

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