Field Update Based on Other Fields

I am trying to identify customers that have what are called 4 line addresses.  A 3 line address is:
Name
Street
City, State Zip
For my purposes, a 4 line would have one additional line (one of the three examples below):
Organization
Name
Street
City, State Zip
    OR
Name
Street1
Street2
City, State Zip
    OR
Organization
Name
Street
Street2
City, State Zip

The bottom line is that I want to check the fields Organization, Name, Street, and Street2 to see if they have something in them.  If one of the fields has something in it, I want it to give me a score of 3 for that customer, if two or more of the fields have something in them, I want to get a score of 4.

I originally thought of how I would do it in Excel and would have something like:
if(Organization is not null,1,0)+if(Name is not null, 1,0)+if(Street is not null,1,0)+if(Street2 is not null, 1,0)
Obviously, this doesn't work in SQL.  Any ideas on what will?

Thanks!  Bart
BartWestphalAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

YiogiCommented:
SELECT CASE WHEN (CASE WHEN (Organization IS NOT NULL) THEN 1 ELSE 0 END
                                    + CASE WHEN (Name is not null) THEN 1 ELSE 0 END
                                    + CASE WHEN (Street is not null) THEN 1 ELSE 0 END
                                    + CASE WHEN (Street2 is not null) THEN 1 ELSE 0 END) > 1 THEN 4
                        ELSE 3
               END
               FROM TABLENAME


0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
BartWestphalAuthor Commented:
Thanks Yiogi.  I was on that track of thought, just stuck on the syntax.  Since I was doing an update, I modified it a little to be:

Update Customers
SET    ADDR_LINES = CASE WHEN (CASE WHEN (CO_NM > '') THEN 1 ELSE 0 END + CASE WHEN (Street1 IS NOT NULL)
                      THEN 1 ELSE 0 END + CASE WHEN (Street2 IS NOT NULL) THEN 1 ELSE 0 END) > 1 THEN 4 ELSE 3 END
0
YiogiCommented:
Whatever suits you, I just gave something to work with from there on :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.