TSQL: Select from set of columns with lowest positive value

Question

Example Schema:

RowID    Quantity    ModifiedPrice    GroupPrice    CustomPrice    SalePrice
----------------------------------------------------------------------------
1        5           20.00            0             15.00          17.00
2        2           14.00            7.00          22.00          0
3        9           10.00            10.00         0              11.00

Based on this example table, I would like to be able to select the lowest non-zero value between the four *Price columns in the most efficient/simplest manner possible.

Example Output:

RowID    Quantity    EndPrice
------------------------------
1        5           15.00
2        2           7.00
3        9           10.00

For extra information, the DB is SQL Server 2005.

Answer 1

SELECT  RowId, Quantity,
        (
        SELECT  MIN(price)
        FROM    (
                SELECT  ModifiedPrice AS price
                UNION ALL
                SELECT  GroupPrice
                UNION ALL
                SELECT  CustomPrice
                UNION ALL
                SELECT  SalePrice
                ) qi
        WHERE   price > 0
        )
FROM    mytable

This is more readable than a bunch of CASE statements.

Note however that this is about 4 times as slow as CASE statements.

Here's the test script which parses and yields correct results:

CREATE TABLE #t_prices
        (
        RowID INT NOT NULL,
        Quantity INT NOT NULL,
        ModifiedPrice FLOAT NOT NULL,
        GroupPrice FLOAT NOT NULL,
        CustomPrice FLOAT NOT NULL,
        SalePrice FLOAT NOT NULL
        )

INSERT
INTO    #t_prices
VALUES  (1, 5, 20.00, 0, 15.00, 17.00)
INSERT
INTO    #t_prices
VALUES  (2, 2, 14.00, 7.00, 22.00, 0)
INSERT
INTO    #t_prices
VALUES  (3, 9, 10.00, 10.00, 0, 11.000)


SELECT  RowId, Quantity,
        (
        SELECT  MIN(price)
        FROM    (
                SELECT  ModifiedPrice AS price
                UNION ALL
                SELECT  GroupPrice
                UNION ALL
                SELECT  CustomPrice
                UNION ALL
                SELECT  SalePrice
                ) qi
        WHERE   price > 0
        )
FROM    #t_prices

Answer 2

I would use a case statement:

CASE
  WHEN condition THEN trueresult
  [...n]
[ELSE elseresult]
END

Starting with an uncluttered answer, assume none of the values are NULL:

CASE 
  WHEN ModifiedPrice > GroupPrice AND ModifiedPrice > CustomPrice AND ModifiedPrice > SalePrice THEN ModifiedPrice
  WHEN GroupPrice > CustomPrice AND GroupPrice > SalePrice THEN GroupPrice
  WHEN CustomPrice > SalePrice THEN CustomPrice
  ELSE SalePrice
END

If any values are NULL, then those clauses will return false, so we need to use ISNULL to fix things up and replace NULLS with a large negative number or zero, if you do not expect any negative prices. Assuming no negative prices, I will use zero.

CASE 
  WHEN ModifiedPrice > ISNULL(GroupPrice, 0) AND ModifiedPrice > ISNULL(CustomPrice, 0) AND ModifiedPrice > ISNULL(SalePrice,0) THEN ModifiedPrice
  WHEN GroupPrice > ISNULL(CustomPrice, 0) AND GroupPrice > ISNULL(SalePrice, 0) THEN GroupPrice
  WHEN CustomPrice > ISNULL(SalePrice, 0) THEN CustomPrice
  ELSE ISNULL(SalePrice, 0)
END

Not pretty, but it will work. If you perform some statistics to see which column is normally the largest value, you can change the query to test that column first. (You cannot merely rearrange my WHEN clauses as written since they each assume the previous price has already been rejected. If CustomPrice was usually the largest, I would swap ModifiedPrice and CustomPrice in the code above.)

Answer 3

I'm a bit late to this particular question, but UNPIVOT might have been handy here as well. 4 Columns probably isn't too bad, but when you're looking at 30 columns that you want to un crosstab, UNPIVOT is a godsend :-)