Return to Revisions

#Don't Repeat Yourself#

Okay, a good way of getting rid of repeated formulas is to use move them into a CROSS APPLY.

We can move the following into a CROSS APPLY, which I named Calcs.

, SUM((ISNULL(Price,0) * Qty)) -- Subtotal
, SUM(CAST((ISNULL(SalesTaxRate,0) * ISNULL(Price,0) * Qty) / 100 AS DECIMAL(6,2)))
, SUM(CAST((ISNULL(ImportTaxRate,0) * ISNULL(Price,0) * Qty) / 100 AS DECIMAL(6,2)))

I'm actually not going to include the divide by 100 bit in my CROSS APPLY, which makes stuff a little easier for us later.

#Applying Yourself to the Problem#

For now I'm going to ignore the Totals bit, you'll see why a little later. This is what the query looks like now:

SELECT
    Id,
    Product,
    [Price] = COALESCE(Price,0),
    Qty,
    [Subtotal] = Calcs.Item_Price,
    [Sales Tax] = CAST(Calcs.Sales_Tax / 100 AS DECIMAL(6,2)),
    [Import Tax] = CAST(Calcs.Import_Tax / 100 AS DECIMAL(6,2)),
    [Total Tax] = COALESCE(CAST((Calcs.Sales_Tax + Calcs.Import_Tax) / 100 AS DECIMAL(6,2)),0),
    [Line Total] = 
    Calcs.Item_Price
    + COALESCE(CAST((Calcs.Sales_Tax + Calcs.Import_Tax) / 100 AS DECIMAL(6,2)),0)
FROM #Sales
CROSS APPLY
(
    SELECT
        ISNULL(Price,0) * Qty AS Item_Price,
        (ISNULL(SalesTaxRate,0) * ISNULL(Price,0) * Qty) AS Sales_Tax,
        (ISNULL(ImportTaxRate,0) * ISNULL(Price,0) * Qty) AS Import_Tax
) Calcs

#Rollup, Rollup!#

Now, a SQL keyword you don't see used very often. We are going to use GROUP BY X WITH ROLLUP to automatically generate a total column for us.

What you want to do is add a GROUP BY Product WITH ROLLUP below the CROSS APPLY. The query needs a couple of modifications now though, because we've added aggregation.

Basically for anything that will be included in both the data and the totals, you take either the SUM, if it is a numeric column, or either the MIN or MAX for text fields.

For fields that you only want to include in either the data or totals, you have to add a CASE statement to the select query similar to one of the following:

--For the total column, GROUPING(Product) will be 1
-- for the data columns it will be equal to 0
CASE 
    WHEN GROUPING(Product) = 0 
    THEN MAX(Product) --For data rows, return the product
    ELSE 'Totals' --For the total row, return the text 'Totals'
END AS Product,

CASE 
    WHEN GROUPING(Product) = 0 
    THEN MAX(Price) --For data rows, return the price
    --We don't include an else because we don't want to 
    -- want anything to be returned for this field in the totals column.
END AS Price,

#All Together Now!#

Here is what I ended up with, which I think has much less repeated formulas and avoids having to basically repeat the entire query twice.

SELECT
    CASE 
        WHEN GROUPING(Product) = 0 
        THEN MAX(Id)
    END AS Id,
    CASE 
        WHEN GROUPING(Product) = 0 
        THEN MAX(Product)
        ELSE 'Totals'
    END AS Product,
    CASE 
        WHEN GROUPING(Product) = 0 
        THEN ISNULL(MAX(Price), 0)
    END AS Price,
    CASE 
        WHEN GROUPING(Product) = 0 
        THEN MAX(Qty)
    END AS Qty,
    [Subtotal] = SUM(Calcs.Item_Price),
    [Sales Tax] = SUM(CAST(Calcs.Sales_Tax / 100 AS DECIMAL(6,2))),
    [Import Tax] = SUM(CAST(Calcs.Import_Tax / 100 AS DECIMAL(6,2))),
    [Total Tax] = SUM(COALESCE(CAST((Calcs.Sales_Tax + Calcs.Import_Tax) / 100 AS DECIMAL(6,2)),0)),
    [Line Total] = 
    SUM(Calcs.Item_Price
    + COALESCE(CAST((Calcs.Sales_Tax + Calcs.Import_Tax) / 100 AS DECIMAL(6,2)),0))
FROM #Sales
CROSS APPLY
(
    SELECT
        ISNULL(Price,0) * Qty AS Item_Price,
        (ISNULL(SalesTaxRate,0) * ISNULL(Price,0) * Qty) AS Sales_Tax,
        (ISNULL(ImportTaxRate,0) * ISNULL(Price,0) * Qty) AS Import_Tax
) Calcs
GROUP BY Product WITH ROLLUP
ORDER BY GROUPING(Product),Id