Page 1 of 3 123 Last
  • Jump to page:
    #1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    26
    Rep Power
    0

    Query to determine duplicates


    I am trying to write a query to find duplicates and I have been unsuccessful. I need to query records where if patient_last_name, patient_first_name, proc_code, and DOS are equal AND all the status of all who meet the that criteria have a status equal to "denied" AND none of these have a approved_amount greater than zero. I have tried so many variations and have been unsuccessful. It should be simple code but I don't know if it is logical possible with one table. Any suggestions?. Have included some test data since I am working with PHI information.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,206
    Rep Power
    4279
    Originally Posted by dennisdj
    I have tried so many variations and have been unsuccessful.
    could we, you know, have a peek at one of these attempts?

    Comments on this post

    • dennisdj agrees
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    26
    Rep Power
    0
    Originally Posted by r937
    could we, you know, have a peek at one of these attempts?
    This is one but it returns all the fields that have amount of zero. If the amount is greater than zero but it meets the other criteria I don't want to see any of those claims. Only the ones who have any approved amounts. Some may have been billed multiple times and finally received payment. If this is the case, I want all the attempts left out as well.

    SELECT patient_last_name, patient_first_name, proc_code, DOS, approved_amt
    FROM Claim_base_table
    WHERE
    patient_last_name = patient_last_name AND
    patient_first_name = patient_first_name AND
    proc_code = proc_code AND
    DOS =DOS AND
    approved_amt = 0;
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,206
    Rep Power
    4279
    the problem with that query is that your WHERE conditions apply to each row separately

    so when you say patient_last_name = patient_last_name you are just comparing a value to itself, and that's going to evaluate as TRUE for all rows

    you need to compare multiple rows together, and for this, you need GROUP BY

    so you would group the rows based on all the columns that are pertinent to duplication, and then see whether the COUNT(*) of these rows is more than 1
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    26
    Rep Power
    0

    Almost there


    Ok I got to show the information once but I can get it to work with the last criteria of if none of these have an paid_amount = 0.

    This is what I got so far:

    SELECT A.patient_last_name, A.patient_first_name, A.proc_code, A.DOS
    FROM Claim_base_table A
    INNER JOIN Claim_base_table B
    ON A.patient_last_name = B.patient_last_name AND
    A.patient_first_name = B.patient_first_name AND
    A.proc_code = B.proc_code AND
    A.DOS =B.DOS
    GROUP BY A.patient_last_name, A.patient_first_name, A.proc_code, A.DOS;
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    26
    Rep Power
    0
    I tried this but it returns the same information as the one I just posted above:

    SELECT A.patient_last_name, A.patient_first_name, A.proc_code, A.DOS
    FROM Claim_base_table A
    INNER JOIN Claim_base_table B
    ON A.patient_last_name = B.patient_last_name AND
    A.patient_first_name = B.patient_first_name AND
    A.proc_code = B.proc_code AND
    A.DOS =B.DOS
    WHERE
    A.approved_amt= 0 AND B.approved_amt = 0
    GROUP BY A.patient_last_name, A.patient_first_name, A.proc_code, A.DOS;

    Test data is:

    Doe | Tom | Therapy | 10/21/2012 | 0.00
    Doe | Tom | Therapy | 10/21/2012 | 0.00
    Doe | Jane | Therapy | 1/1/2012 | 0.00
    Doe | Jane | Therapy | 1/1/2012 | 0.00
    Doe | Jane | Therapy | 1/1/2012 | 90.00

    In this case it should just show the first line and none of the rest but I am getting

    Doe |Tom | Therapy | 10/21/2012 | 0.00
    Doe | Jane | Therapy | 1/1/2012 | 0.00

    when I should be getting
    Doe |Tom | Therapy | 10/21/2012 | 0.00
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    26
    Rep Power
    0

    I got it... I think


    Originally Posted by r937
    the problem with that query is that your WHERE conditions apply to each row separately

    so when you say patient_last_name = patient_last_name you are just comparing a value to itself, and that's going to evaluate as TRUE for all rows

    you need to compare multiple rows together, and for this, you need GROUP BY

    so you would group the rows based on all the columns that are pertinent to duplication, and then see whether the COUNT(*) of these rows is more than 1
    I did this and it appeared to work but I am worried on a broader data set it may not. Do you think this would achieve what I am trying to do?

    SELECT
    A.patient_last_name, A.patient_first_name, A.proc_code, A.DOS
    FROM
    Claim_base_table A
    INNER JOIN
    Claim_base_table B
    ON
    A.patient_last_name = B.patient_last_name AND
    A.patient_first_name = B.patient_first_name AND
    A.proc_code = B.proc_code AND
    A.DOS =B.DOS
    GROUP BY
    A.patient_last_name, A.patient_first_name, A.proc_code, A.DOS
    HAVING
    SUM (A.approved_amt) = 0;
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    26
    Rep Power
    0
    Ok. It was working and I started on another query using this one and the inital table to show all the field I need and it is now returning all fields instead of the one row I need.
  16. #9
  17. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,206
    Rep Power
    4279
    Code:
    SELECT t.patient_last_name
         , t.patient_first_name
         , t.proc_code
         , t.DOS
         , t.any_other_columns
      FROM ( SELECT patient_last_name
                  , patient_first_name
                  , proc_code
                  , DOS
               FROM Claim_base_table 
              WHERE approved_amt = 0
             GROUP
                 BY patient_last_name
                  , patient_first_name
                  , proc_code
                  , DOS
             HAVING COUNT(*) > 1 ) AS dupes
    INNER
      JOIN Claim_base_table AS t
        ON t.patient_last_name  = dupes.patient_last_name    
       AND t.patient_first_name = dupes.patient_first_name 
       AND t.proc_code          = dupes.proc_code          
       AND t.DOS                = dupes.DOS

    Comments on this post

    • dennisdj agrees
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  18. #10
  19. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    26
    Rep Power
    0
    Thanks for your help but the code above you gave still gives duplicates and shows some approved claims as well. Any additional assistance you can give?
  20. #11
  21. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,206
    Rep Power
    4279
    Originally Posted by dennisdj
    Thanks for your help but the code above you gave still gives duplicates
    excuse me, but i thought you said in the first post --
    I am trying to write a query to find duplicates
    the query i gave you finds duplicates based on those 4 columns, then shows all rows that have the duplicate columns
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  22. #12
  23. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    26
    Rep Power
    0
    Originally Posted by r937
    excuse me, but i thought you said in the first post --the query i gave you finds duplicates based on those 4 columns, then shows all rows that have the duplicate columns
    That statement was part of a longer post and I went on to explain exactly what I was trying to accomplish. I need to find the duplicates to filter out the ones that have been paid and only return a true claim that has been denied but I only want to see one. The claims have been billed so many times I can't give a complete picture of what is truly outstanding. Any help?
  24. #13
  25. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,206
    Rep Power
    4279
    Originally Posted by dennisdj
    I need to find the duplicates to filter out the ones that have been paid and only return a true claim that has been denied but I only want to see one.
    i'm afraid you lost me with this

    care to try to translate it into columns?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  26. #14
  27. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    26
    Rep Power
    0
    after I added the claim_amt and approved_amt this is what I am getting after running the suggested query:

    |pat..t_last_name|p...t...name|Proc_code|DOS |approved_amt|
    Doe | Joe |99214 |3/5/2013| 0.00 |
    Doe | Joe |99214 |3/5/2013| 0.00 |
    Doe | Joe |99214 |3/5/2013| 0.00 |
    Smith | Tom |90837 |6/10/2013| 0.00 |
    Smith |Tom |90837 |6/10/2013| 0.00 |
    Smith | Tom |90837 |6/10/2013| 74.57 |
    Smith | Tom |90837 |6/10/2013| 0.00 |

    I need it to show only a row if the approved amount is 0.00 and the is not another one with the same name code and date with amount greater than 0.00 in the approved amount and only show one. It should only show this if the data from above was used:
    Doe | Joe |99214 |3/5/2013| 0.00 |
  28. #15
  29. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,206
    Rep Power
    4279


    so for the 4 Tom Smith rows, you want to see them all?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
Page 1 of 3 123 Last
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo