TSQL: Trying to Streamline/Optimise my Stored-Procedure

Question

Good afternoon everyone,

I have written a Stored Procedure that as it is works and executes relatively fast as it is just doing fairly simple calculation. I guess you could say my issue with the procedure itself is the number of repeated 'CASE Statement' both in the SELECT and ORDER BY clause. My TSQL knowledge is still fairly N00bish as I am still a 'P' plate at best. Is it possible to streamline my code further so that I only have the CASE WHEN calculation appearing once and I can continue to utilize it in multiple places? I believe this will be better for future proofing as well as I only need to make the changes at the root statement without having to change it in multiple locations!

@Officer_Name is a variable passed in from the user interface. As you can see both the F_YEAR (Fiscal Year), F_Quarter (Fiscal Quarter) Field calculation is repeated again in the Order By part of the statement and I am wondering if that can be avoided :) Many many thanks in advance for rescuing this damoiseau in distress, I hope there is a generous expert with a greater level of TSQL out there who can do me this favor! Much appreciated.

    BEGIN

    SELECT  TOP (100) PERCENT 
    COUNT(DISTINCT(dbo.TableA.[Account ID])) AS Applications,
    SUM(CASE WHEN [Client Claims] LIKE '%claim%' THEN 1 ELSE 0 END) AS Main_Client,
    COUNT([TableA_ID]) AS Clients,
    (CASE
    WHEN [Finalised date] < '07/01/' +  CONVERT(VARCHAR(4), DATEPART(Year,GETDATE()) - 2) THEN 'PAST CASES'
    WHEN [Finalised date] BETWEEN '07/01/' +  CONVERT(VARCHAR(4), DATEPART(Year,GETDATE()) - 2)  AND '06/30/' +  CONVERT(VARCHAR(4), DATEPART(Year,GETDATE()) - 1) THEN 'YEAR'
    WHEN MONTH([Finalised date]) BETWEEN 1  AND 3  THEN ' Q3'
    WHEN MONTH([Finalised date]) BETWEEN 4  AND 6  THEN ' Q4'
    WHEN MONTH([Finalised date]) BETWEEN 7  AND 9  THEN ' Q1'
    WHEN MONTH([Finalised date]) BETWEEN 10 AND 12 THEN ' Q2'
    END) AS F_Quarter, 
    (CASE 
    WHEN [Finalised date] < '07/01/' +  CONVERT(VARCHAR(4), DATEPART(Year,GETDATE()) - 2) THEN CONVERT(VARCHAR(4), DATEPART(Year,GETDATE()) - 2) + ' & Older'
    WHEN MONTH([Finalised date]) BETWEEN 1  AND 6 THEN convert(char(4), YEAR([Finalised date]) - 0)
    WHEN MONTH([Finalised date]) BETWEEN 7  AND 12 THEN convert(char(4), YEAR([Finalised date]) + 1) 
    ELSE convert(char(4), YEAR([Finalised date])) 
    END) AS F_YEAR

FROM    dbo.TableB INNER JOIN
        dbo.TableA ON dbo.TableB.[Account ID] = dbo.TableA.[Account ID] LEFT OUTER JOIN
        dbo.Officers ON dbo.TableA.[Account Officer] = dbo.Officers.FullName

WHERE   [Case Officer] = @Officer_Name AND [Finalisation] IS NOT NULL

GROUP BY 
    (CASE 
    WHEN [Finalised date] < '07/01/' +  CONVERT(VARCHAR(4), DATEPART(Year,GETDATE()) - 2) THEN CONVERT(VARCHAR(4), DATEPART(Year,GETDATE()) - 2) + ' & Older'
    WHEN MONTH([Finalised date]) BETWEEN 1  AND 6 THEN convert(char(4), YEAR([Finalised date]) - 0)
    WHEN MONTH([Finalised date]) BETWEEN 7  AND 12 THEN convert(char(4), YEAR([Finalised date]) + 1) 
    ELSE convert(char(4), YEAR([Finalised date])) 
    END),
    (CASE
    WHEN [Finalised date] < '07/01/' +  CONVERT(VARCHAR(4), DATEPART(Year,GETDATE()) - 2) THEN 'PAST CASES'
    WHEN [Finalised date] BETWEEN '07/01/' +  CONVERT(VARCHAR(4), DATEPART(Year,GETDATE()) - 2)  AND '06/30/' +  CONVERT(VARCHAR(4), DATEPART(Year,GETDATE()) - 1) THEN 'YEAR'
    WHEN MONTH([Finalised date]) BETWEEN 1  AND 3  THEN ' Q3'
    WHEN MONTH([Finalised date]) BETWEEN 4  AND 6  THEN ' Q4'
    WHEN MONTH([Finalised date]) BETWEEN 7  AND 9  THEN ' Q1'
    WHEN MONTH([Finalised date]) BETWEEN 10 AND 12 THEN ' Q2'
    END)

ORDER BY F_YEAR DESC, F_Quarter
END

score 0 · Accepted Answer · answered Aug 09 '19 at 17:55

0

You can put your CASE expression in a CTE and refer to it multiple times by its alias in the query to follow. However, since your CASE expression for F_YEAR is different from the one for F_Quarter there is no way to only use one CASE expression for the entire query. In pseudo-code, you can do this:

WITH cte AS (
   SELECT ...
   , {CASE Expression for year} AS F_Year
   , {CASE Expression for quarter} AS F_Quarter
   FROM...
)
SELECT ... F_Year, F_Quarter
FROM ... WHERE ...
GROUP BY F_Year, F_Quarter ...

answered Aug 09 '19 at 17:55

Tab Alleman

31,483
7
36
52

Hi Tab, Thank you very much for the input, I have just tried the CTE. However it still won't let me drop the second Case When Statement in the GROUP BY clause. The Calculation needs to be grouped by the value output in the CASE WHEN statement hence my dilemma. I was wondering if there is a way to declare those CASE WHEN as a variable or some such so that I only have the full case when statement appearing once for future proofing and efficiency. – SeanY Aug 12 '19 at 03:45
Yes it will let you drop the CASE expressions in the GROUP BY clause, if you do it correctly. How did you do it? The GROUP BY needs to be in the main query; NOT in the CTE. – Tab Alleman Aug 12 '19 at 13:09
Hi Tab, I just figured out what you meant by doing it properly. I included the GROUP BY statement within the CTE hence the server wanted me to repeat both the CASE WHEN statement within the CTE as well. I have removed the GROUP BY statement from within the CTE to sit below as you have shown me in the example and it works perfectly! Really appreciate your input! you have effectively helped me streamlined at least 10 to 15 other stored procedure with your feedback! I will shout you a beer if the chance ever arises!! – SeanY Aug 13 '19 at 04:28

TSQL: Trying to Streamline/Optimise my Stored-Procedure

1 Answers1