Search code examples
sql-serverdataframeetldata-transfer

Data transformation pretty tough question

enter image description here

I want to select the data and added a new column called 'check' based on the following rules:

  1. for each 'consumer' column, if the consumer doesn't have a master_consumer value (showing 'null' in the master_consumer) AND if its bill_group is 'Consumer' then its 'check' is the same as the value in the 'Consumer_scope' column
  2. for each 'Master_Consumer' column, if it is not null and the number also is in one of the consumer column, if the 'Consumer_scope' is 'out of scope' then all the consumers belong to this 'Master_Consumer' is 'out of scope', for example, number 7 is a master_consumer and a consumer, number 8 and 9 are consumer, although 8 and 9 are 'In Scope', they belong to number 7, so their 'check' value is still 'Out of Scope' same as number 7
  3. if a consumer's product contains 'For Dairy' and this consumer belongs to a master_consumer, all the 'Check' values within the Master_Consumer is 'Out of Scope' My ideal output is as below:

enter image description here

I have created code but very very slow, because this is a sample data, so just wondering could anyone help with it please? Thanks

Solution 
  • case
    when Master_Consumer is null then
        case when Bill_group = 'Consumer'
            then Consumer_scope
            else '' -- ??
        end
    when 
        min(case when Master_Consumer = Consumer and Consumer_scope = 'Out of scope' then 1 end)
            over (partition by Master_Consumer) = 1
        or Product like '%For Dairy%'
        then 'Out of scope'
    else Consumer_scope -- ??
end