Don't Understand SQL Syntax Error

Question

I've gone over this code several times but I cannot figure out why I'm getting

Incorrect Syntax near the keyword 'SR'

Here's my SELECT query:

SELECT OrderDetails.OrderID,OrderDetails.ProductCode,OrderDetails.Vendor_Price,OrderDetails.Quantity
FROM OrderDetails
JOIN
(SELECT OrderDetails.OrderID,  
CASE Orders.SalesRep_CustomerID WHEN  1 THEN 'S'  WHEN 2 THEN 'K' WHEN 3 THEN 'M' ELSE '' END 
FROM Orders
GROUP BY OrderDetails.OrderID)
AS 'SR'
WHERE OrderDetails.ShipDate IS NOT NULL
AND OrderDetails.ShipDate >= DATEADD(Day, Datediff(Day,0, GetDate() -6), 0)
ORDER BY OrderDetails.ProductCode ASC

It's pretty straightforward I just don't see the syntax error the computer is referring to. Thanks.

Answer 1

Change 'SR' to SR. There is no need for quotes.

[UPDATE]

You also forget ON statement after AS SR

JOIN (select query...) as SR on SR.OrderID = OrderDetails.OrderID

I guess it should be OrderID if not change it to whatever you need.

[UPDATE 3] REPLACE THE WHOLE QUERY

BTW, I think you wont achieve the result with this query, you need to use the following:

SELECT OrderDetails.OrderID
            ,OrderDetails.ProductCode
            ,OrderDetails.Vendor_Price
            ,OrderDetails.Quantity
            ,CASE Orders.SalesRep_CustomerID WHEN  1 THEN 'S'  WHEN 2 THEN 'K' WHEN 3 THEN 'M' ELSE '' END 
    FROM OrderDetails 
    JOIN #Also consider using INNER JOIN if needed
        Orders on Orders.OrderId = OrderDetails.OrderId
    WHERE 
        OrderDetails.ShipDate IS NOT NULL
        AND OrderDetails.ShipDate >= DATEADD(Day, Datediff(Day,0, GetDate() -6), 0)
    ORDER BY OrderDetails.ProductCode ASC

Answer 2

You do not have an ON statement after your JOIN