I have a query that is working well, except that users have found a way to screw with the data, so I have to account for the users mistakes.
SELECT DISTINCT CAST(COALESCE(LEFT(od.Part_Family_Code,2),0) AS decimal (10,2)) AS FBGM, ROUND(ISNULL(qbd.Profit_Percent+cd.Commission_Percent,qbd.Profit_Percent),2) AS Budgeted, oh.Order_Number AS Order_Number, od.Work_Code AS Work_Code, sc.Address__Email_Address AS Email_Address, sco.Salesman_Name AS Salesman_Name, cd.Commission_Percent AS Commission_Percent, od.Employee_Code_Routed_By AS Routed_By, od.Product_Code AS Product_Code, od.Part_Number AS Part_Number, CAST(COALESCE(LEFT(od.Part_Family_Code,2),0) *.9 AS decimal (10,2)) AS FBGMx9, CAST(COALESCE(LEFT(od.Part_Family_Code,2),0) *1.1 AS decimal (10,2)) AS FBGMx11, uc.EMail_Address_Primary AS EMail_Address_Primary, cd.Commissionable AS Commissionable FROM dbo.Order_Detail od LEFT OUTER JOIN dbo.Order_Header oh ON od.Order_Header_ID = oh.Order_Header_ID LEFT OUTER JOIN dbo.Quantity_Break_Detail qbd ON od.Order_Detail_ID = qbd.Order_Detail_ID LEFT OUTER JOIN dbo.Billing_Detail bd ON oh.Part_Number_ID = bd.Part_Number_ID LEFT OUTER JOIN dbo.vQV_SalesmanCode_Orders sco ON od.Order_Detail_ID = sco.Order_Detail_ID LEFT OUTER JOIN dbo.Commission_Distribution cd ON od.Order_Detail_ID = cd.Order_Detail_ID LEFT OUTER JOIN dbo.vSalesman_Code sc ON cd.Salesman_Code = sc.Salesman_Code LEFT OUTER JOIN dbo.Employee_Code ec ON od.Employee_Code_Routed_By = ec.Employee_Code LEFT OUTER JOIN dbo.User_Code uc ON ec.User_Code = uc.User_Code WHERE qbd.Profit_Percent <> 0 AND od.Profit_Percent <> 0 AND od.Status NOT IN ( 'Closed' , 'Hold' , 'Planned' ) AND od.Part_Family_Code NOT IN ( '' , '0% - FBGM - WO' , '99' , '99% - FBGM' ) AND (CASE WHEN cd.Commissionable = 1 THEN ((ROUND(qbd.Profit_Percent + cd.Commission_Percent, 2) NOT BETWEEN CONVERT(INT, LEFT(od.Part_Family_Code,2)) -5 AND CONVERT(INT, LEFT(od.Part_Family_Code,2)) +5)) ELSE ((ROUND(qbd.Profit_Percent, 2) NOT BETWEEN CONVERT(INT, LEFT(od.Part_Family_Code,2)) -5 AND CONVERT(INT, LEFT(od.Part_Family_Code,2)) +5)) END) ORDER BY sco.Salesman_Name ASC
I've added the CASE statement and I'm getting the error "Incorrect Syntax Near the Keyword NOT"
I'm not 100% sure if the CASE statement is in the correct format. When a user checks the Pay Commission Box, it changes the record in cd.Commissable from 0 to 1. When they do that I have to add the commission rate back in (qbd.Profit_Percent + cd.Commission_Percent) If they forget, I need to know that and not add the commission rate back in.
Any help would be appreciated.