• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 209
  • Last Modified:

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
0
BartWestphal
Asked:
BartWestphal
  • 2
1 Solution
 
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
 
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

Featured Post

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now