Hi Mika,
Facts:
LOAD as SUPIdentificationNumber,
as SUPClientName,
date(,'DD/MM/YYYY') as SUPDateOfCommencement,
date( ,'DD/MM/YYYY') as %DateKey,
date(,'DD/MM/YYYY') as SUPDateOfCreation,
as SUPClientDoB,
as SUPEmployerLegalName,
as SUPEmployerTradingName,
rtrim(ltrim(field_officer_id)) as FactConsultant,
AutoNumber(rtrim(ltrim())) as %ConsultantKey,
date(,'DD/MM/YYYY') as SUPNTCReceivedDate,
as SUPRTOName,
signup_status_id as SUPStatus,
date(,'DD/MM/YYYY') as SUPStatusDateChange,
rtrim(ltrim()) as SUPAccountManager,
as SUPEmployerTYIMSID,
as SUPBDLReferredBY,
bdl_referred_by_id,
at_fault,
1 as SUP#,
if(isnull(), 0, 1) as SUPBDLLead#,
if(='Not BDL Lead',1,0) as SUPNotBDLLead#,
If(signup_status_id='Sign Up Complete',1,0) as SUPComplete#,
If(signup_status_id='Received',1,0) as SUPReceived#,
if(signup_status_id='4',1,0) as SUPReturn#,
if(at_fault='f',1,0) as AtFaultReturn#
;
SQL SELECT
FieldOfficer.first_name ||' '||
FieldOfficer.surname as "Field Officer",
signed_on as "Date Training Contract Signed",
ss.name as Status,
commencementdate,
appdateofbirth,
employerlegalname,
employertradingname,
tc.id as id,
received_on,
tyims_employerid,
AccountManager.first_name ||' '||
AccountManager.surname as "Account Manager",
BDLRefferedBy.first_name ||' '||
BDLRefferedBy.surname as "BDL Referred By",
tc.bdl_referred_by_id,
last_kpi_action_date as "Status Date of change",
employer_id as "Employer ID",
appgivenname ||' '||
appsurname as "Client Full Name",
rtoname as "Name of RTO",
rtoname as "RTO Name",
rtocode as "RTO NTIS CODE",
heard_about_us_id as "Where Hear",
tc.created_at as "Created Date",
BDLRefferedBy.id,
tc.signup_status_id,
tc.received_at ,
tc.field_officer_id ,
tc.account_manager_id ,
tc.tier ,
tc.employment_type_id ,
tc.contract_type_id ,
tc.finalised_on ,
tc.approved_on ,
tc.training_plan_signed_on ,
tc.training_plan_received_on ,
tc.file_no,
tc.employee_id,
tc.last_kpi_action_date,
tcr.at_fault
from training_contracts tc
left join signup_statuses ss on tc.signup_status_id = ss.id
left join users FieldOfficer on FieldOfficer.id = tc.field_officer_id
Left join users AccountManager on AccountManager.id = tc.account_manager_id
left join users BDLRefferedBy on BDLRefferedBy.id = tc.bdl_referred_by_id
left join training_contract_returns tcr on tc.id = tcr.training_contract_id
where signed_on >= '2014-07-01';
