Sales, Taxes and Totals

I'm working to get some practice with SQL functions which are often used in a financial setting, and I created this simple table with products, prices and tax rates basically.

I then wrote a query to simulate a purchase invoice type of result set (I realize things like that are most often done at application level, or as a separate DB transaction).

After I wrote this query, I found myself using so many parentheses for operations, and repeating the same formulas over and over, and I thought; there's got to be a better way to do this! So here I am seeking peer review.

You can try it using SEDE.

Here is the table:

CREATE TABLE #Sales (
    Id INT NOT NULL IDENTITY PRIMARY KEY
  , Product NVARCHAR(MAX)
  , Price DECIMAL(6,2)
  , Qty INT
  , Import BIT
  , SalesTaxRate DECIMAL(6,2)
  , ImportTaxRate DECIMAL(6,2)
); 
GO

INSERT INTO #Sales (Product, Price, Qty, Import, SalesTaxRate, ImportTaxRate)
VALUES
  ('Apple Pie',          3.99,  10, 0, 7.50, NULL)
, ('Swiss Chocolate',    6.99,  5,  1, NULL, 5.50)
, ('French Wine',        12.99, 3,  1, 7.50, 5.50)
, ('Complimentary Gift', NULL,  1,  0, NULL, NULL)
; 
GO

And here is the query which results in this table being returned:

```
SELECT
Id
, Product
, [Price] = COALESCE(Price, 0)
, Qty
, [Subtotal] = (ISNULL(Price,0) * Qty)
, [Sales Tax] = CAST((ISNULL(SalesTaxRate,0) ISNULL(Price,0) Qty) / 100 AS DECIMAL(6,2))
, [Import Tax] = CAST((ISNULL(ImportTaxRate,0) ISNULL(Price,0) Qty) / 100 AS DECIMAL(6,2))
, [Total Tax] = COALESCE(
CAST(((ISNULL(SalesTaxRate,0) (ISNULL(Price,0) Qty)) + (ISNULL(ImportTaxRate,0) (ISNULL(Price,0) Qty))) / 100 AS DECIMAL(6,2))
, 0)
, [Line Total] = (
ISNULL(Price,0) * Qty -- Subtotal
+ COALESCE(
CAST(((ISNULL(SalesTaxRate,0) (ISNULL(Price,0) Qty)) + (ISNULL(ImportTaxRate,0) (ISNULL(Price,0) Qty))) / 100 AS DECIMAL(6,2))
, 0)) -- Total Tax
FROM #Sales

UNION

SELECT
NULL
, 'Totals'
, NULL
, NULL
, SUM((ISNULL(Price,0) * Qty)) -- Sub

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

StackExchange Code Review Q#97637, answer score: 9