CODEWITHSUNDEEP
sqlsql-serverwindow-functions
P. MAJ
P. MAJ

Reputation: 159

Creating a column based on existing values and applying logics to some - SQL Server

I don't really know what I had to write for the title, as I can not word it out in a single sentence - apologies in advance.

I have a table like this:

enter image description here

I need it to look like so:

enter image description here

Look at my code:

select 
    name,
    [sequence] = case
                    when right(left(name, 4), 1) = 'A'
                       then step
                    when right(left(name, 4), 1) = 'B'
                       then -- I'm stuck here. The logic needs to be the maximum of step when right(left(name, 4), 1) was A + step when it was B
                 end,
    value
from
    table 1
where 
    name like 'AM13%'
order by 
    sequence asc

I'm trying to apply the logic that if the first character after "AM13" was "A", then to bring the step, however if it was B, then add the step to the maximum of A and so on. so instead of having 1,2,3,4,1,2,3,1,2 i would have 1,2,3,4,5,6,7,8,9

I am so stuck!

Any help will be appreciated :)

Upvotes: 0

Views: 48

Answers (2)

P. MAJ
P. MAJ

Reputation: 159

so ok, I found a solution, and it works.. to a degree:

select 
    t1.name,
    [sequence] = case
                        when substring(t1.name, 5, 1) = 'A'
                            then t1.step
                        when substring(t1.name, 5, 1) = 'B'
                            then (select
                                      count(t2.name) --or max(t2.name)
                                  from table 1 as t2
                                  where t2.name like concat(LEFT(t1.name, 4), 'A%')) + t1.step
                        else t1.step
                 end,
    value
from
    table 1 as t1
/*where 
    name like 'AM13%'*/
order by 
    sequence asc

Upvotes: 0

GMB
GMB

Reputation: 222642

For this sample data, wouldn't row_number() just work?

select t.*,
    row_number() over(order by name, step) as sequence
from mytable t
order by name, step

Upvotes: 2

Related Questions