SQL query to exclude subscribers

Question

I'm trying to pull the data from orderDE, where purchaseddate is (day - 1). if the customer only purchased a single product in a day, include him in results. If a customer purchases more than 1 product in a day, exclude that customer.

I'm not very good with SQL, Can someone pls help me with this. Below query is working however I'm not able to exclude those customers who purchased more than 1 product in a day.

Here is an example of my use case: -On February 1st, Mr.Xyz purchased Product1. So productpurchased updated to 1. -On the same day, Mr.Xyz purchased Product2, so productpurchased updated to 2 with same subcriberID. -Now, for 1st February, there are two records with the same subscriberID and purchaseddate. For first record productpurchased = 1 and for second record it is 2.

As there are 2 records for Mr. Xyz, I would like to exclude him from the query results.

My sql Query :-

select

subscriberID, phonenumber, productpurchased, purchaseddate, productcode

From orderDE

where

productcode in (203, 211, 502)

AND

purchaseddate > dateadd(d,-1,getdate())

AND

productpurchased <= '1'

Sample Rows:

Sample output:

Answer 1

If you need to limit the results to only those that have a single order in a day, you could do something like this:

select
  o.subscriberID
, o.phonenumber
, o.productpurchased
, o.purchaseddate
, o.productcode
From orderDE o 
where 
o.productcode in (203, 211, 502) 
AND o.purchaseddate > convert(date, getdate()-1) 
and exists (
  select o1.subscriberid
  from orderDE o1
  where o1.subscriberid = o.subscriberid
  and o1.purchaseddate > convert(date, getdate()-1)
  group by o1.subscriberid 
  having count(*) = 1
)

The sub-query is only those subscribers with a single order in the date range.

The exists where-clause sub-query is joined by subscriberId to the outer query.

Update

Based on the productPurchased details you provided, my query can be simplified. this should give you anyone who only purchased products 203, 211, or 402 since yesterday.

select
  o.subscriberID
, o.phonenumber
, o.productpurchased
, o.purchaseddate
, o.productcode
From orderDE o 
where 
o.productcode in (203, 211, 502) 
AND o.purchaseddate > convert(date, getdate()-1) 
and o.productpurchased = 1