Subquery returned more than 1 value

[hdigital]

I have read quite a bit on the 'Subquery returned more than 1 value'
I understand why and seems obvious, but as I do want multiple records returned I am unsure how to re write this, this looks correct to me.
Does anyone know what im doing wrong here.

SET @CARRIED_FWD=(SELECT ISNULL(REMAINING,0) FROM SV_EMPLOYEE_SICKNESS_CALCULATION
where EMPLOY_REF=@EMPLOY_REF AND SICKNESS_END IN (SELECT MAX(S2.SICKNESS_END)
FROM SV_EMPLOYEE_SICKNESS_CALCULATION S2
WHERE S2.EMPLOY_REF=@EMPLOY_REF ) ) --CH002

Thanks in advance.

[Delerna]

Difficult to say without knowing what you intended to do with @CARRIED_FWD
once you got it

Because you need to keep all records returned then 1 possibility that comes to mind is to keep what you have as a subquery with appropriate fields added so that you can join it to the query where you would have used @CARRIED_FWD

SELECT your,fields
FROM OtherTableOrView a
JOIN
(SELECT S2.EMPLOY_REF,ISNULL(REMAINING,0) 
FROM SV_EMPLOYEE_SICKNESS_CALCULATION
where EMPLOY_REF=@EMPLOY_REF 
  AND SICKNESS_END IN (SELECT MAX(S2.SICKNESS_END)
                                         FROM SV_EMPLOYEE_SICKNESS_CALCULATION S2 
                                         WHERE S2.EMPLOY_REF=@EMPLOY_REF ) 
)  b on a.???=b.EMPLOY_REF
 
 

Of course you will need to decide for yourself which fields to include and join to
as well as make any other adjustments required to make it work for your situation.

Hope this idea helps but there are plenty of other ways to resolve it.

[hdigital]

Thanks for your response. would it help if I provided more info

[Delerna]

If you need more help

[hdigital]

I sent you a personal message. I was unsure of publishing the full script