remove rows of data based on data in the combination of two columns

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.

Leave a Comment

Your email address will not be published.

Scroll to Top