April 29, 2016 at 12:47 pm
Trying to exclude my dups any ideas?
16091E00078 QMXM00000164709Jaso, Rachel E2016-04-30 00:00:001111182 230.30
16091E00074 QMXM00000159830Sullivan, Michael J2016-04-30 00:00:001116023 218.78
16055E01323 QMXM00000163831Hensley, Danny K2016-04-30 00:00:002226005 137.24
16055E01323 QMXM00000163831Hensley, Danny K2016-04-30 00:00:002226005 137.24
16063E00182 QMXM00000163831Hensley, Danny K2016-04-30 00:00:002226005 137.24
16095E00445 QMXM00000169093Cervantes, Josefina 2016-04-30 00:00:002229817 98.46
16095E00445 QMXM00000169093Cervantes, Josefina 2016-04-30 00:00:002229817 98.46
16095E00445 QMXM00000169093Cervantes, Josefina 2016-04-30 00:00:002229817 98.46
Select
claimdetail.claimid As [Claim ID],
member.memid As [Member ID],
member.fullname As [Member Name],
(Select
Max(paycheck.checkprintdate)
From
paycheck) As [Check Date],
paycheck.checknbr As [Check No],
paycheck.checkamt As [Check Amt],
provider.npi As [Blank Identifyer]
From
payvoucher Inner Join
member
On payvoucher.memberid = member.memid Inner Join
paycheck
On paycheck.mempayeeid = member.memid Inner Join
claimdetail
On claimdetail.claimid = payvoucher.claimid Inner Join
provider
On claimdetail.renderingprovid = provider.provid
Where
claimdetail.claimid = payvoucher.claimid And
paycheck.checknbr Not In ('NULL', ' ') And
member.memid Not In ('NULL', ' ')and
provider.npi in ('Null', ' ')
Order By
[Check Amt] Desc
April 29, 2016 at 1:01 pm
Review which table is causing the dupes and remove them there before doing the join. In other words, use a subquery or temp table if necessary.
Check Date is the same for all rows because it's not correlated to the main query.
April 29, 2016 at 1:12 pm
There are no Dups in the original database as all of these are considered Key Values,
how would you begin to eliminate the dubs if they are key values.
Luis Cazares (4/29/2016)
Review which table is causing the dupes and remove them there before doing the join. In other words, use a subquery or temp table if necessary.Check Date is the same for all rows because it's not correlated to the main query.
April 29, 2016 at 1:24 pm
You need to comment all tables and the uncomment each table at a time. Or go the other way, comment one table at a time and see which one would prevent duplicates when commented.
All can be considered key values, but I'm guessing that your tables relationships are not 1-to-1 but 1-to-many.
My guess is that the claim detail table is creating the duplicates.
May 2, 2016 at 5:16 am
SilverBack (4/29/2016)
There are no Dups in the original database as all of these are considered Key Values,how would you begin to eliminate the dubs if they are key values.
Luis Cazares (4/29/2016)
Review which table is causing the dupes and remove them there before doing the join. In other words, use a subquery or temp table if necessary.Check Date is the same for all rows because it's not correlated to the main query.
My first question is are you sure the data is truly duplicated and not simply new data that doesn't have enough attributes to differentiate itself between entries?
Under the assumption that you do indeed have duplicated data, here's some pseudo code I use to pull out the dups. Depends on whether you want the minimum entry or the max entry.
SELECT t1.MyID, t1.Column1, t1.Column2
FROM Table t1
INNER JOIN (SELECT MIN(MyID) AS MyID, Column1
FROM Table
GROUP BY Column1) t2
ON t1.Column1 = t2.Column1
AND t1.MyID = t2.MyID
This helps me get the distinct record and the key value from the table in question. So for your Member table, you'd do a MIN or MAX on memid and use fullname where I have Column1.
Does that help?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply