0

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')
  • Have you tried adding something like where count(b.customerID) = 1? – zuzannamj May 13 at 18:56
  • sorry @zuzannamj, not sure if I understand. How would that help? – Duicug May 13 at 20:50
  • You would need something like GROUP BY a.customerID HAVING COUNT(a.customerID) = 1 – DonL May 14 at 6:50
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

| improve this answer | |
  • 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 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. – Swati Mishra May 14 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 at 12:39
  • Can you check how many customers have purchased just once, by running the internal query? – Swati Mishra May 14 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 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.