how to truncate values while calculating in a window function

Question

I hope my title somewhat reflects the issue...

I have table Courses which I track each year how many points a student got. Now I have to run a query which tallies the points. But: any row that passes 150 or a multiple of 150, on that year the student loses any points higher then TotaPoints/150.

Here is the SQL fiddle: http://sqlfiddle.com/#!18/e84bf/10

Here is the simple schema, I put in the CorrectResult manually here for testing:

CREATE TABLE Courses
    ([Year] int, [Points] int, [CorrectResult] int)
;
    
INSERT INTO Courses
    ([Year], [Points], [CorrectResult])
VALUES
    (1, 148, 148),
    (2, 4, 150),
    (3, 149, 299)
;

Here is my meanwhile incorrect query:

SELECT 
  Year,
  Points,
  CorrectResult,
  TotalCredits - LostPointsTally as RelevantPoints 
FROM (
  SELECT 
    Year,
    Points,
    CorrectResult,
    TotalCredits,
    LAG(LevelGroup, 1, 0) OVER (ORDER BY Year) AS PreviousLevelGroup,
    CASE 
      WHEN LevelGroup > LAG(LevelGroup, 1, 0) OVER (ORDER BY Year) THEN 
        TotalCredits - (LAG(LevelGroup, 1, 0) OVER (ORDER BY Year) * 150)
      ELSE 
        0
    END AS LostPointsTally
  FROM (
    SELECT 
      Year,
      Points,
      CorrectResult,
      TotalCredits,
      FLOOR(TotalCredits / 150) + 1 AS LevelGroup
    FROM (
      SELECT 
        Year,
        Points,
        CorrectResult,
        SUM(CASE 
          WHEN Points > 150 THEN 150
          ELSE Points
        END) OVER (ORDER BY Year) AS TotalCredits
      FROM Courses
    ) t
  ) t1
) t2;

Running this query, on row 3 you get 300, which is wrong. we need to get 150+149 which is 299.

(I hope I come through clear, if not I'll be more than glad to clarify further)

Please advise

Thanks so much

Answer 1

As I understand your problem, you want to calculate an adjusted running total where anytime a multiple of 150 is passed, the total is clamped at that 150 multiple. The running total for later rows continues from that point until the next 150 multiple is reached. Further, the points per record is limited to 150, so it is not possible to cross more that one multiple of 150 in the same year.

Because the calculation for each row depends on the non-trivial adjustment calculation from the previous row, I do not think this can be done using a straightforward aggregation with running totals and the LAG() function. This will require an iterative calculation, possibly using a recursive CTE (common table expression).

The following uses multiple CTEs to build up to the results. The first CTE applies a limit to the points that each year can contribute. The second CTE is a recursive CTE that includes the first year as a base and recursively includes later years joined to the prior year results.

I reworked your LevelGroup logic by calculating a LevelUpThreshold value (next multiple of 150) that limits the running sum for that year. It is then a matter of calculating the prior-adjusted-sum + current-adjusted-points and compare with the calculated LevelUpThreshold to select the new adjusted sum. The LEAST() function works quite nicely for this.

The resulting query would be something like:

WITH CTE_Adjusted_Points AS (
    SELECT C.*, LEAST(Points, 150) AS AdjustedPoints
    FROM Courses C
),
CTE_Adjusted_Sum AS (
    SELECT
        P.*,
        P.AdjustedPoints AS RawSum,
        P.AdjustedPoints AS AdjustedSum
    FROM CTE_Adjusted_Points P
    WHERE P.Year = 1
    UNION ALL
    SELECT
        P.*,
        S.AdjustedSum + P.AdjustedPoints AS RawSum,
        LEAST(S.AdjustedSum + P.AdjustedPoints, L.LevelUpThreshold) AS AdjustedSum
    FROM CTE_Adjusted_Sum S
    JOIN CTE_Adjusted_Points P ON P.Year = S.year + 1
    CROSS APPLY (
        SELECT (S.AdjustedSum / 150 + 1) * 150 AS LevelUpThreshold
    ) L
)
SELECT S.Year, S.Points, S.AdjustedPoints, S.RawSum, S.AdjustedSum, S.CorrectResult
FROM CTE_Adjusted_Sum S
ORDER BY S.Year;

The above logic assumes:

1. All Points values are non-negative.
2. Year starts at 1 and increments without duplicates or gaps.

Results (with some extra test data):

Year	Points	AdjustedPoints	RawSum	AdjustedSum	CorrectResult
1	148	148	148	148	148
2	4	4	152	150	150
3	149	149	299	299	299
4	10	10	309	300	300
5	500	150	450	450	450
6	70	70	520	520	520
7	70	70	590	590	590
8	70	70	660	600	600
9	70	70	670	670	670

See this db<>fiddle for a working example.