?
Solved

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

Posted on 2013-11-05
9
Medium Priority
?
340 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
9 Comments
 

Author Comment

by:lbarnett419
ID: 39625046
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 80 total points
ID: 39625428
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
ID: 39625561
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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 23

Expert Comment

by:David
ID: 39625655
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
 
LVL 32

Expert Comment

by:awking00
ID: 39625665
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
ID: 39625723
(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 32

Accepted Solution

by:
awking00 earned 1920 total points
ID: 39627319
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
ID: 39627342
Awking: Thanks, works perfectly!
Dvz: Thanks for the analysis.
0
 
LVL 32

Expert Comment

by:awking00
ID: 39627438
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
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.
Suggested Courses

764 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