Customers that only made one order

Question

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')

Answer 1

You can try this.

I recreated your scenario:

And used this query:

select customerid,orderdate,orderheaderid from orders where customerid in ( SELECT customerid from orders group by customerid having count(customerid) = 1 ) and FORMAT(orderdate, 'mm-dd-yyyy') = FORMAT(cast('03-01-2020' as date), 'mm-dd-yyyy')