SQL合计金额相减，如果新条目在表由(SQL Total Sum with subtraction

我试图找到TotalPoints总和相减，如果新的条目在表中进行，

TotalPoints查询：

SELECT SUM(t.TotalUserActions) as Actions, sum(t.AllTotalPoints) as TotalPoints,
(select Name from CustomerTable where CustomerId=1) as Name from 
(
SELECT CASE 
      WHEN LoayaltyPointsTable.LoyaltyPointsId=4 THEN (SELECT COUNT(amount) 
      FROM RedeemPointsTable where CustomerId=1) 
      ELSE COUNT(CustomerTable.CustomerId) 
      END as TotalUserActions , CustomerTable.Name,
    CASE 
      WHEN LoayaltyPointsTable.LoyaltyPointsId=4 THEN (SELECT SUM(amount) 
      FROM RedeemPointsTable where CustomerId=1)*Points
      ELSE SUM(LoayaltyPointsTable.Points) 
      END as AllTotalPoints 

FROM
     LoayaltyPointsTable
INNER JOIN
     LoyaltyDetailsTable
on LoayaltyPointsTable.LoyaltyPointsId = LoyaltyDetailsTable.LoyaltyPointsId
INNER  JOIN 
     CustomerTable 
on CustomerTable.CustomerId = LoyaltyDetailsTable.CustomerId

where CustomerTable.CustomerID =1
group by CustomerTable.Name,LoayaltyPointsTable.LoyaltyPointsId,
         LoayaltyPointsTable.Points,CustomerTable.CustomerId
) t

Totalpoints查询输出：

Actions  Totalpoints     Name
30       500             John

PriceTable

Priceid Title Discriptions Pricepoints
    1    abc    abc           400
    2    def    def           500

PriceClaimTable

PriceClaimId CustomerId PriceId
1               1         22
2               2         23

使用上面的表，我想减TotalPoints - 基于在客户和PriceId在PriceClaimTable Pricepoints如果有基于在客户中PriceClaimTable没有新的条目，那么就显示Totalpoints没有减法

迄今为止，我试图找到价位

select PriceTable.PricePoints from PriceTable  

inner join PriceClaimTable 
on PriceTable.PriceId = PriceClaimTable.PriceId

inner join CustomerTable 
on CustomerTable.CustomerId = PriceClaimTable.CustomerId 

where CustomerTable.CustomerId =1

group by PriceTable.PricePoints

这给了我输出：

PricePoints
400

预期输出：

TotalPoints:
100                         //  (500-400)

我怎样才能减去一个查询的结果，发现基于客户ID总得分总和？

其他的表结构：

http://sqlfiddle.com/#!2/67436/5

任何帮助将是巨大的。

基于SQL小提琴，我做了一个新的尝试，我相信这是正确的，虽然它返回81，而不是100约翰：

;  with LP (CustomerId, Name, UserActions, TotalPoints) as (
SELECT
   C.CustomerId,
   C.Name,
   sum(case when P.LoyaltyPointsId = 4 then isnull(R.RedeemCount, 0) else 1 end),
   sum(P.Points * case when P.LoyaltyPointsId = 4 then isnull(R.RedeemAmount,0) else 1 end)
from
   CustomerTable C
   join LoyaltyDetailsTable D on D.CustomerId = C.CustomerId
   join LoyaltyPointTable P on P.LoyaltyPointsId = D.LoyaltyPointsId
   outer apply (
       select sum(Amount) as RedeemAmount, count(Amount) as RedeemCount 
       from RedeemPointsTable R
       where R.CustomerId = C.CustomerId
   ) R
   group by C.CustomerId, C.Name
),

PP (CustomerId, Pricepoints) as (
    select C.CustomerId, sum(P.Pricepoints)
    from PriceTable P
    join PriceClaimTable C on P.PriceClaimId = C.PriceClaimId
    group by C.CustomerId
)

select 
    LP.CustomerId, LP.Name, LP.UserActions, LP.TotalPoints - isnull(PP.Pricepoints, 0) as Points
from
    LP
    left outer join PP on LP.CustomerId = PP.CustomerId 
order by LP.CustomerId

假设是，客户总是从忠诚度表中找到，但是从赎回或价格表没有必要

SQL捣鼓这个版本： http://sqlfiddle.com/#!3/5e379/8