SQL Function for updating column with values-CodePudding

Those who have helped me before, i tend to use SAS9.4 a lot for my day to day work, however there are times when i need to use SQL Server

There is a output table i have with 2 variables (attached output.csv) output table

ID, GROUP, DATE

The table has 830 rows: 330 have a "C" group 150 have a "A" group 50 have a "B" group

the remaining 300 have group as "TEMP"

within SQL i do not now how to programatically work out the total volume of A B C. The aim is to update "TEMP" column to ensure there is an Equal amount of "A" and "B" totalling 250 of each (the remainder of the total count)

so the table totals

330 have a "C" group 250 have a "A" group 250 have a "B" group

CodePudding user response：

You want to proportion the "temp" to get equal amounts of "A" and "B".

So, the idea is to count up everything in A, B, and Temp and divide by 2. That is the final group size. Then you can use arithmetic to allocate the rows in Temp to the two groups:

select t.*,
       (case when seqnum   a_cnt <= final_group_size then 'A' else 'B' end) as allocated_group
from (select t.*, row_number() over (order by newid()) as seqnum
      from t
      where group = 'Temp'
     ) t cross join
     (select (cnt_a   cnt_b   cnt_temp) / 2 as final_group_size,
             g.*
      from (select sum(case when group = 'A' then 1 else 0 end) as cnt_a,
                   sum(case when group = 'B' then 1 else 0 end) as cnt_b,
                   sum(case when group = 'Temp' then 1 else 0 end) as cnt_temp
            from t
           ) g
     ) g

SQL Server makes it easy to put this into an update:

with toupdate as (
      select t.*,
             (case when seqnum   a_cnt <= final_group_size then 'A' else 'B' end) as allocated_group
      from (select t.*, row_number() over (order by newid()) as seqnum
            from t
            where group = 'Temp'
           ) t cross join
           (select (cnt_a   cnt_b   cnt_temp) / 2 as final_group_size,
                   g.*
            from (select sum(case when group = 'A' then 1 else 0 end) as cnt_a,
                         sum(case when group = 'B' then 1 else 0 end) as cnt_b,
                         sum(case when group = 'Temp' then 1 else 0 end) as cnt_temp
                  from t
                 ) g
           ) g
      )
update toupdate
    set group = allocated_group;

CodePudding user response：

I'd go with a top 250 update style approach

update top (250) [TableName] set Group = 'A' where exists (Select * from [TableName] t2 where t2.id = [TableName].id order by newid()) and Group = 'Temp'

update top (250) [TableName] set Group = 'B' where exists (Select * from [TableName] t2 where t2.id = [TableName].id order by newid()) and Group = 'Temp'