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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.