SELECT DISTINCT
sf_contact.id AS contactid,
sf_case.contact_email__c AS contact_email,
sf_case.contact_full_name__c AS contact_full_name,
sf_contact.firstname AS contact_first_name,
sf_contact.lastname AS contact_last_name,
sf_case.id AS case_id,
sf_case.RecordTypeName__c AS case_type,
sf_case.ownerid AS case_owner_id,
sf_user.email AS case_owner_email,
sf_user.name AS case_owner_full_name,
sf_user.firstname AS case_owner_first_name,
sf_user.lastname AS case_owner_last_name,
sf_case.status AS case_status,
sf_case.kickoff_call__c AS kickoff_call,
sf_case.gameplan_url__c AS gameplan_url,
sf_case.Site_Shell_URL_Text__c AS siteshell_url,
sf_case.Registration_URL__c AS registration_url,
sf_case.Last_Session_Created__c AS last_session_created,
sf_case.First_Session_Status__c AS first_session_status,
sf_case.first_usage_date__c AS first_usage_date,
sf_case.case_age__c AS case_age,
sf_case.organization__c AS organization_id,
sf_case.Organization_Name__c AS organization_name,
sf_case.Anticipated_Usage_Date_AUD__c AS anticipated_usage_date,
sf_orgs.Processor_Settings_Status__c AS processor_settings_status,
sf_orgs.Bank_Account_Status__c AS bank_account_status,
sf_case.Registration_Project_Status__c AS registration_project_status,
sf_orgs.Boss_Organization_Id_Text__c AS boss_id,
sf_org_prod.Sold_On__c as sold_on,
sf_case.accountid AS account_id,
sf_case.Organization_Product__c AS organization_product_id,
sf_user.phone AS case_owner_phone,
sf_orgs.country__c AS organization_country,
sf_case.Automated_Messaging_Opt_Out__c AS opt_out,
sf_session.Session_Id__c AS session_id,
sf_session.Status__c AS session_status,
sf_contact.id AS contact_id,
sf_account.name AS account_name


FROM ENT.[Case_salesforce] sf_case
LEFT JOIN ENT.[Org_Product__c_Salesforce] sf_org_prod ON sf_org_prod.id = sf_case.Organization_Product__c
INNER JOIN ENT.[User_Salesforce] sf_user ON sf_case.ownerid = sf_user.id
INNER JOIN ENT.[contact_salesforce] sf_contact ON sf_case.contactid = sf_contact.id
INNER JOIN ENT.[ORGANIZATION__C_SALESFORCE] sf_orgs ON sf_case.organization__c = sf_orgs.id
INNER JOIN ENT.[Account_Salesforce] sf_account ON sf_case.accountid = sf_account.id
LEFT JOIN ENT.[Session__c_Salesforce] sf_session ON sf_org_prod.id = sf_session.Organization_Product__c


WHERE
sf_case.RecordTypeName__c = 'Onboarding Project'
AND
sf_org_prod.Sold_On__c >= DATEADD(day, -20, cast(GETDATE() AS DATE))
AND
sf_org_prod.Sold_On__c <= DATEADD(day, -11, cast(GETDATE() AS DATE))
AND
(sf_case.status = 'new'
OR
sf_case.status = 'in progress')
AND
(sf_user.email = 'emailsample@sample.com'