Hello,I need to calculate te target time, being the time that is to be substracted of an emplyees target. Having the table below, how would i be able to add a column where the registered time is calculated as follows:

1. For every itemid, if an employee is "mentor", he gets all the time, the others get 0.2. For every Itemid, if mentor is not present time is divided between employees.

SELECT *,CASE WHEN employeeID = mentorID THEN [registered time]
WHEN COUNT(CASE WHEN employeeID= mentorID THEN 1 ELSE 0 END) OVER (PARTITION BY itemID) >0 THEN 0
ELSE [registered time]/COUNT(1) OVER (PARTITION BY itemID)
END AS [target-time]
FROM Table

SELECT *,CASE WHEN employeeID = mentorID THEN [registered time]
WHEN COUNT(CASE WHEN employeeID= mentorID THEN 1 ELSE 0 END) OVER (PARTITION BY itemID) =0 THEN [registered time]/COUNT(1) OVER (PARTITION BY itemID)
ELSE 0
END AS [target-time]
FROM Table