WITH X AS
(
SELECT DISTINCT
ClientId, SetId, sum(TransferedCapital) as TransferedCapital
from CaseDetailsView
group by ClientId, SetId
)
SELECT
......................
,sum(PCH.PaymentValue)/(X.TransferedCapital-sum(case when CA.CaseActionDefinitionId = 87 then CDV.CurrentCapital else 0 end)) as [Payment1]
,datediff(week,CDV.DateImported,PS.SessionDate)+1) as [Week]
from
CaseActionHistory as CA
join CaseDetailsView as CDV on CA.CaseDetailId = CDV.CaseDetailsId
join PaymentCaseHistory as PCH on PCH.ActionArchiveId = CA.CaseActionId
join X on CDV.ClientId = X.ClientId and CDV.SetId = X.SetId
join PaymentSession as PS on PS.SessionId = PCH.SessionId
where
(CDV.ClientId = @ClientId or @ClientId IS NULL)
and
(CA.IsDeleted IS NULL or CA.IsDeleted <> 'True')
and
(CDV.SetId IN (@SetId))
group by
CDV.ClientId,CDV.SetId, CDV.DateImported, CDV.ProductName, COALESCE(datediff(week,CDV.DateImported,PS.SessionDate)+1,0), X.TransferedCapital