WITH cte AS
(
SELECT TOP (1000)
MARA.MATNR,
MARA.SATNR,
MARA.ATTYP,
MARA.MTART,
MARA.MSTAE,
MARA.LVORM,
MVKE.VMSTA as Variant_Status,
MVKE.VTWEG, mvke.VKORG, MVKE2.
VMSTA as Generic_Status,
MVKE2.VTWEG, MVKE2.VKORG,
mara.satnr + mvke.vkorg as concated,
SUM(CASE WHEN Variant_Status <> 'I2' THEN 1 ELSE 0 END) OVER (PARTITION BY SATNR, VKORG) marker
from [dgSAP_PRD].dbo.MARA AS MARA
JOIN [dgSAP_PRD].dbo.MVKE AS MVKE ON MARA.MATNR = MVKE.MATNR
JOIN [dgSAP_PRD].dbo.MARA AS MARA2 ON MARA.SATNR = MARA2.MATNR
JOIN [dgSAP_PRD].dbo.MVKE AS MVKE2 ON MARA2.MATNR = MVKE2.MATNR
WHERE MARA.MTART <> 'ZODE'
AND MARA.ATTYP in (02)
AND MARA.LVORM = ''
AND MVKE2.VTWEG = '34'
AND MVKE.VTWEG = '34'
AND MVKE.VKORG=MVKE2.VKORG
and mvke2.vmsta <> 'I2'
)
SELECT *
FROM cte
WHERE marker = 0
ORDER BY SATNR, VKORG, VKORG, MATNR
CLICK HERE to find out more related problems solutions.