[dennisdj]

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.

[r937]

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?

[dennisdj]

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;

[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

[dennisdj]

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;

[dennisdj]

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

[dennisdj]

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;

[dennisdj]

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.

[r937]

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

[dennisdj]

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?

[r937]

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

[dennisdj]

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?

[r937]

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?

[dennisdj]

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 |

[r937]

so for the 4 Tom Smith rows, you want to see them all?