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:
.
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!
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*/