Geek Answers Handbook

SQL Total Sum with subtraction if a new record is made in a table

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

TotalPoints request:

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

Output points totals:

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 the table above, I'm trying to subtract TotalPoints - Pricepoints based on CustomerId and PriceId in PriceClaimTable. And if there is no new Customerid based entry in PriceClaimTable, just show the exact points without subtracting

So far I have been trying to find 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 a conclusion like:

PricePoints
400

Expected Result:

TotalPoints:
100                         //  (500-400)

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

Another table structure:

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

Any help would be great.

0
1

SQL Fiddle, , , , , 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 Fiddle : http://sqlfiddle.com/#!3/5e379/8

0

More articles:


All Articles