Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL 2012 not equal Issue

Posted on 2015-01-21
15
Medium Priority
?
52 Views
Last Modified: 2015-01-28
I am trying to identify changes in addresses between tables. But my query is returning items that already exists in table the first data set should be empty because it exists already in old table you can see it in the second data set.

Here is the code
Select new.PRPR_ID,new.MaxOfNatlProvID,new.AdrType,new.Street,new.city,new.[state],new.ZipCd,new.STD_COUNTY,new.PrimPhone,new.Fax
from #New new 
Where SuppressRow = 0 
and exists (Select top 1 1 from #old arc 
				Where new.PRPR_ID = arc.PRPR_ID
				and new.MaxOfNatlProvID = arc.MaxOfNatlProvID COLLATE DATABASE_DEFAULT
 				and new.AdrType = arc.AdrType COLLATE DATABASE_DEFAULT
				and new.PRPR_ID in (Select PRPR_ID from #old where Getdate() Between StartDate and EndDate)
				and (ltrim(rtrim(new.Street)) COLLATE DATABASE_DEFAULT <> ltrim(rtrim(arc.Street)) COLLATE DATABASE_DEFAULT
					or ltrim(rtrim(new.city)) COLLATE DATABASE_DEFAULT <> ltrim(rtrim(arc.city)) COLLATE DATABASE_DEFAULT
					or ltrim(rtrim(new.[state])) COLLATE DATABASE_DEFAULT<> ltrim(rtrim(arc.[state])) COLLATE DATABASE_DEFAULT
					or ltrim(rtrim(new.ZipCd)) COLLATE DATABASE_DEFAULT <> ltrim(rtrim(arc.ZipCd)) COLLATE DATABASE_DEFAULT
					or ltrim(rtrim(new.STD_COUNTY)) COLLATE DATABASE_DEFAULT <> ltrim(rtrim(arc.STD_COUNTY)) COLLATE DATABASE_DEFAULT
					or ltrim(rtrim(new.PrimPhone)) COLLATE DATABASE_DEFAULT <> ltrim(rtrim(arc.PrimPhone)) COLLATE DATABASE_DEFAULT
					or ltrim(rtrim(new.Fax)) COLLATE DATABASE_DEFAULT <> ltrim(rtrim(arc.Fax)) COLLATE DATABASE_DEFAULT
					)		
	)
and PRPR_ID = '200010803000'
Except
Select old.PRPR_ID,old.MaxOfNatlProvID,old.AdrType,old.Street,old.city,old.[state],old.ZipCd,old.STD_COUNTY,old.PrimPhone,old.Fax
from #old old
where PRPR_ID = '200010803000'
and AdrType = 'PLSV'

Open in new window


Here is a screen shot
does not work

If I have same Query and Use Except it works fine
Except works
0
Comment
Question by:Leo Torres
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 7
15 Comments
 
LVL 43

Expert Comment

by:pcelba
ID: 40561707
What value is in the PrimPhone and Fax? If there is NULL value then you cannot simply compare it but you have to convert it by ISNULL():
or ltrim(rtrim(ISNULL(new.PrimPhone,''))) COLLATE DATABASE_DEFAULT <> ltrim(rtrim(ISNULL(arc.PrimPhone,''))) COLLATE DATABASE_DEFAULT
or ltrim(rtrim(ISNULL(new.Fax,''))) COLLATE DATABASE_DEFAULT <> ltrim(rtrim(ISNULL(arc.Fax,''))) COLLATE DATABASE_DEFAULT

Open in new window

You should apply ISNULL to other nullable columns.
0
 
LVL 8

Author Comment

by:Leo Torres
ID: 40561809
Good point forgot about that..

but same result still comming up.
Select new.PRPR_ID,new.MaxOfNatlProvID,new.AdrType,new.Street,new.city,new.[state],new.ZipCd,new.STD_COUNTY,new.PrimPhone,new.Fax
from #New new 
Where SuppressRow = 0 
and exists (Select top 1 1 from #old arc 
				Where new.PRPR_ID = arc.PRPR_ID
				and new.MaxOfNatlProvID = arc.MaxOfNatlProvID COLLATE DATABASE_DEFAULT
 				and new.AdrType = arc.AdrType COLLATE DATABASE_DEFAULT
				and new.PRPR_ID in (Select PRPR_ID from #old where Getdate() Between StartDate and EndDate)
				and (ltrim(rtrim(isNULL(new.Street,''))) COLLATE DATABASE_DEFAULT <> ltrim(rtrim(isNULL(arc.Street,''))) COLLATE DATABASE_DEFAULT
					or ltrim(rtrim(isNULL(new.city,''))) COLLATE DATABASE_DEFAULT <> ltrim(rtrim(isnull(arc.city,''))) COLLATE DATABASE_DEFAULT
					or ltrim(rtrim(isNULL(new.[state],''))) COLLATE DATABASE_DEFAULT<> ltrim(rtrim(isnull(arc.[state],''))) COLLATE DATABASE_DEFAULT
					or ltrim(rtrim(isNULL(new.ZipCd,''))) COLLATE DATABASE_DEFAULT <> ltrim(rtrim(isnull(arc.ZipCd,''))) COLLATE DATABASE_DEFAULT
					or ltrim(rtrim(isNULL(new.STD_COUNTY,''))) COLLATE DATABASE_DEFAULT <> ltrim(rtrim(isnull(arc.STD_COUNTY,''))) COLLATE DATABASE_DEFAULT
					or ltrim(rtrim(isNULL(new.PrimPhone,''))) COLLATE DATABASE_DEFAULT <> ltrim(rtrim(isnull(arc.PrimPhone,''))) COLLATE DATABASE_DEFAULT
					or ltrim(rtrim(isNULL(new.Fax,''))) COLLATE DATABASE_DEFAULT <> ltrim(rtrim(isNULL(arc.Fax,''))) COLLATE DATABASE_DEFAULT
					)		
	)
and PRPR_ID = '200010803000'

Select old.PRPR_ID,old.MaxOfNatlProvID,old.AdrType,old.Street,old.city,old.[state],old.ZipCd,old.STD_COUNTY,old.PrimPhone,old.Fax,StartDate,EndDate 
from #old old
where PRPR_ID = '200010803000'
and AdrType = 'PLSV'

Open in new window

0
 
LVL 43

Expert Comment

by:pcelba
ID: 40561861
What about the MaxOfNatlProvID value? It also looks like a NULL.
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
LVL 8

Author Comment

by:Leo Torres
ID: 40561903
I add it but same thing

Select new.PRPR_ID,new.MaxOfNatlProvID,new.AdrType,new.Street,new.city,new.[state],new.ZipCd,new.STD_COUNTY,new.PrimPhone,new.Fax
from #New new 
Where SuppressRow = 0 
and exists (Select top 1 1 from #old arc 
				Where new.PRPR_ID = arc.PRPR_ID
				and isNULL(new.MaxOfNatlProvID,'') = isNULL(arc.MaxOfNatlProvID,'') COLLATE DATABASE_DEFAULT
 				and new.AdrType = arc.AdrType COLLATE DATABASE_DEFAULT
				and new.PRPR_ID in (Select PRPR_ID from #old where Getdate() Between StartDate and EndDate)
				and (ltrim(rtrim(isNULL(new.Street,''))) COLLATE DATABASE_DEFAULT <> ltrim(rtrim(isNULL(arc.Street,''))) COLLATE DATABASE_DEFAULT
					or ltrim(rtrim(isNULL(new.city,''))) COLLATE DATABASE_DEFAULT <> ltrim(rtrim(isnull(arc.city,''))) COLLATE DATABASE_DEFAULT
					or ltrim(rtrim(isNULL(new.[state],''))) COLLATE DATABASE_DEFAULT<> ltrim(rtrim(isnull(arc.[state],''))) COLLATE DATABASE_DEFAULT
					or ltrim(rtrim(isNULL(new.ZipCd,''))) COLLATE DATABASE_DEFAULT <> ltrim(rtrim(isnull(arc.ZipCd,''))) COLLATE DATABASE_DEFAULT
					or ltrim(rtrim(isNULL(new.STD_COUNTY,''))) COLLATE DATABASE_DEFAULT <> ltrim(rtrim(isnull(arc.STD_COUNTY,''))) COLLATE DATABASE_DEFAULT
					or ltrim(rtrim(isNULL(new.PrimPhone,''))) COLLATE DATABASE_DEFAULT <> ltrim(rtrim(isnull(arc.PrimPhone,''))) COLLATE DATABASE_DEFAULT
					or ltrim(rtrim(isNULL(new.Fax,''))) COLLATE DATABASE_DEFAULT <> ltrim(rtrim(isNULL(arc.Fax,''))) COLLATE DATABASE_DEFAULT
					)		
	)
and PRPR_ID = '200010803000'

Select old.PRPR_ID,old.MaxOfNatlProvID,old.AdrType,old.Street,old.city,old.[state],old.ZipCd,old.STD_COUNTY,old.PrimPhone,old.Fax,StartDate,EndDate 
from #old old
where PRPR_ID = '200010803000'
and AdrType = 'PLSV'

Open in new window


Here is a hint may this will help. If I comment out these 3 lines it works. If any of these lines are un-commented it does not work
Select new.PRPR_ID,new.MaxOfNatlProvID,new.AdrType,new.Street,new.city,new.[state],new.ZipCd,new.STD_COUNTY,new.PrimPhone,new.Fax
from #New new 
Where SuppressRow = 0 
and exists (Select top 1 1 from #old arc 
				Where new.PRPR_ID = arc.PRPR_ID
				and isNULL(new.MaxOfNatlProvID,'') = isNULL(arc.MaxOfNatlProvID,'') COLLATE DATABASE_DEFAULT
 				and new.AdrType = arc.AdrType COLLATE DATABASE_DEFAULT
				and new.PRPR_ID in (Select PRPR_ID from #old where Getdate() Between StartDate and EndDate)
				and (ltrim(rtrim(isNULL(new.Street,''))) COLLATE DATABASE_DEFAULT <> ltrim(rtrim(isNULL(arc.Street,''))) COLLATE DATABASE_DEFAULT
					--or ltrim(rtrim(isNULL(new.city,''))) COLLATE DATABASE_DEFAULT <> ltrim(rtrim(isnull(arc.city,''))) COLLATE DATABASE_DEFAULT
					or ltrim(rtrim(isNULL(new.[state],''))) COLLATE DATABASE_DEFAULT<> ltrim(rtrim(isnull(arc.[state],''))) COLLATE DATABASE_DEFAULT
					--or ltrim(rtrim(isNULL(new.ZipCd,''))) COLLATE DATABASE_DEFAULT <> ltrim(rtrim(isnull(arc.ZipCd,''))) COLLATE DATABASE_DEFAULT
					--or ltrim(rtrim(isNULL(new.STD_COUNTY,''))) COLLATE DATABASE_DEFAULT <> ltrim(rtrim(isnull(arc.STD_COUNTY,''))) COLLATE DATABASE_DEFAULT
					or ltrim(rtrim(isNULL(new.PrimPhone,''))) COLLATE DATABASE_DEFAULT <> ltrim(rtrim(isnull(arc.PrimPhone,''))) COLLATE DATABASE_DEFAULT
					or ltrim(rtrim(isNULL(new.Fax,''))) COLLATE DATABASE_DEFAULT <> ltrim(rtrim(isNULL(arc.Fax,''))) COLLATE DATABASE_DEFAULT
					)		
	)
and PRPR_ID = '200010803000'

Select old.PRPR_ID,old.MaxOfNatlProvID,old.AdrType,old.Street,old.city,old.[state],old.ZipCd,old.STD_COUNTY,old.PrimPhone,old.Fax,StartDate,EndDate 
from #old old
where PRPR_ID = '200010803000'
and AdrType = 'PLSV'

Open in new window

0
 
LVL 43

Expert Comment

by:pcelba
ID: 40562968
Hmm... It seems I am blind...

Of course, the first query must select the record from the NEW table because there EXISTS both equal and different record in the OLD table. And you are checking just the different record...

Commenting the different columns out then looks as if the query worked correctly which is just a mistake...

So, you should check the equal record in the OLD table and if it exists then don't select the record from the NEW table:
Select new.PRPR_ID,new.MaxOfNatlProvID,new.AdrType,new.Street,new.city,new.[state],new.ZipCd,new.STD_COUNTY,new.PrimPhone,new.Fax
from #New new 
Where SuppressRow = 0 
and NOT exists (Select 1 from #old arc 
				Where new.PRPR_ID = arc.PRPR_ID
				and isNULL(new.MaxOfNatlProvID,'') = isNULL(arc.MaxOfNatlProvID,'') COLLATE DATABASE_DEFAULT
 				and new.AdrType = arc.AdrType COLLATE DATABASE_DEFAULT
				and new.PRPR_ID in (Select PRPR_ID from #old where Getdate() Between StartDate and EndDate)
				and (ltrim(rtrim(isNULL(new.Street,''))) COLLATE DATABASE_DEFAULT = ltrim(rtrim(isNULL(arc.Street,''))) COLLATE DATABASE_DEFAULT
					and ltrim(rtrim(isNULL(new.city,''))) COLLATE DATABASE_DEFAULT = ltrim(rtrim(isnull(arc.city,''))) COLLATE DATABASE_DEFAULT
					and ltrim(rtrim(isNULL(new.[state],''))) COLLATE DATABASE_DEFAULT = ltrim(rtrim(isnull(arc.[state],''))) COLLATE DATABASE_DEFAULT
					and ltrim(rtrim(isNULL(new.ZipCd,''))) COLLATE DATABASE_DEFAULT = ltrim(rtrim(isnull(arc.ZipCd,''))) COLLATE DATABASE_DEFAULT
					and ltrim(rtrim(isNULL(new.STD_COUNTY,''))) COLLATE DATABASE_DEFAULT = ltrim(rtrim(isnull(arc.STD_COUNTY,''))) COLLATE DATABASE_DEFAULT
					and ltrim(rtrim(isNULL(new.PrimPhone,''))) COLLATE DATABASE_DEFAULT = ltrim(rtrim(isnull(arc.PrimPhone,''))) COLLATE DATABASE_DEFAULT
					and ltrim(rtrim(isNULL(new.Fax,''))) COLLATE DATABASE_DEFAULT = ltrim(rtrim(isNULL(arc.Fax,''))) COLLATE DATABASE_DEFAULT
					)		
	)
and PRPR_ID = '200010803000'

Open in new window

0
 
LVL 8

Author Comment

by:Leo Torres
ID: 40563518
Well, sort of. I did this query but this query identifies adds which I have done. The reason I did exists is because I am trying to confirm that the record exists and the operation to do next is an update. I need to know that for the steps going forward. Don't know if that makes sense.  

Second, with the piece of code below wont all fields have to change for a change to be recognized? I am looking for a sample. But the reason behind the "or " was to capture a change in any of the elements.
and (ltrim(rtrim(isNULL(new.Street,''))) COLLATE DATABASE_DEFAULT = ltrim(rtrim(isNULL(arc.Street,''))) COLLATE DATABASE_DEFAULT
					and ltrim(rtrim(isNULL(new.city,''))) COLLATE DATABASE_DEFAULT = ltrim(rtrim(isnull(arc.city,''))) COLLATE DATABASE_DEFAULT
					and ltrim(rtrim(isNULL(new.[state],''))) COLLATE DATABASE_DEFAULT = ltrim(rtrim(isnull(arc.[state],''))) COLLATE DATABASE_DEFAULT
					and ltrim(rtrim(isNULL(new.ZipCd,''))) COLLATE DATABASE_DEFAULT = ltrim(rtrim(isnull(arc.ZipCd,''))) COLLATE DATABASE_DEFAULT
					and ltrim(rtrim(isNULL(new.STD_COUNTY,''))) COLLATE DATABASE_DEFAULT = ltrim(rtrim(isnull(arc.STD_COUNTY,''))) COLLATE DATABASE_DEFAULT
					and ltrim(rtrim(isNULL(new.PrimPhone,''))) COLLATE DATABASE_DEFAULT = ltrim(rtrim(isnull(arc.PrimPhone,''))) COLLATE DATABASE_DEFAULT
					and ltrim(rtrim(isNULL(new.Fax,''))) COLLATE DATABASE_DEFAULT = ltrim(rtrim(isNULL(arc.Fax,''))) COLLATE DATABASE_DEFAULT
					)	

Open in new window

0
 
LVL 43

Expert Comment

by:pcelba
ID: 40563957
Did you notice the NOT EXISTS in my answer?

OK, probably yes.

If you want to update some record then you may do it directly and update the whole record even when just one field is changed. The WHERE part having AND ... AND ... AND inside is ideal to recognize this one changed field because it evaluates to False if just one letter is different in any part of the address.

And now back to your original question - What would you like to do if you have two records in the OLD table and just one record in the NEW table? Which one is correct? What should happen to the incorrect one?

You've requested the query to work as if you used EXCEPT, so the reversed logic from EXISTS ... OR ... OR ... OR to  NOT EXISTS ... AND ... AND ... AND did the trick.
0
 
LVL 8

Author Comment

by:Leo Torres
ID: 40564421
This process is relatively new if there are 2 in the old and a new that replaced both then yes both have to be identified for update. These should be rewarded and far between but I have seen them myself.
0
 
LVL 43

Expert Comment

by:pcelba
ID: 40564899
I don't understand now...

One more question:
How do you recognize which address in the OLD file belongs to the address in the NEW file?

I can see PRPR_ID and AdrType columns but this does not fit all your needs probably.

You should also check the date range... What meaning has StartDate and EndDate in the OLD file?
0
 
LVL 8

Author Comment

by:Leo Torres
ID: 40567304
a PRPR_ID records could have a PLSV type or a Bill Type or a Combo type which means its both a PLSV and Bill.

The start and ends date relate to when PRPR-ID changed once there is a change the old record gets an End Date with yesterdays date and a new records is created with todays date and infinite termdate until a change happens. This design allows for there to be a historical record of what changes happen to a provider.

PLSV= Place of service
Bill = BIlling address
Combo = A place of service is also the billing address.

These are the only 3 possible type for AdrType.

I need to maintain a list of what is current for billing purpose and maintain historical values as well for audit purpose.

Hope that sheds some light on the subject.
Thanks for your help!
0
 
LVL 43

Accepted Solution

by:
pcelba earned 2000 total points
ID: 40567360
OK, thanks for the explanation.

This means you have to omit "archive records" when comparing NEW record against the OLD table. The comparison against the current record in the OLD table then must show the truth.
0
 
LVL 8

Author Comment

by:Leo Torres
ID: 40568361
Yes, you can also have duplicate address but each address is unique by address and AdrType
0
 
LVL 43

Expert Comment

by:pcelba
ID: 40569058
OK, the AdrType is involved in the query already but the Start/End date is still nowhere.
0
 
LVL 8

Author Comment

by:Leo Torres
ID: 40571270
added a seprate add before your query this may work.
Update n
Set ChangeType = 'ADD'--Select *
from new n
Where SuppressRow = 0 
and NOT exists (Select top 1 1 from old arc
				Where new.PRPR_ID = arc.PRPR_ID
				and new.AdrType = arc.AdrType
				)

Open in new window

0
 
LVL 43

Expert Comment

by:pcelba
ID: 40571312
Yes, the additional ChangeType will work for records which are really NEW i.e. not present in the OLD table.

To solve existing (but changed) records update you have to compare all columns as you did in previous queries.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question