Financial report query

Question

I have been tasked with creating financial reports. I am looking for any suggestions on improving performance, readability/formatting or best practices.

/*setting dummy parameters */
SET DATEFORMAT dmy
DECLARE @startProduct NVARCHAR = ''
DECLARE @endProduct NVARCHAR = 'zzzzzzzzzzzzzzzzzzzzzz'
DECLARE @startGroup NVARCHAR = ''
DECLARE @endGroup NVARCHAR = 'zzzzzzzzzzzzzzzzzz'
DECLARE @startDepartment NVARCHAR = ''
DECLARE @endDepartment NVARCHAR = 'zzzzzzzzzzzzzzzzzzzzzzz'
DECLARE @startDate DATE = '01/05/2000'
DECLARE @endDate DATE = CAST('31/05/2012' AS DATE)
DECLARE @StartTime TIME = '08:00:00'
DECLARE @endTime TIME = '17:00:00'
DECLARE @grouping INT = 0
DECLARE @site INT = 21
/*
ProductCode = 0
ProductGroup = 1
Department = 2
Pump = 3
Date = 4
*/
SELECT
 CASE @grouping
 WHEN 0 THEN P.Code
 WHEN 1 THEN P.[Group]
 WHEN 2 THEN P.Department
 WHEN 3 THEN P.[Description]
 WHEN 4 THEN CONVERT(VARCHAR,RPS.SalesDateTime,103)
 END AS GroupCode, 
 CASE @grouping
 WHEN 0 THEN P.[Description]
 WHEN 1 THEN PG.[Description]
 WHEN 2 THEN PD.[Description]
 WHEN 3 THEN 'PUMP: ' + CONVERT(VARCHAR,RPS.PumpId) +' HOSE: ' + CONVERT(VARCHAR,RPS.HoseId)
 WHEN 4 THEN CONVERT(VARCHAR(MAX),CAST(FLOOR(CAST(RPS.SalesDateTime AS FLOAT)) AS DATETIME))
 END AS [GroupDescription],
 SUM(RPS.Quantity) AS Quantity,
 SUM( CASE
 WHEN P.GSTable = 1 THEN RPS.TotalAmount / 1.1
 ELSE RPS.TotalAmount
 END) AS AmountX,
 SUM(CASE
 WHEN P.GSTable = 1 THEN (rps.TotalAmount * .10)
 ELSE 0 END
 ) AS GST,
 SUM(RPS.UnitLastCost * RPS.Quantity) AS CostXgst,
 SUM(RPS.TotalAmount) AS AmountInc,
 FLOOR(ROUND(((1 -
 (
 SUM(RPS.UnitLastCost * RPS.Quantity)
 /
 NULLIF(SUM
 (
 CASE
 WHEN P.GSTable = 1 THEN RPS.TotalAmount / 1.1
 ELSE RPS.TotalAmount
 END
 ),0))) * 100 ),0))
 AS Margin
FROM
 RetailPosSales RPS
 JOIN Products P ON RPS.ProductCode = P.Code
 LEFT OUTER JOIN ProductGroups PG ON P.[Group] = PG.Code /*need this if the grouping is 1*/
 LEFT OUTER JOIN ProductDepartments PD ON P.Department = PD.Code /*need this if the grouping is 2 */
WHERE
 ((@grouping <> 3) OR (@grouping = 3 AND RPS.IsFuelSale = 1)) /*When grouping by pump, only allow fuel products */
 AND P.Code BETWEEN @startProduct AND @endProduct
 AND P.[Group] BETWEEN @startGroup AND @endGroup
 AND P.Department BETWEEN @startDepartment AND @endDepartment
 AND CAST(RPS.SalesDateTime AS DATE) BETWEEN @startDate AND @endDate
 AND CAST(RPS.SalesDateTime AS TIME) BETWEEN @StartTime AND @endTime
 AND RPS.SiteId = @site
GROUP BY
 CASE @grouping
 WHEN 0 THEN P.Code
 WHEN 1 THEN P.[Group]
 WHEN 2 THEN P.Department
 WHEN 3 THEN P.[Description]
 WHEN 4 THEN CONVERT(VARCHAR,RPS.SalesDateTime,103)
 END,
 CASE @grouping
 WHEN 0 THEN P.[Description]
 WHEN 1 THEN PG.[Description]
 WHEN 2 THEN PD.[Description]
 WHEN 3 THEN 'PUMP: ' + CONVERT(VARCHAR,RPS.PumpId) +' HOSE: ' + CONVERT(VARCHAR,RPS.HoseId)
 WHEN 4 THEN CONVERT(VARCHAR(MAX),CAST(FLOOR(CAST(RPS.SalesDateTime AS FLOAT)) AS DATETIME))
 END
ORDER BY
 GroupCode,
 GroupDescription

• Cheran Shunmugavel: Which database (SQL Server, Oracle, other?) and version?
• TheRealTy: Sorry. mssql2008+

Answer 1

It appears that you're using the same expressions for the GroupCode and GroupDescription columns in the GROUP BY clause. That's asking for trouble later on if someone needs to modify either expression, especially given their size. Instead, use a subquery:

SELECT
 GroupCode,
 GroupDescription,
 -- etc
FROM
 (
 SELECT
 CASE /* etc */ END AS GroupCode,
 CASE /* etc */ END AS GroupDescription,
 -- etc
 FROM
 -- etc
 WHERE
 -- etc
 ) AS dummy
GROUP BY
 GroupCode,
 GroupDescription
ORDER BY
 GroupCode,
 GroupDescription

EDIT:

There may be some other opportunities to use subqueries to reduce duplication of expressions. For example, the expression for the column AmountX is also used in the Margin calculation.