Cacher is the code snippet organizer for pro developers

Select lead.Email as 'Lead:Email', lead.id as 'Lead:Id',lead.MiddleName as 'Lead:MiddleName', lead.LastName as 'Lead:LastName', lead.Gender__c as 'Lead:Gender__c'
FROM [Lead_Salesforce_1] as lead
Left join _sent on _sent.SubscriberKey=lead.id
Left join _job on _sent.jobid=_job.jobid AND _job.EmailName = 'AUDI-transactional-newsletter'
Inner Join CampaignMember_Salesforce_2 on CampaignMember_Salesforce_2.leadid=lead.id and CampaignMember_Salesforce_2.CampaignId='70158000000NIBPAA4'
Where (lead.Lead_Sub_Type__c = 'No follow-up' AND lead.LeadType__c = 'Newsletter' AND lead.Interested_In_Brand__c = 'Audi')
AND cast(switchoffset(todatetimeoffset(lead.CreatedDate, '-06:00'), '+02:00') as datetime) > cast(switchoffset(todatetimeoffset(DATEADD(minute,-20,GETDATE()), '-06:00'), '+02:00') as datetime)
AND (cast(_job.PickupTime as datetime) < cast(DATEADD(minute,-20,GETDATE()) as datetime) OR _job.PickupTime is NUll)