asked
Switch calculation on column
I have interesting problem but I am not sure if it is even possible to complete. I wanted to see if anyone had any ideas on how to go about this. Please note the example below: This is how I want the data to look. Note at rowno 7 the calculations reverse. Before rowno 7 I add the start and end from the same rowno and carry that value to the start of the nxt rowno. After row 7 it subtracts startrow 7 from end row 8 and puts the value in start of rowno 8. RowNo Reason Start End 1 begin 0 20 2 add 20 5 3 add 25 10 4 add 35 3 5 add 38 2 6 add 40 8 7 static 48 0 8 reduce 46 (2) 9 reduce 41 (5) 10 reduce 37 (4) 11 reduce 34 (3) 12 end 0 (34)
So there seems to be 2 separate calculations, depending on whether the variance is before or after the 'Static' value of REASON column (not shown here)? Is there only ever one 'Static' in the data, i.e. only one turning point? Could the base data be defined as RowNo Variance Reason 1 20 '' 2 5 '' 3 10 '' 4 3 '' 5 2 '' 6 8 '' 7 0 'Static' 8 (2) '' 9 (5) '' 10 (4) '' 11 (3) '' 12 (34) ''
So which column is the 'real' data and which is calculated? Up to `RowNo` 7 'real' data is `End` and after it is `Start`?
Is the variance +/- like below, or is it an absolute value? declare @YourTable table (RowNo int, Variance int, Reason varchar(20)) insert into @YourTable select 1,20,'' insert into @YourTable select 2,5, '' insert into @YourTable select 3,10,'' insert into @YourTable select 4,3, '' insert into @YourTable select 5,2, '' insert into @YourTable select 6,8, '' insert into @YourTable select 7,0, 'Static' insert into @YourTable select 8,-2,'' insert into @YourTable select 9,-5,'' insert into @YourTable select 10,-4,'' insert into @YourTable select 11,-3,'' insert into @YourTable select 12,-34,'' select T1.RowNo, case when T1.RowNo ]
CROSS APPLY allows me to 'call a function' for each row returned from the base table. The 'function' I'm calling is simply a sum of the values for all the rows prior to the one I'm currently dealing with. This is a case of row-by-agonizing-row (RBAR ) processing known as a triangular join. It will not perform well on large data sets.
