It seems the = sign is useful for integers but for strings I tried using it but got no result. I am trying to make it that the @SLP parameter takes the Sales person name and filters out any OCRD table. Tried different approaches. I did it in the main query in the bottom and in the CTE. nothing seems to work. I could use the SLPCODE which is not so user friendly.
/**SELECT FROM [OSLP] T11 **/ DECLARE @Slp AS VARCHAR /* WHERE */ SET @Slp = /* T11.SLPNAME */ 'Jack Cohen' /**SELECT FROM [OINV] T9 **/ DECLARE @Year1 AS INT /* WHERE */ SET @Year1 = /* T9.DocDate */ YEAR('[%1]') /**SELECT FROM [OINV] T10 **/ DECLARE @Year2 AS int /* WHERE */ SET @Year2 = /* T10.DocDate */ YEAR('[%2]') ; WITH -- only fetch items with sales ItemsInfo (ItemCode, ItemName,TotalQuantity) AS ( SELECT OITM.ItemCode, OITM.ItemName, ( (SELECT ISNULL(SUM(T0.Quantity),0) FROM INV1 T0 INNER JOIN OINV T1 ON T0.DocEntry = T1.DocEntry WHERE YEAR(T0.[DocDate]) >= @Year1 AND YEAR(T0.[DocDate]) <= @Year2 AND T0.Itemcode = OITM.ItemCode ) - (SELECT ISNULL(SUM(T0.Quantity),0) FROM RIN1 T0 INNER JOIN ORIN T1 ON T0.DocEntry = T1.DocEntry WHERE YEAR(T0.[DocDate]) >= @Year1 AND YEAR(T0.[DocDate]) <= @Year2 AND T0.Itemcode = OITM.ItemCode ) ) 'TotalQuantity' FROM OITM ) , BP (CardCode, CardName, SlpCode) AS ( SELECT T0.[CardCode], T0.[CardName], T0.[SlpCode] FROM OCRD T0 INNER JOIN OSLP T1 ON T1.SLPCODE = T0.SLPCODE WHERE T1.SLPNAME LIKE @SLP ) SELECT INSIDES.[Customer Code], INSIDES.[Customer Name], INSIDES.[Item No.], INSIDES.[Item Description], INSIDES.[Quantity LY] AS [Quantity LY], INSIDES.[Quantity TY] AS [Quantity TY], INSIDES.[Total$ LY] AS [Last Year], INSIDES.[Total$ TY] AS [This Year], ISNULL(INSIDES.[Quantity TY],0) - ISNULL(INSIDES.[Quantity LY],0) AS [Delta_QT], ISNULL(INSIDES.[Total$ TY],0) - ISNULL(INSIDES.[Total$ LY],0) AS [Delta_Val] FROM ( SELECT BP.CardCode AS 'Customer Code', BP.CardName AS 'Customer Name', ItemsInfo.ItemCode AS 'Item No.', ItemsInfo.ItemName AS 'Item Description', ( (SELECT ISNULL(SUM(T0.Quantity),0) FROM INV1 T0 INNER JOIN OINV T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OCRD T2 ON T2.CardCode = T1.CardCode INNER JOIN OSLP T3 ON T2.SLPCODE = T3.SLPCODE WHERE YEAR(T0.[DocDate]) = @Year1 AND T0.Itemcode = ItemsInfo.ItemCode AND T3.SLPNAME LIKE '[%0]' ) - (SELECT ISNULL(SUM(T0.Quantity),0) FROM RIN1 T0 INNER JOIN ORIN T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OCRD T2 ON T2.CardCode = T1.CardCode INNER JOIN OSLP T3 ON T2.SLPCODE = T3.SLPCODE WHERE YEAR(T0.[DocDate]) = @Year1 AND T0.Itemcode = ItemsInfo.ItemCode AND T3.SLPNAME LIKE '[%0]' ) ) AS 'Quantity LY', ( (SELECT ISNULL(SUM(T0.Quantity),0) FROM INV1 T0 INNER JOIN OINV T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OCRD T2 ON T2.CardCode = T1.CardCode INNER JOIN OSLP T3 ON T2.SLPCODE = T3.SLPCODE WHERE YEAR(T0.[DocDate]) = @Year2 AND T0.Itemcode = ItemsInfo.ItemCode AND T3.SLPNAME LIKE '[%0]' ) - (SELECT ISNULL(SUM(T0.Quantity),0) FROM RIN1 T0 INNER JOIN ORIN T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OCRD T2 ON T2.CardCode = T1.CardCode INNER JOIN OSLP T3 ON T2.SLPCODE = T3.SLPCODE WHERE YEAR(T0.[DocDate]) = @Year2 AND T0.Itemcode = ItemsInfo.ItemCode AND T3.SLPNAME LIKE '[%0]' ) ) AS 'Quantity TY', ( (SELECT ISNULL(SUM(T0.LineTotal),0) FROM INV1 T0 INNER JOIN OINV T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OCRD T2 ON T2.CardCode = T1.CardCode INNER JOIN OSLP T3 ON T2.SLPCODE = T3.SLPCODE WHERE YEAR(T0.[DocDate]) = @Year1 AND T0.Itemcode = ItemsInfo.ItemCode AND T3.SLPNAME LIKE '[%0]' ) - (SELECT ISNULL(SUM(T0.LineTotal),0) FROM RIN1 T0 INNER JOIN ORIN T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OCRD T2 ON T2.CardCode = T1.CardCode INNER JOIN OSLP T3 ON T2.SLPCODE = T3.SLPCODE WHERE YEAR(T0.[DocDate]) = @Year1 AND T0.Itemcode = ItemsInfo.ItemCode AND T3.SLPNAME LIKE '[%0]' ) ) AS 'Total$ LY', ( (SELECT ISNULL(SUM(T0.LineTotal),0) FROM INV1 T0 INNER JOIN OINV T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OCRD T2 ON T2.CardCode = T1.CardCode INNER JOIN OSLP T3 ON T2.SLPCODE = T3.SLPCODE WHERE YEAR(T0.[DocDate]) = @Year2 AND T0.Itemcode = ItemsInfo.ItemCode AND T3.SLPNAME LIKE '[%0]' ) - (SELECT ISNULL(SUM(T0.LineTotal),0) FROM RIN1 T0 INNER JOIN ORIN T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OCRD T2 ON T2.CardCode = T1.CardCode INNER JOIN OSLP T3 ON T2.SLPCODE = T3.SLPCODE WHERE YEAR(T0.[DocDate]) = @Year2 AND T0.Itemcode = ItemsInfo.ItemCode AND T3.SLPNAME LIKE '[%0]' ) ) AS 'Total$ TY' FROM ItemsInfo, BP ) INSIDES WHERE (INSIDES.[Quantity TY] + INSIDES.[Quantity LY]) <> 0 OR (INSIDES.[Total$ TY] + INSIDES.[Total$ LY]) <> 0