Policy    Claim      Job       Excess
A         1          E         50.00
A         1          F          0.00
A         2          X         50.00
A         2          Y          0.00
A         2          Z          0.00
 

(
Policy char(1)
,Claim Int
,Job Char(1)
,Excess Decimal (19,2)
)

INSERT into #PolClaim
Values ('A',1,'E',50.00)
,('A',1,'F',50.00)
,('A',2,'X',50.00)
,('A',2,'Y',50.00)
,('A',2,'Z',50.00)


;
With PolicyClaimLines 
AS 
(
	Select Policy,Claim,count(*) LineCount
	From #PolClaim
	Group by Policy,Claim
)
Select
	Pol.Policy
	,Pol.Claim
	,Job
	,Excess
	,Excess/LineCount
	,Row_Number() OVER (Partition By Pol.Policy,Pol.Claim ORDER by Job)
	,CASE Row_Number() OVER (Partition By Pol.Policy,Pol.Claim ORDER by Job)
		WHEN 1 then FLOOR((Excess/LineCount)*100)/100.00
		ELSE CEILING((Excess/LineCount)*100)/100.00
		END
From
	#PolClaim Pol
	JOIN PolicyClaimLines PCL 
		ON PCL.Policy=Pol.Policy
		AND PCL.Claim=Pol.Claim