Dennis
11/6/2018 - 1:56 PM

PON SQL - SEAT - Transactional - Proefrit

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', 
lead.Interested_In_Vehicle_Model__c as 'Lead:Interested_In_Vehicle_Model__c', lead.Interested_In_Brand__c as 'lead:Interested_In_Brand__c', lead.Dealer_Preference_Sales_Dealer__c as 'lead:Dealer_Preference_Sales_Dealer__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 = 'SEAT_transactional_proefrit' 
Where (lead.Lead_Sub_Type__c = 'Test Drive' AND lead.LeadType__c = 'New Vehicle' AND lead.Interested_In_Brand__c = 'SEAT')
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)