Solved

Identify more than 1 person at same address and assign family id

Posted on 2013-11-05
9
328 Views
Last Modified: 2013-11-06
The table contains:
 
unique student id
 lastname
 firstname
 streetaddress
motheremail

I need to create family ids where 1 or more students live at the same address (streetaddress) and have the same motheremail.

For example, there are 3 students with the same last name living at 111 Main Street.
Once I identify them, I need to assign them a familyid such as 145-1

There are also 3 students who do not have the same last name but live at 121 Spruce Street. I still need to assign them a familyid such as 145-2.

Familyid is not in the table, but I could add it as a column, if need be.

I added some sample data in the spreadsheet.
Thank you for your help!
EESampleDataFamilyID.xls
0
Comment
Question by:lbarnett419
  • 4
  • 3
  • 2
9 Comments
 

Author Comment

by:lbarnett419
Comment Utility
Oops, forgot!
I need to assign every student a family id. Therefore if 3 students live at the same address, they all have the same familyid.
If one student lives at an address, he/she needs to be assigned a family id.
Thanks
0
 
LVL 23

Assisted Solution

by:David
David earned 20 total points
Comment Utility
First, obviously, this reads like a school assignment.

Secondly, I'd counsel you to take a step back, and gather your rules / business requirements to some simpler layout.  Can an address exist in the system if no one currently lives there?  You imply a need for a location id (145 above) rather than family, as you use "family" in both related, and unrelated, terms.

Can a person belong to more than one location, and/or more than one family?  Student John lives at one address with everyone else in his/her dormitory; and also lives with his/her parents across town on weekends. S/he receives postal mail at both locations.

How do you differentiate between related family and unrelated family?  Family Smith are Student John's parents; they rent a room to another student, Smith.  And of course, there are multiple Smiths (even John Smiths) at the dorm.  Moreover, Smith might do a co-op out of state for one term, and upon return, move into an apartment.

IOW both the family surname, and the street address, cannot uniquely differentiate between data sets.

What else?
0
 

Author Comment

by:lbarnett419
Comment Utility
Hi Dvz:
(1) No address can exist in the system if no one currently lives there. It is a requirement.

(2) A person or persons(students) can belong to only one location. These are not students in a college setting and are all under 18, so there is no movement out to a dorm part of the time, etc. It doesn't matter if it is called FamilyID or LocationId.

(3) If 3 students live in the same household, 2 may be from a previous marriage (Smith) and 1 from the current marriage (Jones).

(4) You are right, the family surname & streetaddress cannot uniquely differentiate between data sets. I was hoping if we added the motheremail field to the mix, it might help. The motheremail is the same for all students in the same household, regardless of the students' last name.

Unfortunately, I get asked to do things that take a long time manually and was trying to find a way to automate.

I know the most basic way is to order the data by either address or motheremail, eyeball the student names, then assign the numbers manually, but I was hoping for a workaround.

Thank you for your consideration.
0
 
LVL 23

Expert Comment

by:David
Comment Utility
That helps -- somewhat :)  You've clarified that there must be one and only one mothermail per housing unit (address, whatever).  Can the mothermail ever be modified, as in changing carriers?

Rather than a hyphenated string, I'm thinking of a compound key (two or more columns).

A big missing piece is how to differentiate between family and non-family in the unit?  What fact is unique?
0
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.

 
LVL 31

Expert Comment

by:awking00
Comment Utility
SQL> select * from family;

 STUDENTID LASTNAME  FIRSTNAME STREETADDRESS        MOTHEREMAIL
---------- --------- --------- -------------------- -------------------
     19854 Sampson   King      121 Spruce Street    mamasmith@sst.org
     45123 Jones     Howard    111 Main Street      mamajones@sst.org
     49800 Bowen     Brandon   121 Spruce Street    mamasmith@sst.org
     65122 Jones     Susie     111 Main Street      mamajones@sst.org
     79652 Duck      Daffy     1435 False Court     mamaduck@sst.org
     85121 Smith     Johnny    121 Spruce Street    mamasmith@sst.org
     85121 Jones     Mary      111 Main Street      mamajones@sst.org
     98985 Mouse     Mickey    4569 True Lane       mamamouse@sst.org

You said >>The motheremail is the same for all students in the same household<<
Then perhaps something like this works

SQL> select studentid, lastname, firstname, streetaddress, motheremail,
  2  dense_rank() over (order by motheremail) * 100||'-'||
  3  row_number() over (partition by motheremail order by studentid) familyid
  4  from family;

STUDENTID LASTNAME  FIRSTNAME STREETADDRESS        MOTHEREMAIL          FAMILYID
--------- --------- --------- -------------------- -------------------- --------
    79652 Duck      Daffy     1435 False Court     mamaduck@sst.org     100-1
    45123 Jones     Howard    111 Main Street      mamajones@sst.org    200-1
    65122 Jones     Susie     111 Main Street      mamajones@sst.org    200-2
    85121 Jones     Mary      111 Main Street      mamajones@sst.org    200-3
    98985 Mouse     Mickey    4569 True Lane       mamamouse@sst.org    300-1
    19854 Sampson   King      121 Spruce Street    mamasmith@sst.org    400-1
    49800 Bowen     Brandon   121 Spruce Street    mamasmith@sst.org    400-2
    85121 Smith     Johnny    121 Spruce Street    mamasmith@sst.org    400-3
0
 

Author Comment

by:lbarnett419
Comment Utility
(1) Yes, the motheremail may change, but it will attach to all the children at that one address.

(2) I don't care about the hyphen....just so ONE familyid is assigned to all the children at that one address.
Which means, the family id would be 4001 (or whatever works) for all children at the 121 Spruce Street address. It doesn't matter the format--i used the hyphen as an example.  You mentioned locationid earlier...that kind of thing.

Thanks!
0
 
LVL 31

Accepted Solution

by:
awking00 earned 480 total points
Comment Utility
Then just use the dense_rank for the familyid. If you want them to start with 4001, for example, you could do something like the following:
select studentid, lastname, firstname, streetaddress, motheremail,
to_char(dense_rank() over (order by motheremail) + 4000) familyid
from family;
STUDENTID LASTNAME  FIRSTNAME STREETADDRESS        MOTHEREMAIL          FAMILY
--------- --------- --------- -------------------- -------------------- ------
    79652 Duck      Daffy     1435 False Court     mamaduck@sst.org     4001
    45123 Jones     Howard    111 Main Street      mamajones@sst.org    4002
    65122 Jones     Susie     111 Main Street      mamajones@sst.org    4002
    85121 Jones     Mary      111 Main Street      mamajones@sst.org    4002
    98985 Mouse     Mickey    4569 True Lane       mamamouse@sst.org    4003
    19854 Sampson   King      121 Spruce Street    mamasmith@sst.org    4004
    85121 Smith     Johnny    121 Spruce Street    mamasmith@sst.org    4004
    49800 Bowen     Brandon   121 Spruce Street    mamasmith@sst.org    4004
0
 

Author Closing Comment

by:lbarnett419
Comment Utility
Awking: Thanks, works perfectly!
Dvz: Thanks for the analysis.
0
 
LVL 31

Expert Comment

by:awking00
Comment Utility
I just noticed that Mary Jones and Johnny Smith have the same studentID. Is that a typo or can that actually occur?
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

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

744 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

17 Experts available now in Live!

Get 1:1 Help Now