question

sqlnewb avatar image
sqlnewb 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)
sql-server-2008t-sql
6 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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) ''
1 Like 1 ·
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?
0 Likes 0 ·
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
0 Likes 0 ·
So which column is the 'real' data and which is calculated? Up to `RowNo` 7 'real' data is `End` and after it is `Start`?
0 Likes 0 ·
@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
0 Likes 0 ·
Show more comments

1 Answer

·
Kev Riley avatar image
Kev Riley answered
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 ]
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

The variance is +/- I will probably change it to absolute when graphing.
0 Likes 0 ·
@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
0 Likes 0 ·
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.
0 Likes 0 ·
@sqlnewb : updated my answer to correct the error you found.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.