Solved

SQL 2012 not equal Issue

Posted on 2015-01-21
15
51 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 42

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 42

Expert Comment

by:pcelba
ID: 40561861
What about the MaxOfNatlProvID value? It also looks like a NULL.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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 42

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 42

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 42

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 42

Accepted Solution

by:
pcelba earned 500 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 42

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 42

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

627 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