0

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

Existing Data

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

output

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

5
  • I also want the running total be partitioned by AADT group Jun 14, 2019 at 15:02
  • 1
    You can edit the question and add that comment to it.
    – McNets
    Jun 14, 2019 at 15:05
  • I am new and couldn't find the option, thanks a lot. Jun 14, 2019 at 15:09
  • 1
    The desired output doesn't make much sense. I don't see a running total. For example in the very first row, why PCSum should be 6 when PC is 0? Jun 14, 2019 at 18:53
  • Hi ypercube, thanks for your concern, actually the rows for which the cumulative sum would reach 5, I need the proportion in each row using this cumulative sum in each row Jun 15, 2019 at 14:15

1 Answer 1

0
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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.