Partition by problem

Transact-SQL
1

hello, i found this site very helpuf and maybe someone can help me.

i have a table in which i need to search for those opprtunities that have PL=HA and need to put "1" in the column[HA]. Lets say that the column HA is empty.
my problem is that if the opportunities repeat i need to put "1" even if on raws they dont have the PL=HA.
example:
image.

for example the opprtunity OPE-0007525935, repeats 3 times but it only has on 1 raw HA at PL. i need to fill with "1" for all 3 raws.dont take into consderation the "YES" filling in thsi column. at the end i will need to replace 1 with yes.

i have tried this:

select [HPE Opportunity Id],[PL], case when [PL]='HA' then count([HPE Opportunity Id]) over (partition by [HPE Opportunity Id],[PL]) else ' 'end as valoare from table

but it doest populate the HA column as i want. For this opprtuity it puts 1 only on the raw that has HA at PL, the rest are 0.
Maybe someone has an ideea, i would really appreciate.
thank you!

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
3

Depending on what values can be present in field PL, you can develop other solution.
(I 'm thinking to use MIN(PL) = 'HA' ....)

4

thank you so much, it worked

5

Great! my pleasure

6

Alternative (based on your initial query):

select [HPE Opportunity Id]
      ,[PL]
      ,sign(sum(case when [PL]='HA' then 1 else 0 end) over(partition by [HPE Opportunity Id])) as valoare
  from [table]
;
1 Like
7

Thank you, this works as well :smiley: