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.




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)




I have created a third DE with the following fields





This is what I have so far


from [MainDE] a

left join [OrdersDE] b
on a.customerID = b.customerID 

and [FirstOrderDate] > DATEADD(hh, 7, '01 March 2020')
  • Have you tried adding something like where count(b.customerID) = 1?
    – zuzannamj
    May 13, 2020 at 18:56
  • sorry @zuzannamj, not sure if I understand. How would that help?
    – Duicug
    May 13, 2020 at 20:50
  • You would need something like GROUP BY a.customerID HAVING COUNT(a.customerID) = 1
    – DonL
    May 14, 2020 at 6:50

1 Answer 1


You can try this.

I recreated your scenario:

enter image description here

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') enter image description here

  • Unfortunately I'm not getting the expected result, not sure why because in your recreated scenario the output looks right. I'm getting 136 records and I should be getting around 70k
    – Duicug
    May 14, 2020 at 11:38
  • Can you share your query? Also, can you share your data extension structure, just want to see the primary keys and data types,if that's OK. May 14, 2020 at 11:39
  • My query is a copy paste from yours. The PK for the order table is a composite key with CustomerID(number)(PK) and HeaderID(number)(PK) and the order date is null.
    – Duicug
    May 14, 2020 at 12:39
  • Can you check how many customers have purchased just once, by running the internal query? May 14, 2020 at 12:49
  • I just looked at the dates and noticed that with your query I only get the customers who ordered once but on the first date of the month so 1st of march, 1st of april and 1st of may
    – Duicug
    May 14, 2020 at 13:27

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .