Basic SF to SugarCRM

-- Manual opt-outs for some folks...
select id,FirstName,LastName from contact where HasOptedOutOfEmail = 1;

-- get list of Lead Sources
select distinct(LeadSource) from contact;

-- create these custom fields:
-- Campaign__c as campaign_c,
-- RFC_Rcd__c as rfc_rcd_c,
-- Spouse__C as spouse_c,
-- Partner__c as partner_c,
-- Dev_Agent__C as dev_agent_c,
-- Status__c as status_c,
-- -- Temp__c as temp_c,
-- Item_23_Rcd__c as item_23_rc_c,
-- FDD_Sent__c as fdd_sent_c

-- Pull contact information, formatted headers
select
Id as id,
Salutation as salutation,
Firstname as first_name,
LastName as last_name,

MailingStreet as primary_address_street,
MailingCity as primary_address_city,
MailingState as primary_address_state,
MailingPostalCode as primary_address_postalcode,
MailingCountry as primary_address_country,

OtherStreet as alt_address_street,
OtherCity as alt_address_city,
OtherState as alt_address_state,
OtherPostalCode as alt_address_postalcode,
OtherCountry as alt_address_country,

Phone as phone_work,
Fax as phone_fax,
MobilePhone as phone_mobile,
HomePhone as phone_home,
OtherPhone as phone_other,
AssistantPhone as assistant_phone,
Email as email1,
Title as title,
Department as department,
AssistantName as assistant,
LeadSource as lead_source,
Birthdate as birthdate,
Description as description,
DoNotCall as do_not_call,
Campaign__c as campaign_c,
RFC_Rcd__c as rfc_rcd_c,
Spouse__C as spouse_c,
Partner__c as partner_c,
Dev_Agent__C as dev_agent_c,
Status__c as status_c,
Temp__c as temp_c,
Item_23_Rcd__c as item_23_rc_c,
FDD_Sent__c as fdd_sent_c

from contact
where IsDeleted = 0;

-- pull event/meeting information, formatted headers
select
WhoId as parent_id,
'Contacts' as parent_type,
Subject as name,
Location as location,
ActivityDateTime as start_date,
DurationInMinutes as total_duration_in_minutes, -- won't be used, just for error-checking
DurationInMinutes DIV 60 as duration_hours,
DurationInMinutes MOD 60 as duration_minutes,
Description as description
from event
where IsDeleted = 0;

-- pull notes information, formatted headers
select
ParentId as parent_id,
Title as name,
Body as description,
'Contacts' as parent_type
from note
where IsDeleted = 0;

-- pull task information, formatted headers
select
WhoId as parent_id,
'Contacts' as parent_type,
Subject as name,
ActivityDate as start_date,
ActivityDate as end_date,
Status as status,
Priority as priority,
CASE
    WHEN Priority = 'Normal' THEN 'Medium'
    WHEN Priority = 'High' THEN 'High'
    ELSE NULL
END as priority
from task
where IsDeleted = 0;

--           --
-- Round Two --
--           --

-- Pull contact information, formatted headers
select
Id as id,
`First Name` as first_name,
`Last Name` as last_name,
`Primary Address` as primary_address_street,
`Primary Address City` as primary_address_city,
`Primary Address State` as primary_address_state,
`Primary Address Postal Code` as primary_address_postalcode,
`Primary Address Country` as primary_address_country,
`Alternate Address` as alt_address_street,
`Alternate Address City` as alt_address_city,
`Alternate Address State` as alt_address_state,
`Alternate Address Postalcode` as alt_address_postalcode,
`Alternate Address Country` as alt_address_country,
`Office Phone` as phone_work,
Fax as phone_fax,
`Mobile Phone` as phone_mobile,
`Home Phone` as phone_home,
`Other Phone` as phone_other,
Email as email1,
`Lead Source` as lead_source,
`Campaign/Referral Source` as campaign_c,
`RFC Rcd` as rfc_rcd_c,
`Spouse` as spouse_c,
`Partner` as partner_c,
`Development Agent` as dev_agent_c,
`Status` as status_c,
`Item 23 Rcd` as item_23_rc_c,
`FDD Sent` as fdd_sent_c
from contact;

-- pull event/meeting information, formatted headers
select
WhoId as parent_id,
'Contacts' as parent_type,
Subject as name,
Location as location,
ActivityDateTime as start_date,
DurationInMinutes DIV 60 as duration_hours,
DurationInMinutes MOD 60 as duration_minutes,
Description as description,
OwnerId as assigned_user_name
from events
where IsDeleted = 0;

-- pull notes information, formatted headers
select
ParentId as parent_id,
Title as name,
Body as description,
OwnerId as assigned_user_name,
'Contacts' as parent_type
from notes
where IsDeleted = 0;

-- pull task information, formatted headers
select
WhoId as parent_id,
'Contacts' as parent_type,
Subject as name,
ActivityDate as start_date,
ActivityDate as end_date,
Status as status,
OwnerId as assigned_user_name,
CASE
    WHEN Priority = 'Normal' THEN 'Medium'
    WHEN Priority = 'High' THEN 'High'
    ELSE NULL
END as priority
from tasks
where IsDeleted = 0;