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!