locked
How to use a CASE statement within a sub select RRS feed

  • Question

  • Hello everyone,

    I have been going nuts trying to figure out how to sum a set of revenue numbers for a given range of months IF  a month (independent of the months in the range is selected. The entire SQL is too long to paste into this so i will paste the part I am having trouble with. I will also include a partial paste of an Excel file that will have what I am looking for. This example is looking at only on producer (our sales reps are called "producers").

    Select NE.FiscalYear
    , NE.FiscalMonth
    , AcctPD
    , BeginDate
    , EmployeeID
    , ProducerName
    , SUM([Producer Revenue]) Revenue
    , JulyForecast = 
      CASE when AcctPD = 20207
    	(select sum([Producer Revenue]) FROM #Northeast where AcctPD between 201908 and 202004
    	and EmployeeID = '27179') 
    
    		
    From #NortheastSum NE
    -- FiscalYear in (@start,@End)
    --Where AcctPD = @AcctPD
    INNER JOIN rpt_vwAccountingPeriod dap on dap.AccountingPeriodCode = NE.AcctPD
    WHERE NE.FiscalYear > 2019
    AND ProducerCode  NOT IN ('ATL','DXD','KXC','KXH','LXD','MEX','SXA','WXH','1AT','1CH','1DN','1DU','CON','CHI','CAL','1HO','1HR'
    ,'1KC','1LA','1NY','1SF','1SL','1WD','COC','ZCC','SFO','WDC','STL','SJO','SFR','SAN','NYC','NEJ'
    ,'NAS','LOS','LON','KAN','HOU','HAR','FLA','DUN','DEN','DAL','MPH')  
    AND EmployeeID = '27179'
    
    Group by NE.FiscalYear
    , NE.FiscalMonth
    , AcctPD
    , BeginDate
    , EmployeeID
    , ProducerName

    The CASE statement is not working.

    Below is example of Excel file that shows the correct forecasted amount.

    Bailey correct forecast for July-September

    Please let me know if you have any questions or need any additional information.


    Don Fox

    Thursday, August 6, 2020 3:27 PM

Answers

  • WITH CTE_Forecast_By_Producer_For_July AS (
    	SELECT EmployeeID, SUM([Producer Revenue]) / 9 AS JulyForecast
    	FROM #Northeast 
    	WHERE AcctPD BETWEEN 201908 AND 202004
    	GROUP BY EmployeeID
    ),
    CTE_Forecast_By_Producer_For_August AS (
    	SELECT EmployeeID, SUM([Producer Revenue]) / 8 AS AugustForecast
    	FROM #Northeast 
    	WHERE AcctPD BETWEEN 201909 AND 202004
    	GROUP BY EmployeeID
    )
    
    Select 
    	  NE.FiscalYear
    	, NE.FiscalMonth
    	, AcctPD
    	, BeginDate
    	, EmployeeID
    	, ProducerName
    	, SUM([Producer Revenue]) Revenue
    	--, SUM([Agency Premium]) Premium
    	, CASE WHEN AcctPD = 202007 THEN ISNULL(JF.JulyForecast, 0) ELSE 0 END AS JulyForecast
    	, CASE WHEN AcctPD = 202008 THEN ISNULL(AF.AugustForecast, 0) ELSE 0 END AS AugForecast
    FROM #NortheastSum NE
    -- FiscalYear in (@start,@End)
    --Where AcctPD = @AcctPD
    INNER JOIN rpt_vwAccountingPeriod dap on dap.AccountingPeriodCode = NE.AcctPD
    LEFT JOIN CTE_Forecast_By_Producer_For_July AS JF ON NE.EmployeeID = JF.EmployeeID
    LEFT JOIN CTE_Forecast_By_Producer_For_August AS AF ON NE.EmployeeID = AF.EmployeeID
    WHERE NE.FiscalYear > 2019
    AND ProducerCode  NOT IN ('ATL','DXD','KXC','KXH','LXD','MEX','SXA','WXH','1AT','1CH','1DN','1DU','CON','CHI','CAL','1HO','1HR'
    ,'1KC','1LA','1NY','1SF','1SL','1WD','COC','ZCC','SFO','WDC','STL','SJO','SFR','SAN','NYC','NEJ'
    ,'NAS','LOS','LON','KAN','HOU','HAR','FLA','DUN','DEN','DAL','MPH')  
    AND EmployeeID = '27179'
    
    -- AcctPD between 201905 and 201912
    Group by NE.FiscalYear
    , NE.FiscalMonth
    , AcctPD
    , BeginDate
    , EmployeeID
    , ProducerName


    A Fan of SSIS, SSRS and SSAS

    • Marked as answer by Barely_There Friday, August 7, 2020 1:49 PM
    Thursday, August 6, 2020 9:56 PM

All replies

  • Should AcctPD = 20207 be AcctPD = 202007? You do not have 20207 in your sample data.

    A Fan of SSIS, SSRS and SSAS

    Thursday, August 6, 2020 3:36 PM
  • You're right, it should be 202007. I am able to do forecast for one producer (Bailey) but the output is ugly and only works for one producer at a time.  Here is the revised code:

    Select NE.FiscalYear
    , NE.FiscalMonth
    , AcctPD
    , BeginDate
    , EmployeeID
    , ProducerName
    , SUM([Producer Revenue]) Revenue
    --, SUM([Agency Premium]) Premium
    , JulyForecast = 
      --CASE when AcctPD = 20207
    	(select sum([Producer Revenue]) FROM #Northeast where AcctPD between 201908 and 202004
    	and EmployeeID = '27179')/9
    , AugForecast = 
    	(select sum([Producer Revenue]) FROM #Northeast where AcctPD between 201909 and 202004
    	and EmployeeID = '27179')/8 
    
    		
    From #NortheastSum NE
    -- FiscalYear in (@start,@End)
    --Where AcctPD = @AcctPD
    INNER JOIN rpt_vwAccountingPeriod dap on dap.AccountingPeriodCode = NE.AcctPD
    WHERE NE.FiscalYear > 2019
    AND ProducerCode  NOT IN ('ATL','DXD','KXC','KXH','LXD','MEX','SXA','WXH','1AT','1CH','1DN','1DU','CON','CHI','CAL','1HO','1HR'
    ,'1KC','1LA','1NY','1SF','1SL','1WD','COC','ZCC','SFO','WDC','STL','SJO','SFR','SAN','NYC','NEJ'
    ,'NAS','LOS','LON','KAN','HOU','HAR','FLA','DUN','DEN','DAL','MPH')  
    AND EmployeeID = '27179'
    
    -- AcctPD between 201905 and 201912
    Group by NE.FiscalYear
    , NE.FiscalMonth
    , AcctPD
    , BeginDate
    , EmployeeID
    , ProducerName

    The output looks like this:


    Don Fox

    Thursday, August 6, 2020 6:58 PM
  • Did you try this:

    JulyForecast = CASE WHEN AcctPD = 202007 THEN (SELECT SUM([Producer Revenue]) FROM #Northeast WHERE AcctPD BETWEEN 201908 AND 202004 AND EmployeeID = '27179')/9 ELSE 0 END


    A Fan of SSIS, SSRS and SSAS


    Thursday, August 6, 2020 7:14 PM
  • The results look pretty good. How could I set this up for multiple producers (around 100)?

    Don


    Don Fox

    Thursday, August 6, 2020 8:37 PM
  • Is the EmployeeID 27179 for the Producer Bailey, George? If yes, remove or comment out "AND EmployeeID = '27179'".

    A Fan of SSIS, SSRS and SSAS

    Thursday, August 6, 2020 9:11 PM
  • Because the CASE statements still have the EmployeeID, the forecasted amounts will be for Bailey but if I remove the "and EmployeeID = '27179'" from the CASE statements then the amounts will be for everyone. Any suggestions?

    Don Fox


    Thursday, August 6, 2020 9:33 PM
  • WITH CTE_Forecast_By_Producer_For_July AS (
    	SELECT EmployeeID, SUM([Producer Revenue]) / 9 AS JulyForecast
    	FROM #Northeast 
    	WHERE AcctPD BETWEEN 201908 AND 202004
    	GROUP BY EmployeeID
    ),
    CTE_Forecast_By_Producer_For_August AS (
    	SELECT EmployeeID, SUM([Producer Revenue]) / 8 AS AugustForecast
    	FROM #Northeast 
    	WHERE AcctPD BETWEEN 201909 AND 202004
    	GROUP BY EmployeeID
    )
    
    Select 
    	  NE.FiscalYear
    	, NE.FiscalMonth
    	, AcctPD
    	, BeginDate
    	, EmployeeID
    	, ProducerName
    	, SUM([Producer Revenue]) Revenue
    	--, SUM([Agency Premium]) Premium
    	, CASE WHEN AcctPD = 202007 THEN ISNULL(JF.JulyForecast, 0) ELSE 0 END AS JulyForecast
    	, CASE WHEN AcctPD = 202008 THEN ISNULL(AF.AugustForecast, 0) ELSE 0 END AS AugForecast
    FROM #NortheastSum NE
    -- FiscalYear in (@start,@End)
    --Where AcctPD = @AcctPD
    INNER JOIN rpt_vwAccountingPeriod dap on dap.AccountingPeriodCode = NE.AcctPD
    LEFT JOIN CTE_Forecast_By_Producer_For_July AS JF ON NE.EmployeeID = JF.EmployeeID
    LEFT JOIN CTE_Forecast_By_Producer_For_August AS AF ON NE.EmployeeID = AF.EmployeeID
    WHERE NE.FiscalYear > 2019
    AND ProducerCode  NOT IN ('ATL','DXD','KXC','KXH','LXD','MEX','SXA','WXH','1AT','1CH','1DN','1DU','CON','CHI','CAL','1HO','1HR'
    ,'1KC','1LA','1NY','1SF','1SL','1WD','COC','ZCC','SFO','WDC','STL','SJO','SFR','SAN','NYC','NEJ'
    ,'NAS','LOS','LON','KAN','HOU','HAR','FLA','DUN','DEN','DAL','MPH')  
    AND EmployeeID = '27179'
    
    -- AcctPD between 201905 and 201912
    Group by NE.FiscalYear
    , NE.FiscalMonth
    , AcctPD
    , BeginDate
    , EmployeeID
    , ProducerName


    A Fan of SSIS, SSRS and SSAS

    • Marked as answer by Barely_There Friday, August 7, 2020 1:49 PM
    Thursday, August 6, 2020 9:56 PM
  • inserting the code as show

    /**Select NE.FiscalYear
    --, NE.FiscalMonth
    , AcctPD
    , BeginDate
    , EmployeeID
    , ProducerName
    , SUM([Producer Revenue]) Revenue **/
    
    WITH CTE_Forecast_By_Producer_For_July AS (
    	SELECT EmployeeID, SUM([Producer Revenue]) / 9 AS JulyForecast
    	FROM #Northeast 
    	WHERE AcctPD BETWEEN 201908 AND 202004
    	GROUP BY EmployeeID
    ),
    CTE_Forecast_By_Producer_For_August AS (
    	SELECT EmployeeID, SUM([Producer Revenue]) / 8 AS AugustForecast
    	FROM #Northeast 
    	WHERE AcctPD BETWEEN 201909 AND 202004
    	GROUP BY EmployeeID
    )
    
    Select 
    	  NE.FiscalYear
    	, NE.FiscalMonth
    	, AcctPD
    	, BeginDate
    	, NE.EmployeeID
    	, ProducerName
    	, SUM([Producer Revenue]) Revenue
    	--, SUM([Agency Premium]) Premium
    	, CASE WHEN AcctPD = 202007 THEN ISNULL(JF.JulyForecast, 0) ELSE 0 END AS JulyForecast
    	, CASE WHEN AcctPD = 202008 THEN ISNULL(AF.AugustForecast, 0) ELSE 0 END AS AugForecast
    FROM #NortheastSum NE
    -- FiscalYear in (@start,@End)
    --Where AcctPD = @AcctPD
    INNER JOIN rpt_vwAccountingPeriod dap on dap.AccountingPeriodCode = NE.AcctPD
    LEFT JOIN CTE_Forecast_By_Producer_For_July AS JF ON NE.EmployeeID = JF.EmployeeID
    LEFT JOIN CTE_Forecast_By_Producer_For_August AS AF ON NE.EmployeeID = AF.EmployeeID
    WHERE NE.FiscalYear > 2019
    /**AND ProducerCode  NOT IN ('ATL','DXD','KXC','KXH','LXD','MEX','SXA','WXH','1AT','1CH','1DN','1DU','CON','CHI','CAL','1HO','1HR'
    ,'1KC','1LA','1NY','1SF','1SL','1WD','COC','ZCC','SFO','WDC','STL','SJO','SFR','SAN','NYC','NEJ'
    ,'NAS','LOS','LON','KAN','HOU','HAR','FLA','DUN','DEN','DAL','MPH')  **/
    AND NE.EmployeeID = '27179'
    
    -- AcctPD between 201905 and 201912
    Group by NE.FiscalYear
    , NE.FiscalMonth
    , AcctPD
    , BeginDate
    , ne.EmployeeID
    , ProducerName
    
    Order by 4

    It gave me the following error: 

    Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
    Msg 102, Level 15, State 1, Line 643
    Incorrect syntax near ','.

    I uncommented the code block starting with "Select NE.FiscalYear..." and got the same error. Do you see what I am doing wrong?


    Don Fox

    Friday, August 7, 2020 12:38 AM
  • Thank you! I made some minor modifications and it worked. I could not have figured this out without your help! I took out the filter on EmployeeID and added the Forecast months as part of the "Group By"

    Here is the code for anyone else who can use it:

    WITH CTE_Forecast_By_Producer_For_July AS (
    	SELECT EmployeeID, SUM([Producer Revenue]) / 9 AS JulyForecast
    	FROM #Northeast 
    	WHERE AcctPD BETWEEN 201908 AND 202004
    	GROUP BY EmployeeID
    	),
    CTE_Forecast_By_Producer_For_August AS (
    	SELECT EmployeeID, SUM([Producer Revenue]) / 8 AS AugustForecast
    	FROM #Northeast 
    	WHERE AcctPD BETWEEN 201909 AND 202004
    	GROUP BY EmployeeID
    )
    
    Select 
    	  NE.FiscalYear
    	, NE.FiscalMonth
    	, AcctPD
    	, BeginDate
    	, NE.EmployeeID
    	, ProducerName
    	, SUM([Producer Revenue]) Revenue
    	--, SUM([Agency Premium]) Premium
    	, CASE WHEN AcctPD = 202007 THEN ISNULL(JF.JulyForecast, 0) ELSE 0 END AS JulyForecast
    	, CASE WHEN AcctPD = 202008 THEN ISNULL(AF.AugustForecast, 0) ELSE 0 END AS AugForecast
    FROM #NortheastSum NE
    -- FiscalYear in (@start,@End)
    --Where AcctPD = @AcctPD
    INNER JOIN rpt_vwAccountingPeriod dap on dap.AccountingPeriodCode = NE.AcctPD
    LEFT JOIN CTE_Forecast_By_Producer_For_July AS JF ON NE.EmployeeID = JF.EmployeeID
    LEFT JOIN CTE_Forecast_By_Producer_For_August AS AF ON NE.EmployeeID = AF.EmployeeID
    WHERE NE.FiscalYear > 2019
    /**AND ProducerCode  NOT IN ('ATL','DXD','KXC','KXH','LXD','MEX','SXA','WXH','1AT','1CH','1DN','1DU','CON','CHI','CAL','1HO','1HR'
    ,'1KC','1LA','1NY','1SF','1SL','1WD','COC','ZCC','SFO','WDC','STL','SJO','SFR','SAN','NYC','NEJ'
    ,'NAS','LOS','LON','KAN','HOU','HAR','FLA','DUN','DEN','DAL','MPH')  **/
    --AND NE.EmployeeID = '27179'
    
    -- AcctPD between 201905 and 201912
    Group by NE.FiscalYear
    , NE.FiscalMonth
    , AcctPD
    , BeginDate
    , ne.EmployeeID
    , ProducerName
    , JulyForecast
    , AugustForecast
    
    Order by 6,3
    


    Don Fox

    Friday, August 7, 2020 1:52 PM