SELECT    	[User].FullName AS Responsible,
			COUNT(DISTINCT Lawsuit.Id) AS LawsuitInWork,
			COUNT(DISTINCT Payments.Parent) AS Debetors,
			CAST(ROUND(COUNT(DISTINCT Payments.Parent)*100.0/COUNT(DISTINCT Lawsuit.Id),2) as decimal(5,2)) as LawsuitPercent,
			SUM(ostatok.Ostatok ) AS Balance,
			SUM(Payments.Payments) AS Payments,
			ROUND((SUM(Payments.Payments)/SUM(ostatok.Ostatok))*100,2) as PaymentsPercent
from Lawsuit
left join [User] ON Lawsuit.Responsible = [User].Id
left join (select SUM( Lawsuit_OstatokDolgov.ostatok) as Ostatok, Lawsuit_OstatokDolgov.Parent from Lawsuit_OstatokDolgov group by  Lawsuit_OstatokDolgov.Parent) as ostatok on ostatok.Parent = Lawsuit.Id
left join (select Sum (Lawsuit_Payments.Sum) as Payments, Lawsuit_Payments.Parent as Parent from Lawsuit_Payments
{if {$BeginTerm} <> Null AND {$EndTerm} <> Null}
	WHERE Lawsuit_Payments.PayDate >= @BeginTerm
	AND Lawsuit_Payments.PayDate <= @EndTerm
	AND Lawsuit.StageStatus IS NOT NULL AND
	(Lawsuit.StageStatus = 1 OR Lawsuit.StageStatus = 102 OR Lawsuit.StageStatus = 103)
{end if}
	group by Lawsuit_Payments.Parent
) as Payments on Payments.Parent = Lawsuit.Id

{if {$Claimant} <> Null AND {$Responsible.Count} <= 0}
Where
	Lawsuit.Claimant = @Claimant
{end if}

{if {$Claimant} = Null AND {$Responsible.Count} > 0}
Where
	Lawsuit.Responsible in (@Responsible)
{end if}

{if {$Claimant} <> Null AND {$Responsible.Count} > 0}
Where
		Lawsuit.Responsible in (@Responsible)
		AND Lawsuit.Claimant = @Claimant
{end if}

Where
Lawsuit.StageStatus IS NOT NULL AND
	(Lawsuit.StageStatus = 1 OR Lawsuit.StageStatus = 102 OR Lawsuit.StageStatus = 103)


group by [User].FullName
order by Payments DESC