Switch calculation on column

Question

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)

Answer 1

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 <= (select RowNo from @YourTable where Reason = 'Static')
    then isnull(T2.RunningVar,0) 
    else isnull(T2.RunningVar + T1.Variance,0)
 end as start,
 T1.Variance as [End]
from @YourTable T1
cross apply (select sum(Variance) RunningVar from @YourTable where RowNo < T1.RowNo) T2

gives

RowNo       start       End
----------- ----------- -----------
1           0           20
2           20          5
3           25          10
4           35          3
5           38          2
6           40          8
7           48          0
8           46          -2
9           41          -5
10          37          -4
11          34          -3
12          0           -34

Switch calculation on column

Follow this question

Related Questions