question

Switch calculation on column

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 ·

·
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 ]

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 ·