Skip to Content
0
Jan 24, 2022 at 07:52 PM

Trying to use a like statement for a parameter within a with (CTE)

49 Views

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