How to use previous row's column's value f

Question

I have a table

Id | Aisle | OddEven | Bay | Size | Y-Axis
3  | A1    | Even    | 14  | 10   | 100 
1  | A1    | Even    | 16  | 10   |
6  | A1    | Even    | 20  | 10   |
12 | A1    | Even    | 26  | 5    | 150
10 | A1    | Even    | 28  | 5    |
11 | A1    | Even    | 32  | 5    |
2  | A1    | Odd     | 13  | 10   | 100
5  | A1    | Odd     | 17  | 10   |
4  | A1    | Odd     | 19  | 10   |
9  | A1    | Odd     | 23  | 5    | 150
7  | A1    | Odd     | 25  | 5    |
8  | A1    | Odd     | 29  | 5    |

want to look like this

Id | Aisle | OddEven | Bay | Size | Y-Axis
1  | A1    | Even    | 14  | 10   | 100 
2  | A1    | Even    | 16  | 10   | 110
3  | A1    | Even    | 20  | 10   | 120
4  | A1    | Even    | 26  | 5    | 150
5  | A1    | Even    | 28  | 5    | 155
6  | A1    | Even    | 32  | 5    | 160
7  | A1    | Odd     | 13  | 10   | 100
8  | A1    | Odd     | 17  | 10   | 110
9  | A1    | Odd     | 19  | 10   | 120
10 | A1    | Odd     | 23  | 5    | 150
11 | A1    | Odd     | 25  | 5    | 155
12 | A1    | Odd     | 29  | 5    | 160

I need a select query and update query. What its doing is there are already some Y-Axis Number been filled (at the start of the Odd/Even) then I need to take the previous row's Y-Axis column's value and adds to the current rows's size which = to current Y-Axis. Needs to keep doing it until it finds another Y-Axis has the value it skips the calculation and next row is using that number.

My thinking process is this:

Id will definitely be used, however, the Id is not sequence as shown my example

so I need to have

ROW_Number OVER (PARTITION BY Aisle,OddEven,Bay Order BY Aisle,OddEven,Bay)

Then some kind of JOIN the same table but the ON is T1.RN = T2.RN - 1

Where I am stuck is but the first row has not previous value it will try to update that value.

Anyone have an idea for SQL Query 2008 for Select and Update will be greatly appreciated! Thanks.

Answer 1

You seem to want a cumulative sum. This would be easier in SQL Server 2012+. You can do this in SQL Server 2008 using outer apply:

select t.*, cume_value
from t outer apply
     (select sum(size) + sum(yaxis) as cume_value
      from t t2
      where t2.aisle = t.aisle and t2.oddeven = t.oddeven and
            t2.bay < t.bay
     ) t2;

Answer 2

I gotta leave my computer so update query should be easy to move on from here.

Below is the select query;

select  row_number() over (order by oddeven,bay) id,
    Aisle,
    OddEven,
    Bay,
    Size,
    max(ISNULL([Y-Axis],0)) over (partition by Aisle, OddEven,Size order by bay) 
        + sum(CASE WHEN [Y-Axis] is null THEN Size ELSE 0 END) over (partition by Aisle,OddEven,size order by Bay) as [Y-Axis]
from oddseven
order by id

Answer 3

A little more difficult on 2008, but I think this is what you are looking for

Declare @Table table (Id int,Aisle varchar(25),OddEven varchar(25),Bay int,Size int,[Y-Axis] int)
Insert Into @Table values
(3,'A1','Even',14,10 ,100),
(1,'A1','Even',16,10 ,0),
(6,'A1','Even',20,10 ,0),
(12,'A1','Even',26,5,150),
(10,'A1','Even',28,5,0),
(11,'A1','Even',32,5,0),
(2,'A1','Odd',13,10 ,100),
(5,'A1','Odd',17,10 ,0),
(4,'A1','Odd',19,10 ,0),
(9,'A1','Odd',23,5,150),
(7,'A1','Odd',25,5,0),
(8,'A1','Odd',29,5,0)

;with cteBase as (
      Select *
            ,IDNew=Row_Number() over (Order By Aisle,Bay) 
            ,RowNr=Row_Number() over (Order By Aisle,OddEven,Bay) 
       From  @Table
 )
 , cteGroup as (Select TmpRowNr=RowNr,GrpNr=Row_Number() over (Order By RowNr) from cteBase where [Y-Axis]>0)
 , cteFinal as (
                Select A.*
                      ,GrpNr = (Select max(GrpNr) from cteGroup Where TmpRowNr<=RowNr)
                 From  cteBase A
                )
Select ID=Row_Number() over (Order By A.OddEven,A.Bay)
      ,A.Aisle
      ,A.OddEven
      ,A.Bay
      ,A.Size
      ,[Y-Axis] = Sum(case when B.[Y-Axis]>0 then B.[Y-Axis] else B.Size end) 
 From  cteFinal A
 Join  cteFinal B on (B.RowNr<=A.RowNr and A.GrpNr=B.GrpNr)
 Group By
       A.IDNew
      ,A.Aisle
      ,A.OddEven
      ,A.Bay
      ,A.Size 
 Order By A.OddEven,A.Bay

Returns

ID  Aisle   OddEven Bay Size    Y-Axis
1   A1      Even    14  10      100
2   A1      Even    16  10      110
3   A1      Even    20  10      120
4   A1      Even    26  5       150
5   A1      Even    28  5       155
6   A1      Even    32  5       160
7   A1      Odd     13  10      100
8   A1      Odd     17  10      110
9   A1      Odd     19  10      120
10  A1      Odd     23  5       150
11  A1      Odd     25  5       155
12  A1      Odd     29  5       160