0

Trying to create a Data Extension where we calculate the total spend in a category, if the category exists in a list of promotional categories. The point is that we want to provide an offer when a minimum spend is achieved.

I can't seem to figure this out and have been digging for some time. Below is the part that is tripping me up. CategoryID is in the table being evaludated and there are potential matches in Offers. I tried to CAST the results, but not luck. I only want to find the total spend on the product if the product is in the right category.

The ExtPrice field is Decimal(10,2) and the Quantity field is a Number

CASE when CategoryID in (Select ID from Offers) Then (CAST(ExtPrice as Decimal(10,2)) * CAST(Quantity as Decimal(10,2))) end as Product1_TotalSpend

If someone can point me in the right direction, it would be most appreciated!

2

You could do something like this:

select
i.orderNum
, sum(
    case when o.offerID is not null then i.extprice else 0 end
) promoSpend
, sum (
    case when o.offerID is null then i.extprice else 0 end
) nonPromoSpend
from orderItems i
left join Offers o on o.offerid = i.categoryID
group by
i.ordernum

Here's a link to the test: https://rextester.com/VPCU67650

  • Thank you. This definitely got me to my answer. What I didn't mention is that I had to multiply the quantity by the price to get to a total spend, but what you provided got me 90% of the way there. I owe you a beverage. – George Rosedale Nov 20 '18 at 6:03

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.