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