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
