Sales, Taxes and Totals

Question

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)) -- 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)))
, SUM(COALESCE(
    CAST(((ISNULL(SalesTaxRate,0) * (ISNULL(Price,0) * Qty)) + (ISNULL(ImportTaxRate,0) * (ISNULL(Price,0) * Qty))) / 100 AS DECIMAL(6,2))
    , 0))
, SUM(
    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

Answer 1

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

Answer 2

I recommend specifying an ORDER BY clause, even if the ordering is based on something arbitrary like the Id. When users print the same invoice twice, they expect the output to be repeatable.

You used both COALESCE(Price, 0) and ISNULL(Price,0) — consistency would be good. I recommend putting a NOT NULL constraint on Price — perhaps also with DEFAULT 0.00.

Be careful about doing the same on the tax column, though! Canadian GST makes a distinction between untaxable items and items taxed at 0%. For that reason, I believe that a NULL tax rate should be propagated when multiplying, and only treated as 0.00 for the summation.