Select with in a Case

1

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

2

What error message are you seeing?

Not sure about this bit:

{ fn CURDATE() }

in particular the curly brackets and the "fn"

3

Your problem is that dbo.TripPointValue.PointVal is not in scope for:

THEN (SUM(dbo.Sales_Detail.Units) * dbo.TripPointValue.PointVal)

I would be inclined not to use ODBC functions but to use CURRENT_TIMESTAMP or GETDATE() instead.

Try something like:

SELECT SUM(D.Units) AS CustUnits
    ,SUM(D.Sales) AS CustSales
    ,D.DIM103, D.DIM400, A.MasterCustId
    ,SUM
    (
        CASE
            WHEN P.PointVal IS NOT NULL
            THEN D.Units * T.PointVal
            WHEN D.DIM103 IN ('Yokohama','Hankook','Nokian','Sumitomo')
            THEN D.Units * 2
            WHEN D.DIM103 IN ('Goodyear','Kelly','Dunlop')
                AND DIM400 IN ('Passenger','Light Truck','Winter')
            THEN D.Units * 2
            ELSE D.Units
        END
    ) AS Points
FROM dbo.Sales_Detail D
    JOIN dbo.Customer_ActiveTrip A
        ON D.DIM200 = A.CustomerId
    OUTER APPLY
    (
        SELECT TOP (1) T.PointVal
        FROM dbo.TripPointValue T
        WHERE D.[Inv-Date] BETWEEN T.StartDate AND T.EndDate
            AND D.DIM103 = T.Manufacturer
        --????
        ORDER BY T.PointVal
    ) P
WHERE D.[Inv-Date] >= DATEADD(day, - 1, CURRENT_TIMESTAMP)
    AND D.DIM400 IN ('Passenger','Light Truck','Winter','Medium Truck')
GROUP BY D.DIM103, D.DIM400, A.MasterCustId;