I'm looking to write a SQL Query to get the Customers that made only one order after the first of March. So if they ordered twice after the first of March I want to exclude them.
I have the following data extensions.
MainDE
CustomerID(PK)
FirstOrderDate(null)
OrdersDE (this is the DE with all orderes so if a Customer would have ordered on the first of March and second of March would be two orderDates)
CustomerID(PK)
OrderHeaderID(PK)
OrderDate(null)
I have created a third DE with the following fields
MyDE
CustomerID(null)
FirstOrderDate(null)
OrderDate(null)
This is what I have so far
SELECT
a.customerID,
a.FirstOrderDate,
b.OrderDate
from [MainDE] a
left join [OrdersDE] b
on a.customerID = b.customerID
and [FirstOrderDate] > DATEADD(hh, 7, '01 March 2020')
where count(b.customerID) = 1
?