Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Simple Query Question

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_same
FROM pcs
WHERE (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_same
FROM pcs
WHERE (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_same
FROM pcs
WHERE (pcs_ssn IN
(SELECT pcs_ssn
FROM IMPACT.dbo.pcs AS pcs_1
WHERE (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_ssn
HAVING (COUNT(pcs_ssn) > 1 ))) AND (pcs_degree <> 'ANC') AND (pcs_degree <> 'HOSP')
ORDER BY pcs_ssn
Go to Top of Page

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 Social
John Smith 123456789

The 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_same
FROM impact.dbo.pcs
WHERE (pcs_ssn IN
(SELECT pcs_ssn
FROM IMPACT.dbo.pcs AS pcs_1
WHERE
(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') and (isnull(pcs_rreas, 'xyz') <> 'NG')
ORDER BY pcs_ssn
Go to Top of Page

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
Go to Top of Page
   

- Advertisement -