- SAP Community
- Products and Technology
- Enterprise Resource Planning
- ERP Q&A
- Duplicate item groups in my report
- Subscribe to RSS Feed
- Mark Question as New
- Mark Question as Read
- Bookmark
- Subscribe
- Printer Friendly Page
- Report Inappropriate Content
Duplicate item groups in my report
- Subscribe to RSS Feed
- Mark Question as New
- Mark Question as Read
- Bookmark
- Subscribe
- Printer Friendly Page
- Report Inappropriate Content
on 12-07-2021 7:52 PM
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
- SAP Managed Tags:
- Microsoft SQL Server,
- SQL,
- SAP Business One
Accepted Solutions (1)
Accepted Solutions (1)
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
Hi,
After adding SUM for header, you can eliminate duplicated item groups. Add the titles, after running the query.
DECLARE @SalesPersonName int
SET @SalesPersonName = '42'
SELECT INSIDES.NAMES AS 'Product Group', SUM(INSIDES.[2014]), SUM(INSIDES.[2015]), SUM(INSIDES.[2016]), SUM(INSIDES.[2017]), SUM(INSIDES.[2018]), SUM(INSIDES.[2019]), SUM(INSIDES.[2020]), SUM(INSIDES.[2021]),
/* To Calculate variance of it is this year minus last year*/
SUM(INSIDES.[2021] - INSIDES.[2020]) AS 'Variance vs PY',
/* To calculate quarters it is a repaeat of code with just different period for month */
/* Q1 */
SUM(INSIDES.[Q1 2018]), SUM(INSIDES.[Q1 PY]), SUM(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 */
SUM(INSIDES.[Q2 2018]), SUM(INSIDES.[Q2 PY]), SUM(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 */
SUM(INSIDES.[Q3 2018]), SUM(INSIDES.[Q3 PY]), SUM(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 */
SUM(INSIDES.[Q4 2018]), SUM(INSIDES.[Q4 PY]), SUM(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
GROUP BY INSIDES.NAMES
ORDER BY INSIDES.NAMES
Regards,
Nagarajan
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Answers (0)
- Portfolio Management – Enhanced Financial Planning integration in Enterprise Resource Planning Blogs by SAP
- Group Reporting: Custom fields not included in data release for plandata in Enterprise Resource Planning Q&A
- SAP Table Creation, Maintenance, and Authorization in Enterprise Resource Planning Blogs by Members
- Group Reporting Data Audit in Enterprise Resource Planning Blogs by SAP
- Custom Authorization for Profit Center Hierarchy in Group Reporting in Enterprise Resource Planning Blogs by SAP
| User | Count |
|---|---|
| 95 | |
| 11 | |
| 11 | |
| 6 | |
| 6 | |
| 4 | |
| 4 | |
| 3 | |
| 3 | |
| 3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.