3
\$\begingroup\$

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
\$\endgroup\$
  • \$\begingroup\$ Which database (SQL Server, Oracle, other?) and version? \$\endgroup\$ – Cheran Shunmugavel Jun 8 '12 at 7:24
  • \$\begingroup\$ Sorry. mssql2008+ \$\endgroup\$ – TheRealTy Jun 8 '12 at 9:15
2
\$\begingroup\$

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.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.