Solved

SQL 2012 not equal Issue

Posted on 2015-01-21
15
45 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
  • 8
  • 7
15 Comments
 
LVL 41

Expert Comment

by:pcelba
Comment Utility
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
Comment Utility
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 41

Expert Comment

by:pcelba
Comment Utility
What about the MaxOfNatlProvID value? It also looks like a NULL.
0
 
LVL 8

Author Comment

by:Leo Torres
Comment Utility
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 41

Expert Comment

by:pcelba
Comment Utility
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
Comment Utility
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 41

Expert Comment

by:pcelba
Comment Utility
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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 8

Author Comment

by:Leo Torres
Comment Utility
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 41

Expert Comment

by:pcelba
Comment Utility
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
Comment Utility
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 41

Accepted Solution

by:
pcelba earned 500 total points
Comment Utility
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
Comment Utility
Yes, you can also have duplicate address but each address is unique by address and AdrType
0
 
LVL 41

Expert Comment

by:pcelba
Comment Utility
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
Comment Utility
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 41

Expert Comment

by:pcelba
Comment Utility
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Creating and Managing Databases with phpMyAdmin in cPanel.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

762 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now