Author |
Topic |
wsilage
Yak Posting Veteran
82 Posts |
Posted - 2014-04-17 : 09:07:08
|
I have this query that I have been using to find duplicate records works great except for now. I had to add to logic to this.The logic I am adding is pcs_rreas <> 'NG'. When I add this it does take out the NG, but it also excludes the reocords that have NULL data in this field. I don't want that to happen. How can I fix this? I tried adding (pcs_rreas <> 'NG' or pcs_rreas is null) but nothing is pulling now.Any ideas would be great. Thanks.SELECT pcs_id1, pcs_rreas, pcs_lname, pcs_fname, pcs_minit, pcs_degree, pcs_xtyp, pcs_office, pcs_dba, pcs_dob, pcs_sex, pcs_eff, pcs_trm, pcs_spec1, pcs_spec2, pcs_spec3, pcs_spec4, pcs_tax1, pcs_ssn, pcs_altid, pcs_upin, pcs_medic, pcs_mcaid, pcs_ecs, pcs_npi, pcs_status, pcs_dir, pcs_den, pcs_www, pcs_hold, pcs_email, pcs_misc1, pcs_misc2, pcs_newdt, pcs_newby, pcs_chgdt, pcs_chgby, pcs_malp, pcs_pf, pcs_ctl, pcs_sys, pcs_pay, pcs_ann, pcs_bcert, pcs_pass, pcs_w9, pcs_taxex, pcs_tax2, pcs_type, pcs_rreas, pcs_routo, pcs_rtime, pcs_rdate, pcs_force, pcs_flag, pcs_v419, pcs_bcity, pcs_bstate, pcs_bcntry, pcs_citizn, pcs_esnim, pcs_dclim, pcs_fill1, pcs_caniv, pcs_ctype, pcs_crost, pcs_crsta, pcs_nonr, pcs_pstate, pcs_sameFROM pcsWHERE (pcs_ssn IN (SELECT pcs_ssn FROM IMPACT.dbo.pcs AS pcs_1 WHERE (pcs_rreas <> 'NG' or pcs_rreas is null) and (pcs_spec1 <> 'HSP') AND (pcs_spec1 <> 'PTP') AND (pcs_degree <> 'HOSP') AND (pcs_spec1 <> 'LOC') AND (pcs_degree <> 'ANC') AND (pcs_ctl <> 'X') GROUP BY pcs_ssn HAVING (COUNT(pcs_ssn) > 1 ))) AND (pcs_degree <> 'ANC') AND (pcs_degree <> 'HOSP') ORDER BY pcs_ssn |
|
Robowski
Posting Yak Master
101 Posts |
Posted - 2014-04-17 : 09:20:55
|
quote: Originally posted by wsilage I have this query that I have been using to find duplicate records works great except for now. I had to add to logic to this.The logic I am adding is pcs_rreas <> 'NG'. When I add this it does take out the NG, but it also excludes the reocords that have NULL data in this field. I don't want that to happen. How can I fix this? I tried adding (pcs_rreas <> 'NG' or pcs_rreas is null) but nothing is pulling now.Any ideas would be great. Thanks.SELECT pcs_id1, pcs_rreas, pcs_lname, pcs_fname, pcs_minit, pcs_degree, pcs_xtyp, pcs_office, pcs_dba, pcs_dob, pcs_sex, pcs_eff, pcs_trm, pcs_spec1, pcs_spec2, pcs_spec3, pcs_spec4, pcs_tax1, pcs_ssn, pcs_altid, pcs_upin, pcs_medic, pcs_mcaid, pcs_ecs, pcs_npi, pcs_status, pcs_dir, pcs_den, pcs_www, pcs_hold, pcs_email, pcs_misc1, pcs_misc2, pcs_newdt, pcs_newby, pcs_chgdt, pcs_chgby, pcs_malp, pcs_pf, pcs_ctl, pcs_sys, pcs_pay, pcs_ann, pcs_bcert, pcs_pass, pcs_w9, pcs_taxex, pcs_tax2, pcs_type, pcs_rreas, pcs_routo, pcs_rtime, pcs_rdate, pcs_force, pcs_flag, pcs_v419, pcs_bcity, pcs_bstate, pcs_bcntry, pcs_citizn, pcs_esnim, pcs_dclim, pcs_fill1, pcs_caniv, pcs_ctype, pcs_crost, pcs_crsta, pcs_nonr, pcs_pstate, pcs_sameFROM pcsWHERE (pcs_ssn IN (SELECT pcs_ssn FROM IMPACT.dbo.pcs AS pcs_1 WHERE (pcs_rreas <> 'NG' or pcs_rreas is null) and (pcs_spec1 <> 'HSP') AND (pcs_spec1 <> 'PTP') AND (pcs_degree <> 'HOSP') AND (pcs_spec1 <> 'LOC') AND (pcs_degree <> 'ANC') AND (pcs_ctl <> 'X') GROUP BY pcs_ssn HAVING (COUNT(pcs_ssn) > 1 ))) AND (pcs_degree <> 'ANC') AND (pcs_degree <> 'HOSP') ORDER BY pcs_ssn
SELECT pcs_id1, pcs_rreas, pcs_lname, pcs_fname, pcs_minit, pcs_degree, pcs_xtyp, pcs_office, pcs_dba, pcs_dob, pcs_sex, pcs_eff, pcs_trm, pcs_spec1, pcs_spec2, pcs_spec3, pcs_spec4, pcs_tax1, pcs_ssn, pcs_altid, pcs_upin, pcs_medic, pcs_mcaid, pcs_ecs, pcs_npi, pcs_status, pcs_dir, pcs_den, pcs_www, pcs_hold, pcs_email, pcs_misc1, pcs_misc2, pcs_newdt, pcs_newby, pcs_chgdt, pcs_chgby, pcs_malp, pcs_pf, pcs_ctl, pcs_sys, pcs_pay, pcs_ann, pcs_bcert, pcs_pass, pcs_w9, pcs_taxex, pcs_tax2, pcs_type, pcs_rreas, pcs_routo, pcs_rtime, pcs_rdate, pcs_force, pcs_flag, pcs_v419, pcs_bcity, pcs_bstate, pcs_bcntry, pcs_citizn, pcs_esnim, pcs_dclim, pcs_fill1, pcs_caniv, pcs_ctype, pcs_crost, pcs_crsta, pcs_nonr, pcs_pstate, pcs_sameFROM pcsWHERE (pcs_ssn IN(SELECT pcs_ssnFROM IMPACT.dbo.pcs AS pcs_1WHERE (isnull(pcs_rreas, 'xyz') <> 'NG') and (pcs_spec1 <> 'HSP') AND (pcs_spec1 <> 'PTP') AND (pcs_degree <> 'HOSP') AND (pcs_spec1 <> 'LOC') AND (pcs_degree <> 'ANC') AND (pcs_ctl <> 'X')GROUP BY pcs_ssnHAVING (COUNT(pcs_ssn) > 1 ))) AND (pcs_degree <> 'ANC') AND (pcs_degree <> 'HOSP') ORDER BY pcs_ssn |
|
|
wsilage
Yak Posting Veteran
82 Posts |
Posted - 2014-04-17 : 10:51:09
|
So this does work but I had to relocate my (isnull(pcs_rreas, 'xyz') <> 'NG') in my query because it didn't work the other way. Now my only problem is that there is one that is still on the report that shouldn't be Name SocialJohn Smith 123456789The NG record is out, but this one should be taken off too because it had the same Social as the NG provider.SELECT pcs_id1, pcs_rreas, pcs_lname, pcs_fname, pcs_minit, pcs_degree, pcs_xtyp, pcs_office, pcs_dba, pcs_dob, pcs_sex, pcs_eff, pcs_trm, pcs_spec1, pcs_spec2, pcs_spec3, pcs_spec4, pcs_tax1, pcs_ssn, pcs_altid, pcs_upin, pcs_medic, pcs_mcaid, pcs_ecs, pcs_npi, pcs_status, pcs_dir, pcs_den, pcs_www, pcs_hold, pcs_email, pcs_misc1, pcs_misc2, pcs_newdt, pcs_newby, pcs_chgdt, pcs_chgby, pcs_malp, pcs_pf, pcs_ctl, pcs_sys, pcs_pay, pcs_ann, pcs_bcert, pcs_pass, pcs_w9, pcs_taxex, pcs_tax2, pcs_type, pcs_rreas, pcs_routo, pcs_rtime, pcs_rdate, pcs_force, pcs_flag, pcs_v419, pcs_bcity, pcs_bstate, pcs_bcntry, pcs_citizn, pcs_esnim, pcs_dclim, pcs_fill1, pcs_caniv, pcs_ctype, pcs_crost, pcs_crsta, pcs_nonr, pcs_pstate, pcs_sameFROM impact.dbo.pcsWHERE (pcs_ssn IN(SELECT pcs_ssnFROM IMPACT.dbo.pcs AS pcs_1WHERE (pcs_spec1 <> 'HSP') AND (pcs_spec1 <> 'PTP') AND (pcs_degree <> 'HOSP') AND (pcs_spec1 <> 'LOC') AND (pcs_degree <> 'ANC') AND (pcs_ctl <> 'X')GROUP BY pcs_ssnHAVING (COUNT(pcs_ssn) > 1 ))) AND (pcs_degree <> 'ANC') AND (pcs_degree <> 'HOSP') and (isnull(pcs_rreas, 'xyz') <> 'NG') ORDER BY pcs_ssn |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2014-04-17 : 12:18:45
|
This is probably a good time to ask for sample data and expected output. The problem with your query is that you are filtering out rows using your predicate (WHERE clause) so those rows aren't considered in the GROUP BY, thus why you are not excluding "John Smith 123456789." Since the query is flawed, it can't really be used as a specification for what you want. So, my suggestion would be to explain in words what you want the logic to be. Supply sample data that we can run queries against. Show us the expected output/result given the sample data. Here are some links if you are unfamiliar with how to provide that:http://www.sqlservercentral.com/articles/Best+Practices/61537/http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx |
|
|
|
|
|