Calculating Running Total in SQL

Question

I have been looking online for an answer but do not really know how to formulate correctly what I would like to achieve and whether it's possible, sorry if the question sounds dumb.

I have Site data with number of PC and AADTgroup 1,2 and 3 for each site. Data will remain sorted by ROWID and partitioned by AADTGroup.

My existing data is

I want the output with two more columns containing PCsum and NAF_Driver/Vehsum

where, I want the running total of PC in PCsum when it reaches to 5 and corresponding running total of NAF_Driver/Veh in NAF_Driver/Vehsum. Running total would start after reaching the limit 5 and with different AADTGroup.

The desired output is like below

I used SQL code

DECLARE @point INT=5 ; ;WITH x(ROWID, AADT_Group, PC, R, PCsum) AS ( SELECT ROWID, AADT_Group, PC, ROW_NUMBER() OVER (ORDER BY ROWID), SUM(PC) OVER (PARTITION BY AADT_Group ORDER BY [ROWID], ROWID RANGE UNBOUNDED PRECEDING) FROM dbo.CAUrban4LaneDiv$ ) SELECT x.ROWID, x.AADT_Group, x.PC, x.PCsum FROM x WHERE x.PCsum < @point ORDER BY x.ROWID;

But it did't give me the desired result TIA

Answer 1

DECLARE @point INT = 5;

WITH cte AS (
  SELECT 
    ROWID, 
    AADT_Group, 
    PC, 
    NAF_Driver_Veh, 
    ROW_NUMBER() OVER (PARTITION BY AADT_Group ORDER BY ROWID) AS R,
    SUM(PC) OVER (PARTITION BY AADT_Group ORDER BY ROWID) AS PCsum,
    SUM(NAF_Driver_Veh) OVER (PARTITION BY AADT_Group ORDER BY ROWID) AS NAF_Driver_VehSum
  FROM dbo.CAUrban4LaneDiv$
)
SELECT 
  cte.ROWID,
  cte.AADT_Group,
  cte.PC,
  cte.NAF_Driver_Veh,
  CASE WHEN cte.PCsum >= @point AND cte.R >= @point
       THEN CAST(cte.PC AS FLOAT) / cte.PCsum
       ELSE 0
  END AS PC_Prop,
  CASE WHEN cte.PCsum >= @point AND cte.R >= @point
       THEN CAST(cte.NAF_Driver_Veh AS FLOAT) / cte.NAF_Driver_VehSum
       ELSE 0
  END AS NAF_Driver_Veh_Prop
FROM cte
ORDER BY cte.AADT_Group, cte.ROWID;