Is it possible to substract multiple rows based on other table without looping?

user_3226114 0
Viewed 80

Let's say I have this Table Rewards

CustomerID DateReceived Point
         1   2020-01-01    15
         1   2020-01-02    20
         2   2020-01-08    20
         2   2020-01-10    10
         3   2020-01-10    50

Then I have this table MaxRewards

CustomerID MaxPoint
         1       20
         2       10
         3       30

So, the idea is Customer's Point combined (summed) should never surpassed MaxPoint in MaxRewards table, if it does, then it will substract starting on latest date, so in the case above, the expected result should be

CustomerID DateReceived Point
         1   2020-01-01    15
         1   2020-01-02     5
         2   2020-01-08    10
         2   2020-01-10     0
         3   2020-01-10    30

How do I execute a query to update the table without a loop? I've been thinking of using CROSS APPLY but I can't seem to get it right.

sql-server sql
4 Answers

The following approach seems to be working:

WITH cte AS (
    SELECT
        r.CustomerID,
        r.DateReceived,
        r.Point,
        mr.MaxPoint,
        COALESCE(SUM(r.Point) OVER (PARTITION BY r.CustomerID ORDER BY r.DateReceived
                                    ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0) AS cumPoints
    FROM Rewards r
    INNER JOIN MaxRewards mr ON r.CustomerID = mr.CustomerID
)

SELECT
    CustomerID,
    DateReceived,
    CASE WHEN cumPoints + Point < MaxPoint
         THEN Point
         ELSE CASE WHEN MaxPoint - cumPoints > 0
                   THEN MaxPoint - cumPoints ELSE 0 END END AS Point
FROM cte
ORDER BY
    CustomerID,
    DateReceived;

screen capture from demo link below

Demo

The logic used here is to compute, in the CTE, the cumulative sum of points for each customer, before the current record in the date sequence. Then, we report one of two things as the Point in the output. For cases where the cumulative sum be already greater than the maximum number of points, we report 0. Otherwise, we report the difference between the maximum number of points and the cumulative sum, which gives the number of points from the current record which we want to add.

user_18632290

Make use of Lag and Case statement:

Demo

SELECT CASE WHEN RUNNING_SUM > MAXPOINT AND PREV_VALUE IS NULL AND POINT > MAXPOINT THEN MAXPOINT
            WHEN RUNNING_SUM > MAXPOINT AND PREV_VALUE IS NULL THEN POINT - MAXPOINT
            WHEN RUNNING_SUM > MAXPOINT AND PREV_VALUE IS NOT NULL AND PREV_VALUE < POINT THEN POINT - PREV_VALUE
            WHEN RUNNING_SUM > MAXPOINT AND PREV_VALUE IS NOT NULL THEN POINT - MAXPOINT         
            WHEN RUNNING_SUM > MAXPOINT THEN MAXPOINT - PREV_VALUE
            ELSE POINT END AS POINT, 
CUSTOMERID, DATERECEIVED FROM (
SELECT C.CUSTOMERID,C.DATERECEIVED, C.POINT, M.MAXPOINT, LAG(POINT) OVER(PARTITION BY C.CUSTOMERID ORDER BY DateReceived) PREV_VALUE,
 SUM(POINT) OVER(PARTITION BY C.CUSTOMERID ORDER BY DateReceived) RUNNING_SUM
FROM CUSTOMER C INNER JOIN MaxRewards M
ON(C.CUSTOMERID = M.CUSTOMERID) ) X;
user_109082740

You can achieve the result from your requirements using lag, derived table, and case

  select
    CustomerID,
    DateReceived,
     case
       when R.LastPoint > R.MaxPoint then 0
       when R.Point + R.LastPoint > R.MaxPoint 
        then R.MaxPoint - R.LastPoint 
        else Point
      end as Point
  from (
  select
    R.CustomerID,
    R.DateReceived,
    R.Point,
    MR.MaxPoint,
    isnull(LAG(R.Point,1) OVER (
          PARTITION BY R.CustomerID
          ORDER BY R.DateReceived
      ),0) LastPoint
   from dbo.Rewards R
   join dbo.MaxRewards MR
     on R.CustomerID = MR.CustomerID
   ) R

results

http://sqlfiddle.com/#!18/1a368

user_23443940

Please find the below query, I used an accumulative sum to calculate how many points left for each customer, and used IIF to understand whether a customer surpassed its threshold :

SELECT CustomerID,DateReceived,IIF(temp_point>0,temp_point,0) as Point
FROM
(
  select CustomerID,DateReceived,
  IIF(PointsLeft>0,Point,Point-ABS(PointsLeft)) as temp_point
  from
  (
    select Rewards.* , 
           sum(Rewards.Point) over (partition by MaxRewards.CustomerID order by DateReceived asc) s,
           MaxRewards.Point as MaxPoint,
           MaxRewards.Point - sum(Rewards.Point) over (partition by MaxRewards.CustomerID order by DateReceived asc) PointsLeft
    From Rewards, MaxRewards
    where Rewards.CustomerID = MaxRewards.CustomerID
  ) a
) b
user_16776380
Related