November 13th, 2013, 09:31 AM
-
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.
November 13th, 2013, 09:54 AM
-
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
November 13th, 2013, 11:07 AM
-
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;
November 13th, 2013, 12:12 PM
-
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
November 13th, 2013, 01:08 PM
-
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;
November 13th, 2013, 01:17 PM
-
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
November 13th, 2013, 02:22 PM
-
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;
November 13th, 2013, 03:37 PM
-
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.
November 14th, 2013, 08:20 AM
-
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
November 18th, 2013, 09:03 AM
-
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?
November 18th, 2013, 09:07 AM
-
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
November 18th, 2013, 10:20 AM
-
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?
November 18th, 2013, 11:53 AM
-
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?
November 18th, 2013, 12:12 PM
-
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 |
November 18th, 2013, 06:05 PM
-
so for the 4 Tom Smith rows, you want to see them all?