The code below has a select within a case. I am looking to search a table to see if a record exists and meets my criteria. If it does get the field pointval from that record else just use the rest of the case statement but I am getting errors when trying to check on the Inv-Date. Not sure why. Do I need to have the Sales_Detail file in the case select statement?
SELECT SUM(dbo.Sales_Detail.Units) AS CustUnits, SUM(dbo.Sales_Detail.Sales) AS CustSales, dbo.Sales_Detail.DIM103, dbo.Sales_Detail.DIM400,
dbo.Customer_ActiveTrip.MasterCustId, CASE WHEN EXISTS
(SELECT *
FROM dbo.TripPointValue
WHERE dbo.Sales_Detail.[Inv-Date] >= dbo.TripPointValue.StartDate AND dbo.Sales_Detail.[Inv-Date] <= dbo.TripPointValue.EndDate AND
DIM103 = dbo.TripPointValue.Manufacturer) THEN (SUM(dbo.Sales_Detail.Units) * dbo.TripPointValue.PointVal)
WHEN DIM103 = 'Yokohama' THEN SUM(dbo.Sales_Detail.Units) * 2 WHEN DIM103 = 'Hankook' THEN SUM(dbo.Sales_Detail.Units)
* 2 WHEN DIM103 = 'Nokian' THEN SUM(dbo.Sales_Detail.Units) * 2 WHEN DIM103 = 'Sumitomo' THEN SUM(dbo.Sales_Detail.Units)
* 2 WHEN DIM103 IN ('Goodyear', 'Kelly', 'Dunlop') AND DIM400 IN ('Passenger', 'Light Truck', 'Winter') THEN SUM(dbo.Sales_Detail.Units)
* 2 ELSE SUM(dbo.Sales_Detail.Units) * 1 END AS Points
FROM dbo.Sales_Detail INNER JOIN
dbo.Customer_ActiveTrip ON dbo.Sales_Detail.DIM200 = dbo.Customer_ActiveTrip.CustomerId
WHERE (dbo.Sales_Detail.[Inv-Date] >= DATEADD(day, - 1, { fn CURDATE() })) AND (dbo.Sales_Detail.DIM400 IN ('Passenger', 'Light Truck', 'Winter', 'Medium Truck'))
GROUP BY dbo.Sales_Detail.DIM103, dbo.Sales_Detail.DIM400, dbo.Customer_ActiveTrip.MasterCustId