Creating a ward directory

Again, another "exciting" post, which is pretty much just Mike making notes so he can retrace his steps someday if needed.

1.  Log into MLS.  Export the Membership data.  If you need help with this, let me know.

2.  Transfer the csv file to your database server.  Well, in my case, I'm running MySQL on my local desktop.

3.  Log into MySQL!

4.  create database MLS;

5.  create table MLS.Membership
(IndivID int(5) not null,
FullName varchar(50) not null,
PreferredName varchar(50) not null,
HofHID int(5) not null,
HHPosition varchar(50) not null,
HHOrder int(2) not null,
HouseholdPhone varchar(20) null,
IndividualPhone varchar(20) null,
HouseholdEmail varchar (50) null,
IndividualEmail varchar(50) null,
Street1 varchar(100) null,
Street2 varchar(100) null,
DP varchar(20) null,
City varchar(20) null,
Postal varchar(10) null,
State varchar(25) null,
Country varchar(25) null,
2Street1 varchar(100) null,
2Street2 varchar(100) null,
2DP varchar(20) null,
2City varchar(20) null,
2Zip varchar(10) null,
2State varchar(25) null,
2Country varchar(125) null,
WardGeoCode int(10) null,
StakeGeoCode int(10) null,
Sex char(1) not null,
Birth date not null,
Baptized date null,
Confirmed date null,
Endowed date null,
RecExp date null,
Priesthood varchar(20) null,
Mission char(3) null,
Married char(10) null,
SpouseMember char(3) null,
SealedtoSpouse char(3) null,
SealedtoPrior char(3) null);

6.  load data local infile 'C:\\Users\\melquist\\Dropbox\\Mike\\church\\Membership.csv' into table MLS.Membership fields terminated by ','
enclosed by '"'
lines terminated by '\n'
(IndivID,FullName,PreferredName,HofHID,HHPosition,HHOrder,HouseholdPhone,IndividualPhone,HouseholdEmail,IndividualEmail,Street1,Street2,DP,City,Postal,State,Country,2Street1,2Street2,2DP,2City,2Zip,2State,2Country,WardGeoCode,StakeGeoCode,Sex,@Birth,@Baptized,@Confirmed,@Endowed,@RecExp,Priesthood,Mission,Married,SpouseMember,SealedtoSpouse,SealedtoPrior)
set
Birth = str_to_date(@Birth, '%d %b %Y'),
Baptized = str_to_date(@Baptized, '%d %b %Y'),
Confirmed = str_to_date(@Confirmed, '%d %b %Y'),
Endowed = str_to_date(@Endowed, '%d %b %Y'),
RecExp = str_to_date(@RecExp, '%b %Y');

7.  delete from Membership where IndivID = 0;

8.  Query that bad boy!
select * from
(
    SELECT HH.PreferredName "Name", S.PreferredName "SpouseName", HH.Street1, HH.HouseholdPhone, HH.IndividualPhone "CellPhone", S.IndividualPhone "SpouseCellPhone"
    FROM mls.membership HH,
         mls.membership S
    where HH.HHPosition in ('Head of Household','Spouse')
    and HH.HofHID = S.HofHID
    and S.HHPosition = 'Spouse'
    UNION
    Select PreferredName "Name", NULL "SpouseName", Street1, HouseHoldPhone, IndividualPhone "CellPhone", NULL "SpouseCellPhone"
    from mls.membership
    where married = 'Single'
    and HHPosition = 'Head of Household'
) PhoneListing
where Name <> SpouseName
or SpouseName is null
order by Name;

A few things to watch out for....

-> The date formats.  At least MySQL is pretty picky about those.
-> Rather than HHPosition, you could also query off of HHOrder.  Either way, I'm a bit leary of "smart" data.  Keep an eye on the data to make sure the key columns don't change in value.