0
Dec 07, 2021 at 07:52 PM

# Duplicate item groups in my report

294 Views

I am wondering if my join is wrong as I get duplicate item group rows.

```DECLARE @SalesPersonName int
SET @SalesPersonName = '42'

SELECT INSIDES.NAMES AS 'Product Group', INSIDES.[2014], INSIDES.[2015], INSIDES.[2016], INSIDES.[2017], INSIDES.[2018], INSIDES.[2019], INSIDES.[2020], INSIDES.[2021],
/* To  Calculate variance of it is this year minus last year*/
INSIDES.[2021] - INSIDES.[2020] AS 'Variance vs PY',
/* To calculate quarters it is a repaeat of code with just different period for month */
/* Q1 */
INSIDES.[Q1 2018], INSIDES.[Q1 PY], INSIDES.[Q1 2018] - INSIDES.[Q1 PY] AS 'Q1 Variance',
/* Will write notes here as to have simplier code */

'Enter Amount Manually' AS 'Q1 Budget',
'Use Excel and write formula for Q1 Budget - Q1 Sales' AS 'Q1 Budget Variance',

/* Q2 */
INSIDES.[Q2 2018], INSIDES.[Q2 PY], INSIDES.[Q2 2018] - INSIDES.[Q2 PY] AS 'Q2 Variance',
'Enter Amount Manually' AS 'Q2 Budget',
'Use Excel and write formula for Q2 Budget - Q2 Sales' AS 'Q2 Budget Variance',

/* Q3 */
INSIDES.[Q3 2018], INSIDES.[Q3 PY], INSIDES.[Q3 2018] - INSIDES.[Q3 PY] AS 'Q3 Variance',
'Enter Amount Manually' AS 'Q3 Budget',
'Use Excel and write formula for Q3 Budget - Q3 Sales' AS 'Q3 Budget Variance',

/* Q4 */
INSIDES.[Q4 2018], INSIDES.[Q4 PY], INSIDES.[Q4 2018] - INSIDES.[Q4 PY] AS 'Q4 Variance',
'Enter Amount Manually' AS 'Q4 Budget',
'Use Excel and write formula for Q4 Budget - Q4 Sales' AS 'Q4 Budget Variance'

FROM
(SELECT ItemGroups.ItmsGrpNam AS 'Names',
((SELECT ISNULL(SUM(T1.LineTOTAL),0)
FROM OINV T0 Inner Join INV1 T1 On T0.Docentry = T1.DocEntry INNER JOIN OSLP T2 ON T2.SLPCODE = T0.SLPCODE
WHERE T1.ItemCode = ITEMS.ItemCode AND T0.[SLPCODE] = '[%0]' AND YEAR(T0.DocDate) = '2014')
-
(SELECT ISNULL(SUM(T1.LineTOTAL),0)
FROM ORIN T0 Inner Join RIN1 T1 On T0.Docentry = T1.DocEntry
WHERE T1.ItemCode = ITEMS.ItemCode AND T0.[SLPCODE] = '[%0]'  AND YEAR(T0.DocDate) = '2014')
) AS '2014',
((SELECT ISNULL(SUM(T1.LineTOTAL),0)
FROM OINV T0 Inner Join INV1 T1 On T0.Docentry = T1.DocEntry INNER JOIN OSLP T2 ON T2.SLPCODE = T0.SLPCODE
WHERE T1.ItemCode = ITEMS.ItemCode AND T0.[SLPCODE] = '[%0]' AND YEAR(T0.DocDate) = '2015')
-
(SELECT ISNULL(SUM(T1.LineTOTAL),0)
FROM ORIN T0 Inner Join RIN1 T1 On T0.Docentry = T1.DocEntry
WHERE T1.ItemCode = ITEMS.ItemCode AND T0.[SLPCODE] = '[%0]'  AND YEAR(T0.DocDate) = '2015')
) AS '2015',
((SELECT ISNULL(SUM(T1.LineTOTAL),0)
FROM OINV T0 Inner Join INV1 T1 On T0.Docentry = T1.DocEntry INNER JOIN OSLP T2 ON T2.SLPCODE = T0.SLPCODE
WHERE T1.ItemCode = ITEMS.ItemCode AND T0.[SLPCODE] = '[%0]' AND YEAR(T0.DocDate) = '2016')
-
(SELECT ISNULL(SUM(T1.LineTOTAL),0)
FROM ORIN T0 Inner Join RIN1 T1 On T0.Docentry = T1.DocEntry
WHERE T1.ItemCode = ITEMS.ItemCode AND T0.[SLPCODE] = '[%0]'  AND YEAR(T0.DocDate) = '2016')
) AS '2016',
((SELECT ISNULL(SUM(T1.LineTOTAL),0)
FROM OINV T0 Inner Join INV1 T1 On T0.Docentry = T1.DocEntry INNER JOIN OSLP T2 ON T2.SLPCODE = T0.SLPCODE
WHERE T1.ItemCode = ITEMS.ItemCode AND T0.[SLPCODE] = '[%0]' AND YEAR(T0.DocDate) = '2017')
-
(SELECT ISNULL(SUM(T1.LineTOTAL),0)
FROM ORIN T0 Inner Join RIN1 T1 On T0.Docentry = T1.DocEntry
WHERE T1.ItemCode = ITEMS.ItemCode AND T0.[SLPCODE] = '[%0]'  AND YEAR(T0.DocDate) = '2017')
) AS '2017',
((SELECT ISNULL(SUM(T1.LineTOTAL),0)
FROM OINV T0 Inner Join INV1 T1 On T0.Docentry = T1.DocEntry INNER JOIN OSLP T2 ON T2.SLPCODE = T0.SLPCODE
WHERE T1.ItemCode = ITEMS.ItemCode AND T0.[SLPCODE] = '[%0]' AND YEAR(T0.DocDate) = '2018')
-
(SELECT ISNULL(SUM(T1.LineTOTAL),0)
FROM ORIN T0 Inner Join RIN1 T1 On T0.Docentry = T1.DocEntry
WHERE T1.ItemCode = ITEMS.ItemCode AND T0.[SLPCODE] = '[%0]'  AND YEAR(T0.DocDate) = '2018')
) AS '2018',
((SELECT ISNULL(SUM(T1.LineTOTAL),0)
FROM OINV T0 Inner Join INV1 T1 On T0.Docentry = T1.DocEntry INNER JOIN OSLP T2 ON T2.SLPCODE = T0.SLPCODE
WHERE T1.ItemCode = ITEMS.ItemCode AND T0.[SLPCODE] = '[%0]' AND YEAR(T0.DocDate) = '2019')
-
(SELECT ISNULL(SUM(T1.LineTOTAL),0)
FROM ORIN T0 Inner Join RIN1 T1 On T0.Docentry = T1.DocEntry
WHERE T1.ItemCode = ITEMS.ItemCode AND T0.[SLPCODE] = '[%0]'  AND YEAR(T0.DocDate) = '2019')
) AS '2019',
((SELECT ISNULL(SUM(T1.LineTOTAL),0)
FROM OINV T0 Inner Join INV1 T1 On T0.Docentry = T1.DocEntry INNER JOIN OSLP T2 ON T2.SLPCODE = T0.SLPCODE
WHERE T1.ItemCode = ITEMS.ItemCode AND T0.[SLPCODE] = '[%0]' AND YEAR(T0.DocDate) = '2020')
-
(SELECT ISNULL(SUM(T1.LineTOTAL),0)
FROM ORIN T0 Inner Join RIN1 T1 On T0.Docentry = T1.DocEntry
WHERE T1.ItemCode = ITEMS.ItemCode AND T0.[SLPCODE] = '[%0]'  AND YEAR(T0.DocDate) = '2020')
) AS '2020',
((SELECT ISNULL(SUM(T1.LineTOTAL),0)
FROM OINV T0 Inner Join INV1 T1 On T0.Docentry = T1.DocEntry INNER JOIN OSLP T2 ON T2.SLPCODE = T0.SLPCODE
WHERE T1.ItemCode = ITEMS.ItemCode AND T0.[SLPCODE] = '[%0]' AND YEAR(T0.DocDate) = '2021')
-
(SELECT ISNULL(SUM(T1.LineTOTAL),0)
FROM ORIN T0 Inner Join RIN1 T1 On T0.Docentry = T1.DocEntry
WHERE T1.ItemCode = ITEMS.ItemCode AND T0.[SLPCODE] = '[%0]'  AND YEAR(T0.DocDate) = '2021')
) AS '2021',

--Q1 Data

((SELECT ISNULL(SUM(T1.LineTOTAL),0)
FROM OINV T0 Inner Join INV1 T1 On T0.Docentry = T1.DocEntry INNER JOIN OSLP T2 ON T2.SLPCODE = T0.SLPCODE
WHERE T1.ItemCode = ITEMS.ItemCode AND T0.[SLPCODE] = '[%0]' AND YEAR(T0.DocDate) = '2018' AND(MONTH(T0.DocDate) >= 1 AND MONTH(T0.DocDate) <= 3) )
-
(SELECT ISNULL(SUM(T1.LineTOTAL),0)
FROM ORIN T0 Inner Join RIN1 T1 On T0.Docentry = T1.DocEntry
WHERE T1.ItemCode = ITEMS.ItemCode AND T0.[SLPCODE] = '[%0]'  AND YEAR(T0.DocDate) = '2018' AND(MONTH(T0.DocDate) >= 1 AND MONTH(T0.DocDate) <= 3) )
) AS 'Q1 2018',

((SELECT ISNULL(SUM(T1.LineTOTAL),0)
FROM OINV T0 Inner Join INV1 T1 On T0.Docentry = T1.DocEntry INNER JOIN OSLP T2 ON T2.SLPCODE = T0.SLPCODE
WHERE T1.ItemCode = ITEMS.ItemCode AND T0.[SLPCODE] = '[%0]' AND YEAR(T0.DocDate) = '2017' AND(MONTH(T0.DocDate) >= 1 AND MONTH(T0.DocDate) <= 3) )
-
(SELECT ISNULL(SUM(T1.LineTOTAL),0)
FROM ORIN T0 Inner Join RIN1 T1 On T0.Docentry = T1.DocEntry
WHERE T1.ItemCode = ITEMS.ItemCode AND T0.[SLPCODE] = '[%0]'  AND YEAR(T0.DocDate) = '2017' AND(MONTH(T0.DocDate) >= 1 AND MONTH(T0.DocDate) <= 3) )
) AS 'Q1 PY',

--Q2 Data

((SELECT ISNULL(SUM(T1.LineTOTAL),0)
FROM OINV T0 Inner Join INV1 T1 On T0.Docentry = T1.DocEntry INNER JOIN OSLP T2 ON T2.SLPCODE = T0.SLPCODE
WHERE T1.ItemCode = ITEMS.ItemCode AND T0.[SLPCODE] = '[%0]' AND YEAR(T0.DocDate) = '2018' AND(MONTH(T0.DocDate) >= 4 AND MONTH(T0.DocDate) <= 6) )
-
(SELECT ISNULL(SUM(T1.LineTOTAL),0)
FROM ORIN T0 Inner Join RIN1 T1 On T0.Docentry = T1.DocEntry
WHERE T1.ItemCode = ITEMS.ItemCode AND T0.[SLPCODE] = '[%0]'  AND YEAR(T0.DocDate) = '2018' AND(MONTH(T0.DocDate) >= 4 AND MONTH(T0.DocDate) <= 6) )
) AS 'Q2 2018',

((SELECT ISNULL(SUM(T1.LineTOTAL),0)
FROM OINV T0 Inner Join INV1 T1 On T0.Docentry = T1.DocEntry INNER JOIN OSLP T2 ON T2.SLPCODE = T0.SLPCODE
WHERE T1.ItemCode = ITEMS.ItemCode AND T0.[SLPCODE] = '[%0]' AND YEAR(T0.DocDate) = '2017' AND(MONTH(T0.DocDate) >= 4 AND MONTH(T0.DocDate) <= 6) )
-
(SELECT ISNULL(SUM(T1.LineTOTAL),0)
FROM ORIN T0 Inner Join RIN1 T1 On T0.Docentry = T1.DocEntry
WHERE T1.ItemCode = ITEMS.ItemCode AND T0.[SLPCODE] = '[%0]'  AND YEAR(T0.DocDate) = '2017' AND(MONTH(T0.DocDate) >= 4 AND MONTH(T0.DocDate) <= 6) )
) AS 'Q2 PY',

-- Q3 data

((SELECT ISNULL(SUM(T1.LineTOTAL),0)
FROM OINV T0 Inner Join INV1 T1 On T0.Docentry = T1.DocEntry INNER JOIN OSLP T2 ON T2.SLPCODE = T0.SLPCODE
WHERE T1.ItemCode = ITEMS.ItemCode AND T0.[SLPCODE] = '[%0]' AND YEAR(T0.DocDate) = '2018' AND(MONTH(T0.DocDate) >= 7 AND MONTH(T0.DocDate) <= 9) )
-
(SELECT ISNULL(SUM(T1.LineTOTAL),0)
FROM ORIN T0 Inner Join RIN1 T1 On T0.Docentry = T1.DocEntry
WHERE T1.ItemCode = ITEMS.ItemCode AND T0.[SLPCODE] = '[%0]'  AND YEAR(T0.DocDate) = '2018' AND(MONTH(T0.DocDate) >= 7 AND MONTH(T0.DocDate) <= 9) )
) AS 'Q3 2018',

((SELECT ISNULL(SUM(T1.LineTOTAL),0)
FROM OINV T0 Inner Join INV1 T1 On T0.Docentry = T1.DocEntry INNER JOIN OSLP T2 ON T2.SLPCODE = T0.SLPCODE
WHERE T1.ItemCode = ITEMS.ItemCode AND T0.[SLPCODE] = '[%0]' AND YEAR(T0.DocDate) = '2017' AND(MONTH(T0.DocDate) >= 7 AND MONTH(T0.DocDate) <= 9) )
-
(SELECT ISNULL(SUM(T1.LineTOTAL),0)
FROM ORIN T0 Inner Join RIN1 T1 On T0.Docentry = T1.DocEntry
WHERE T1.ItemCode = ITEMS.ItemCode AND T0.[SLPCODE] = '[%0]'  AND YEAR(T0.DocDate) = '2017' AND(MONTH(T0.DocDate) >= 7 AND MONTH(T0.DocDate) <= 9) )
) AS 'Q3 PY',

-- Q4 data

((SELECT ISNULL(SUM(T1.LineTOTAL),0)
FROM OINV T0 Inner Join INV1 T1 On T0.Docentry = T1.DocEntry INNER JOIN OSLP T2 ON T2.SLPCODE = T0.SLPCODE
WHERE T1.ItemCode = ITEMS.ItemCode AND T0.[SLPCODE] = '[%0]' AND YEAR(T0.DocDate) = '2018' AND(MONTH(T0.DocDate) >= 10 AND MONTH(T0.DocDate) <= 12) )
-
(SELECT ISNULL(SUM(T1.LineTOTAL),0)
FROM ORIN T0 Inner Join RIN1 T1 On T0.Docentry = T1.DocEntry
WHERE T1.ItemCode = ITEMS.ItemCode AND T0.[SLPCODE] = '[%0]'  AND YEAR(T0.DocDate) = '2018' AND(MONTH(T0.DocDate) >= 10 AND MONTH(T0.DocDate) <= 12) )
) AS 'Q4 2018',

((SELECT ISNULL(SUM(T1.LineTOTAL),0)
FROM OINV T0 Inner Join INV1 T1 On T0.Docentry = T1.DocEntry INNER JOIN OSLP T2 ON T2.SLPCODE = T0.SLPCODE
WHERE T1.ItemCode = ITEMS.ItemCode AND T0.[SLPCODE] = '[%0]' AND YEAR(T0.DocDate) = '2017' AND(MONTH(T0.DocDate) >= 10 AND MONTH(T0.DocDate) <= 12) )
-
(SELECT ISNULL(SUM(T1.LineTOTAL),0)
FROM ORIN T0 Inner Join RIN1 T1 On T0.Docentry = T1.DocEntry
WHERE T1.ItemCode = ITEMS.ItemCode AND T0.[SLPCODE] = '[%0]'  AND YEAR(T0.DocDate) = '2017' AND(MONTH(T0.DocDate) >= 10 AND MONTH(T0.DocDate) <= 12) )
) AS 'Q4 PY'

/* Table where the item group info is gathered */
FROM  OITM ITEMS
INNER JOIN OITB ItemGroups ON ITEMS.ItmsGrpCod = ItemGroups.ItmsGrpCod
) INSIDES
WHERE INSIDES.[2014] + INSIDES.[2015] + INSIDES.[2016] + INSIDES.[2017] + INSIDES.[2018] + INSIDES.[2019] + INSIDES.[2020] + INSIDES.[2021] <> 0
ORDER BY INSIDES.NAMES```