x

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)
more ▼

asked Nov 01, 2011 at 06:26 AM in Default

sqlnewb gravatar image

sqlnewb
216 28 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 ♦♦
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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 gravatar image

Kev Riley ♦♦
53.9k 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 ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

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.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x1850
x991

asked: Nov 01, 2011 at 06:26 AM

Seen: 513 times

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