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
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 ...