0

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'

enter image description here

Sample Rows: enter image description here

Sample output: enter image description here

3
  • Is productPurchased a Number or a String? If you could update your question to include a data sample and the desired output, that would help us help you. Feb 1 at 19:25
  • Hi Adam, Thanks for your response. ProductPurchased is a number field. If customer purchase 1 product in a day, the productpurchased will be updated to 1. when he he purchases next product it will be updated as 2 (+1 for each new product). my goal is to pull only those customers who purchase 1 product in a day. if he purchases more than 1, exclude him from the query results. Feb 2 at 9:15
  • I've updated my answer. Feb 2 at 13:16
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
6
  • Initially, I tried same query. However I'm not getting desired result. productpurchased = 1 works fine. However, if customer purchased more than 1 product today, that customer also getting added in SQL results. 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. Feb 2 at 13:52
  • As there are 2 records for Mr. Xyz, I would like to exclude him from the query results. apologies for confusion. Feb 2 at 13:53
  • Please update your question to include a few sample rows from OrderDE and the desired output. Feb 2 at 14:01
  • Hi Adam, I've updated my question. Feb 3 at 16:22
  • Can you provide a sample of the desired output of the query? Feb 8 at 12:41

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.