Dynamically assigning value to variables inside select statement-CodePudding

I've simplified the example for brevity.

I have a query that takes the total of 'agreed' and 'completed' sales figures for each staff member, and then calculates 'red', 'amber' and 'green' values for those people based on an arbitrary figure. In this example, we'll say 1m = red, 1.25m = amber and 1.5m = green.

Currently, the 1m, 1.25m and 1.5m values are hard-coded in the stored procedure. The main code is as follows:

DECLARE @redtarget int = '1000000'
DECLARE @ambtarget int = '1249999'
DECLARE @gretarget int = '1500000'    

SELECT
         name
         ,staff_id
         ,potential_sales_total
         ,confirmed_sales_total
         ,target_confirmed_figure
         ,case 
              WHEN confirmed_sales_total < @redtarget THEN 'Red'
              WHEN confirmed_sales_total BETWEEN @redtarget AND @ambtarget THEN 'Amber'
              WHEN confirmed_sales_total >= @gretarget THEN 'Green'
         END AS "Level"
    
    FROM
         [Sales_Figures]

However, some staff have custom sales targets, and this value is also contained within Sales_Figures. There aren't many occasions where this field is populated, but when it is then the red, amber and green values need to shift to account for the new figures. The threshold for 'Red' is the target_confirmed_figure * 0.667, 'Amber' is target_confirmed_figure * 0.833, and 'Green' is simply target_confirmed_figure itself.

I'm very close to getting this to work myself, but I keep getting lost in it and would very much appreciate some pointers. I think it's to do with more parameters in the case statement, such as if target_confirmed_figure is NULL, but it seems to get complicated very quickly. I'm an enthusiastic amateur only! There is the odd question on SO that could be vaguely similar, but they seem different enough to be of little help here.

Thanks

CodePudding user response：

With limited info on your table and desire results, I'm not sure this is exactly correct, but I think this will accomplish what you are trying to do

DECLARE @redtarget int = 1000000
    ,@ambtarget int = 1249999
    ,@gretarget int = 1500000    

SELECT
     name
    ,staff_id
    ,potential_sales_total
    ,confirmed_sales_total
    ,target_confirmed_figure
    ,C.* /*Showing numbers for reference*/
    ,case 
        WHEN confirmed_sales_total < C.RedTarget THEN 'Red'
        WHEN confirmed_sales_total BETWEEN C.RedTarget AND C.AmbTarget THEN 'Amber'
        /*Do you want to categorize values between AmbTarget and GreTarget?*/
        WHEN confirmed_sales_total >= C.GreTarget THEN 'Green'
    END AS "Level"
FROM [Sales_Figures] AS S
/*Uses value in column Sales_Figure, unless it's null, then uses variables from above*/
CROSS APPLY (
    SELECT RedTarget = ISNULL(ROUND(target_confirmed_figure*0.667,0),@redtarget)
            ,AmbTarget = ISNULL(ROUND(target_confirmed_figure*0.833,0),@ambtarget)
            ,GreTarget = ISNULL(target_confirmed_figure,@gretarget)
) AS C

CodePudding user response：

In the end - that may not be the most elegant method - I decided to create a union statement. One statement showing only the default targets and applying the red, amber and green targets to those people. The second one showing only where the ones with custom targets are, and applying the red, amber and green criteria based on that value instead:

SELECT
         name
         ,staff_id
         ,potential_sales_total
         ,confirmed_sales_total
         ,target_confirmed_figure
         ,case 
              WHEN confirmed_sales_total < (target_confirmed_figure * 0.667) THEN 'Red'
              WHEN confirmed_sales_total > target_confirmed_figure THEN 'Green'
              ELSE 'Amber'
         END AS "Level"
FROM
         [Sales_Figures]
WHERE ISNULL(target_confirmed_figure,'yes') = 'yes'

UNION

SELECT
             name
             ,staff_id
             ,potential_sales_total
             ,confirmed_sales_total
             ,target_confirmed_figure
             ,case 
                  WHEN confirmed_sales_total < @redtarget THEN 'Red'
                  WHEN confirmed_sales_total >= @gretarget THEN 'Green'
                  ELSE 'Amber'
             END AS "Level"
FROM
      [Sales_Figures]
WHERE ISNULL(target_confirmed_figure,'yes') <> 'yes'

This seems to have done the job, and I get output that shows both the default and custom target figures in one table.

Thanks to everyone who assisted!!