How to populate a column with Yes or No based on t

2019-08-17 07:41发布

问题:

I am trying to populate Yes or No in the column Move, based on a range of values in the column 'Variance' that is attributable to 3 products in the same month for the same customer.

In the picture titled "Expected Example Result", the Variance column for Customer1 and in Month 3, you can see that Customer1 spent $22.24 less on product1, $655.53 less on product2 and spent $319.28 more on product3 meaning they moved money away from product1 and 2 and put into 3.

The problem is I am mainly concerned with money movement from product3 to product2 and 1 and not how money is moved to product3.

The code below is a minimal reproducible working example that is partly modified from a previous question I asked here.

Need help converting an excel if and sumif formula to my SQL Server code

Basically the code says if the max of variance is positive and the min is negative then tag "yes" otherwise "no". There are some scenarios where we do not want "yes" even if it is the max variance like if product3 is the max variance and is positive or if product2 is negative we always want "no".

Otherwise the code works except when product1 is negative and product3 is positive, it tags product1 with "yes" and I can't figure out how to override that and tag it with "no".

I tried

when Variance < 0 and Product = 'Product1' 
and Max(Variance) over (partition by Customer, Mnth) > 0 
and Product = 'Product3' 
then 'No'

Also tried

when Variance < 0 and Product = 'Product1' 
and Variance > 0 and Product = 'Product3' 
then 'No'

Hope I haven't over explained but let me know if I need to clarify anything.

with

baseData as 

            (

            Select  Customer, Product,  Month(TransactionDate) as Mnth, sum(TransactionCharges + OtherCharges) as Charges,

                    case when Year(TransactionDate) = 2018 then 'prior'
                    else 'curr' 
                    end as Status

            From Storedfunction1 ('01-01-2018','12-31-2019')
                    where Customer <> 'internal'   --EXCLUDE INTERNAL CUSTOMERS
                    and Product in ('Product1', 'Product2', 'Product3')                     
                    and Month(TransactionDate) < 5

            group by Customer, Product, Month(TransactionDate), Year(TransactionDate), TransactionCharges, OtherCharges

            ),

getVariances as 
            (
            Select Customer, Product, Mnth,

                        isnull(sum(case when Status = 'curr' then Charges end),0) - isnull(sum(case when Status = 'prior' then Charges end),0)  as Variance, 
            case  

                when    isnull(sum(case when Status = 'prior' then Charges end),0) = 0 and isnull(sum(case when Status = 'curr' then Charges end),0) > 0 then 'New'
                when    isnull(sum(case when Status = 'prior' then Charges end),0) >= 0 and isnull(sum(case when Status = 'curr' then Charges end),0) <= 0 then 'Terminated'
                else 'Current'

            end as Standing,

            case  

                when    isnull(sum(case when Status = 'prior' then Charges end),0) > 0 and isnull(sum(case when Status = 'curr' then Charges end),0) - isnull(sum(case when Status = 'prior' then Charges end),0) > 0 then 'Growth'
                when    isnull(sum(case when Status = 'prior' then Charges end),0) > 0 and isnull(sum(case when Status = 'curr' then Charges end),0) - isnull(sum(case when Status = 'prior' then Charges end),0) < 0 then 'Attrited'
                when    isnull(sum(case when Status = 'prior' then Charges end),0) >= 0 and isnull(sum(case when Status = 'curr' then Charges end),0) - isnull(sum(case when Status = 'prior' then Charges end),0) = 0 then 'Static'
                when    isnull(sum(case when Status = 'prior' then Charges end),0) < 0 and isnull(sum(case when Status = 'curr' then Charges end),0) - isnull(sum(case when Status = 'prior' then Charges end),0) > 0 then 'Growth'
                when    isnull(sum(case when Status = 'prior' then Charges end),0) <= 0 and isnull(sum(case when Status = 'curr' then Charges end),0) - isnull(sum(case when Status = 'prior' then Charges end),0) < 0 then 'Attrited'
                else 'NA'

            end as GrowOrAttrit

            From baseData

            group by Customer, Product, Mnth

             )

            Select *, 

                case    

                    when GrowOrAttrit = 'Static' then 'No'                                      
                    when Variance < 0 and Product = 'Product2' then 'No'                        
                    when Min(Variance) over (partition by Customer, Mnth) > 0 and Max(Variance) over (partition by Customer, Mnth) > 0 then 'No'                                                                        
                    When Max(Variance) over (partition by Customer, Mnth) > 0 and Min(Variance) over (partition by Customer, Mnth) < 0 and Product = 'Product2' then 'Yes'
                    when Variance >= 0 and Standing = 'New' then 'No'
                    when Variance >= 0 then 'No'
                    when Max(Variance) over (partition by Customer, Variance) > 0 and Product = 'Product1' then 'No'
                    when Max(Variance) over (partition by Customer, Mnth) > 0 then 'Yes'
                    when Max(Variance) over (partition by Customer, Mnth) > 0 and Product = 'Product1' then 'No'

                    else 'No'

            end as Move

            From getVariances       

            order by 1,2,3

Expected Example Result

Actual Result