SQL query - Get only the people who only respond to criterias

Question

I'm having trouble doing a SQL query.

To give some context, i have a data extension populated with automated file import. In this table i have every purchase customer did on certain products.

I need to get from this DE only the people who did buy more then 47 € on the "Gamme blanche" during the last 6 months AND buy LESS then 56 € on all the other products during the last 6 months.

Here is what my query looks like (but doesnt give me the results i want)

SELECT

'LAST 6 MONTH' AS PERIOD,
o.MKT_RANGE,
o.YN_MRQ_CNS_BUYER,
o.CS_BP_ID,
SUM(o.MRQ_CNS_RNG_TURNO) AS CA_CUMUL

FROM
"OFFERS by customer and range" AS o

WHERE
CAST(CONCAT(o.YEAR,'/',o.MONTH,'/','30') as date) >= DATEADD(MONTH, DATEDIFF(MONTH,0, GETDATE())-6,0) AND
o.MKT_RANGE = 'GAMME BLANCHE'

GROUP BY 
o.CS_BP_ID,
o.MKT_RANGE,
o.YN_MRQ_CNS_BUYER

HAVING SUM(o.MRQ_CNS_RNG_TURNO)  > 47
UNION
SELECT

'LAST 6 MONTH' AS PERIOD,
o.MKT_RANGE,
o.YN_MRQ_CNS_BUYER,
o.CS_BP_ID,
SUM(o.MRQ_CNS_RNG_TURNO) AS CA_CUMUL

FROM
"OFFERS by customer and range" AS o

WHERE
CAST(CONCAT(o.YEAR,'/',o.MONTH,'/','30') as date) >= DATEADD(MONTH, DATEDIFF(MONTH,0, GETDATE())-6,0) AND
o.MKT_RANGE <> 'GAMME BLANCHE' 

GROUP BY 
o.CS_BP_ID,
o.MKT_RANGE,
o.YN_MRQ_CNS_BUYER

HAVING SUM(o.MRQ_CNS_RNG_TURNO)  < 56

I know that union isn't the good thing to use, but i also cannot use join/inner join because i have two contradictory conditions.

If anyone could help me solving this, it would be greatly appreciated.

Answer 1

How about selecting all of the customers who had more than 47€ in purchases of that specific product and then exists on the other sub-set?

The exists functions like an and since the subquery where-clause includes the "join" back to the outer query. This will work as long as you don't need to return anything in the exists subquery in your final results.

The EOMONTH() function does work in SFMC. I'd use that in your date range criteria. Here's a fiddle example.

select
 'LAST 6 MONTH' PERIOD
, o.MKT_RANGE
, o.YN_MRQ_CNS_BUYER
, o.CS_BP_ID
, SUM(o.MRQ_CNS_RNG_TURNO) CA_CUMUL 
from "OFFERS by customer and range" o
WHERE eomonth(CONCAT(o.year,'-', o.month,'-','1')) >= convert(date, DATEADD(MONTH, DATEDIFF(MONTH,0, GETDATE())-6,0))
and o.MKT_RANGE = 'GAMME BLANCHE'
and o.CS_BP_ID = o.CS_BP_ID
and o.MKT_RANGE = o.MKT_RANGE
and o.YN_MRQ_CNS_BUYER = o.YN_MRQ_CNS_BUYER
and exists (
  select
  SUM(o1.MRQ_CNS_RNG_TURNO)
  from "OFFERS by customer and range" o1
  WHERE eomonth(CONCAT(o1.year,'-', o1.month,'-','1')) >= DATEADD(MONTH, DATEDIFF(MONTH,0, GETDATE())-6,0) 
  and o1.MKT_RANGE = 'GAMME BLANCHE'
  and o1.CS_BP_ID = o.CS_BP_ID
  and o1.MKT_RANGE = o.MKT_RANGE
  and o1.YN_MRQ_CNS_BUYER = o.YN_MRQ_CNS_BUYER
  HAVING SUM(o1.MRQ_CNS_RNG_TURNO) < 56
)
GROUP BY 
  o.CS_BP_ID
, o.MKT_RANGE
, o.YN_MRQ_CNS_BUYER
HAVING SUM(o.MRQ_CNS_RNG_TURNO) > 47