Retriving data row that matches all conditions

Question

"Great Indian Sale Discount" would be applied if all the conditions are satisfied. context can have multiple context_subCategory values. Negative grouping means mandatory conditions. Positive grouping number means optional conditions but one of the optional conditions must be true.

"Great Indian Sale Discount" would be applied if

• context Party and subCategry partyLocation is not equal to (NOT=) Australia
• context Order and order Type = cash and one of the below is true (positive grouping Number)
• Customer context and subcategroy customer Name = Shubh
• Customer context and customer Id = 12
• Customer context and customer Location = Canberra

I am looking for clean approach for solving the problem. I need to select from multiple Discount Names that matches the conditions. subcategory and its values are incoming parameters i.e. party Location, Australia are coming as parameters.

my approach uses a lot of case statements. context are always same Party, Order or Customer, but sub category can be different for different discount Name.

below is code for optional case. similarly, I am having one mandatoryCount for mandatory options. if both optionalCount and mandatoryCount are > 0, I'll select that discount Name.

SET @sql  = 'SET @optionalCount = (select count(*) from #discountData where ' + (            
 SELECT CASE WHEN @context = 'PARTY'            
      THEN 'CONTEXT = ''' + @context + ''' and context_value= ' + (case statment for selecting subcategory )          
      WHEN @context = 'CUSTOMER'            
      THEN 'CONTEXT = ''' + @context + ''' and context_value= ' + (case statment for selecting subcategory)          
      WHEN @context = 'ORDER'            
      THEN 'CONTEXT = ''' + @context + ''' and context_value= ' + (case statment for selecting subcategory)             
      END            
) + ')'        

Is there any other better way of achieving the same?

Answer 1

This is a bit ugly but perhaps you can do something like this:

SELECT  [Discount Name], STRING_AGG(CASE WHEN asign = -1 THEN 'WHERE 1 = 1 ' ELSE ' AND(1 = 0 ' END + aggvalue + CASE WHEN asign = 1 THEN ')' ELSE '' END, '') within GROUP(ORDER BY asign)
FROM    (
    SELECT  [Discount Name]
    ,   SIGN([grouping number]) AS asign
    ,   STRING_AGG(CONCAT(CASE sign([grouping number]) WHEN 1 THEN ' OR ' ELSE 'AND ' END,'(context = ', quotename(context, ''''), ' AND ', quotename(context_subcategory), replace(operator, 'NOT=', '<>'), quotename(context_value, ''''), ')'), '') AS aggValue
    FROM
    (
        VALUES  (N'Great Indian Sale Discount', -1, N'Party', N'partyLocation', N'Australia', N'NOT=')
        ,   (N'Great Indian Sale Discount', -1, N'Order', N'ordertype', N'cash', N'=')
        ,   (N'Great Indian Sale Discount', 1, N'Customer', N'customerName', N'shubh', N'=')
        ,   (N'Great Indian Sale Discount', 2, N'Customer', N'customerid', N'12', N'=')
        ,   (N'Great Indian Sale Discount', 3, N'Customer', N'customer Location', N'Canberra', N'=')
    ) t ([Discount Name],[grouping number],context,context_subCategory,context_value,operator)
    
    GROUP BY sign([grouping number]), [Discount Name]
    ) x
GROUP BY [Discount Name]

You group per discount and sign of the grouping number, one for mandatory and one for optional parameters. Then you aggregate the conditions into a string and handle the ANDs and ORs.

Then in the outer aggregation you put them together and create the final WHERE condition.

The above outputs:

WHERE 1 = 1 AND (context = 'Order' AND [ordertype]='cash')AND (context = 'Party' AND [partyLocation]<>'Australia') AND(1 = 0 OR (context = 'Customer' AND [customerid]='12') OR (context = 'Customer' AND [customer Location]='Canberra') OR (context = 'Customer' AND [customerName]='shubh'))

It shouldn't be too hard to customize the rest.