Compare the rows value in a single column and update the flag accordingly in SQL-Server table

I have one table as below

    A   B   C   D   E
2471    D471  0   24.00   1
2471    D471  0   353.50  1
2471    D471  1   211.00  1

2471    E471  1   343.00  1
2471    E471  0   56.00   1
2471    E471  0   177.06  1
2471    E471  0   9.31    1

2471    F471  0   10.31   1
2471    F471  1   10.31   1

I need to group on A,B and C and need to sum the value of E, example table below how it looks after summing up

    A   B   C   E   SumValue
2471    D471  0   1 377.500000
2471    D471  1   1 211.000000

2471    E471  1   1 343.000000
2471    E471  0   1 242.370000

2471    F471  0   1 10.31
2471    F471  1   1 10.31

I need to compare the SumValue by grouping A,B,C of first row which C has 0 with the second row which C has 1 and which ever has the lowest value those records should come as 1 in E column rest of the records should come as 0 in E column. If the SumValue is same whereas in the last two rows then E column should come as 1 where C is 1 and E column should come as 0 where C is 0.

The output I need is like this…All I need to do in this scenario is updating E column..

    A   B   C   D   E
2471    D471  0   24.00   0
2471    D471  0   353.50  0
2471    D471  1   211.00  1

2471    E471  1   343.00  0
2471    E471  0   56.00   1
2471    E471  0   177.06  1
2471    E471  0   9.31    1

2471    F471  0   10.31   0
2471    F471  1   10.31   1

Scripts to create table and insert data

CREATE TABLE tablename
    ([A] int, [B] varchar(10), [C] int, [D] decimal(10, 2), [E] int)
;

INSERT INTO tablename
    ([A], [B], [C], [D], [E])
VALUES
    (2471, 'D471', 0, 24.00, 1),
    (2471, 'D471', 0, 353.50, 1),
    (2471, 'D471', 1, 211.00, 1),
    (2471, 'E471', 1, 343.00, 1),
    (2471, 'E471', 0, 56.00, 1),
    (2471, 'E471', 0, 177.06, 1),
    (2471, 'E471', 0, 9.31, 1),
    (2471, 'F471', 0, 10.31, 1),
    (2471, 'F471', 1, 10.31, 1)
;

If you have any questions on this query, please let me know

Answer

If I understand correctly:

with toupdate as (
      select t.*,
             dense_rank() over (partition by a, b order by sum_d, c desc) as seqnum
      from (select t.*, sum(d) over (partition by a, b, c) as sum_d
            from tablename t
           ) t
     )
update toupdate
    set e = (case when seqnum = 1 then 1 else 0 end);

Advertisement

Answer