# Switch calculation on column

 0 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) `````` more ▼ asked Nov 01, 2011 at 06:26 AM in Default sqlnewb 216 ● 27 ● 30 ● 31 Ho do you recognise that RowNo 7 is the end of the group? Is it the zero in the columns Start and End that denote a group of rows? Nov 01, 2011 at 06:34 AM WilliamD There is a column with Reasons...the END column is based on variance..SO there is a REASON column that says STATIC when variance is 0 Nov 01, 2011 at 06:44 AM sqlnewb So which column is the 'real' data and which is calculated? Up to `RowNo` 7 'real' data is `End` and after it is `Start`? Nov 01, 2011 at 06:52 AM Kev Riley ♦♦ @kev Riley END is the variance..S0 it starts at 0 and the first variance is 20 after rowno 7 I just change the order of how the Start column is calculated Nov 01, 2011 at 06:59 AM sqlnewb 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) '' Nov 01, 2011 at 07:09 AM Kev Riley ♦♦ add new comment (comments are locked) 10|1200 characters needed characters left ▼ Viewable by all users

 0 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 more ▼ answered Nov 01, 2011 at 07:33 AM Kev Riley ♦♦ 52.8k ● 47 ● 49 ● 76 The variance is +/- I will probably change it to absolute when graphing. Nov 01, 2011 at 08:18 AM sqlnewb @kev riley Would you be able to explain why you used CROSS APPLY? I am trying to learn as many new techniques as I can, but I am a novice at the moment. I have never seen the use of CROSS APPLY Nov 01, 2011 at 08:25 AM sqlnewb 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 http://www.sqlservercentral.com/articles/T-SQL/61539/) processing known as a triangular join. It will not perform well on large data sets. Nov 01, 2011 at 08:35 AM Kev Riley ♦♦ @sqlnewb : updated my answer to correct the error you found. Nov 01, 2011 at 12:34 PM Kev Riley ♦♦ add new comment (comments are locked) 10|1200 characters needed characters left ▼ Viewable by all users

### New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

By Email:

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1834
x985

asked: Nov 01, 2011 at 06:26 AM

Seen: 473 times

Last Updated: Nov 01, 2011 at 07:04 AM