Solved

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

Posted on 2013-11-05
9
330 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
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 20 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
 
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
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 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 480 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

914 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

16 Experts available now in Live!

Get 1:1 Help Now