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 dmyDECLARE @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 = 0DECLARE @site  INT = 21/*ProductCode = 0ProductGroup = 1Department = 2Pump = 3Date = 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 MarginFROM    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 = @siteGROUP 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))    ENDORDER BY    GroupCode,    GroupDescription
Financial report query
performance;sql;finance
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,    -- etcFROM    (        SELECT            CASE /* etc */ END AS GroupCode,            CASE /* etc */ END AS GroupDescription,            -- etc        FROM            -- etc        WHERE            -- etc    ) AS dummyGROUP BY    GroupCode,    GroupDescriptionORDER BY    GroupCode,    GroupDescriptionEDIT: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.