Below is a copy of my query; the problem is that certain individuals have ZIP codes for both mailing address (MAIL) and residence address (RESI). The default is address is their RESI address although if they have a MAIL address then that is the one to be used. So I want only one row for each person but I need it to show MAIL address if MAIL is evident and not their RESI.
So what I see currently is:
What I would like to see is:
I have pondered ways to do this and have googled quite a bit but being rather new to SQL I can not for the life of me figure it out. This community seems pretty helpful so thought I would give it a shot. I was thinking an IF statement but again I could not figure out a way to do this.
Any help would be greatly appreciated!
Ok, I took your example data and output to come up with something that seems to be correct.
The major work here really is using the windowing function
Please not that this doesn't account for cases where an INDVID has more than one MAIL address type/ You will need to consider this yourself.
My code returns the expected output from your question and seems to be the best way of doing what you want.
You would swap out my test code with the real select you supplied and then you can test if it works right.
answered Aug 08, 2012 at 12:57 PM
On SQL Server 2005+ you can achieve this using Sub Query with ROW_NUMBER furntion as in example below.
In the Partition BY part of the ROW_NUMBER function you have to specify all the columns, which uniquey identify the single person.