SQL Total Sum with subtraction if new entry is mad

Question

I am trying to find TotalPoints Sum with subtraction if new entry is made in the table,

TotalPoints Query :

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 Query Output:

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

Using above table , I am trying to subtract TotalPoints – Pricepoints based on the CustomerId and PriceId in PriceClaimTable And if there is no new entry in PriceClaimTable based on the Customerid, then just Show Totalpoints without subtraction

Thus far I tried to find the price points

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

This gives me output as:

PricePoints
400

Expected output:

TotalPoints:
100                         //  (500-400)

How can I subtract the results in one Query and find Total Points Sum based on customerid?

Other Table Structure:

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

Any help would be great.

Answer 1

Based on the SQL Fiddle, I made a new attempt, which I believe is correct, although it returns 81 and not 100 for John:

;  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

Assumption is that the customer is always found from the loyalty tables, but not necessary from redeem or price tables

SQL Fiddle for this version: http://sqlfiddle.com/#!3/5e379/8