Partition by problem

Transact-SQL
2 
  DECLARE @vt_Table TABLE
    (
      [HPE Op] VARCHAR(50),
      [PL Name] VARCHAR(50),
      [SFDC_DATE] DATE,
      [PL] CHAR(2)
    )
    INSERT INTO @vt_Table([HPE Op],[PL Name],SFDC_DATE,PL)
    VALUES('OPE-0007525935','S6 sy','20170727','S6')
    	  , ('OPE-0007525935','S7 sv','20170727','S7')
    	  , ('OPE-0007525935','HA I','20170727','HA')
    	  , ('OPE-0007522027','S7','20170727','S7')
    	  , ('OPE-0007525021','S8','20170727','S8')

one way :

SELECT
	S.[HPE Op]
	,S.PL
	,CASE WHEN H.new_pl = 'HA' THEN 'HA' ELSE S.[PL] END AS Valoare
FROM
	@vt_Table AS S
	INNER JOIN 
	(
		SELECT
			[HPE Op]
			,MIN(CASE WHEN pl ='HA' THEN 'HA' ELSE 'HAX' END) AS new_pl
		FROM	
			@vt_Table AS T
		GROUP BY 
			T.[HPE Op]
	)H
	ON S.[HPE OP] = H.[HPE Op]

output here:

HPE Op	PL	Valoare
OPE-0007522027	S7	S7
OPE-0007525021	S8	S8
OPE-0007525935	S6	HA
OPE-0007525935	S7	HA
OPE-0007525935	HA	HA


--sau
SELECT
	S.[HPE Op]
	,S.PL
	--,CASE WHEN OA.[PL] IS NOT NULL THEN OA.PL ELSE S.PL END AS Valoare
	,CASE WHEN EXISTS(SELECT * 
						FROM @vt_Table AS T 
						WHERE S.[HPE Op] = T.[HPE Op]
								AND T.[PL] = 'HA'
						) 
			THEN 'HA'
			ELSE S.PL END AS Valoare
FROM
	@vt_Table AS S
	/*OUTER APPLY
	(
		SELECT TOP 1 
			T.[PL]
		FROM	
			@vt_Table AS T
		WHERE
			S.[HPE Op] = T.[HPE Op]
			AND T.[PL] = 'HA'
		ORDER BY
			T.PL ASC
	)OA*/
1 Like