You can use SUM to calculate the sum of several numbers, but
you cannot directly use set-based logic to calculate a product. Yet there is a
very simple trick – you can use EXP(SUM(LOG(…))) and get a product of several
numbers without a loop. The trick was originally posted on newsgroups by Tom Cooper.
For example, suppose that you have to allow for multiple
discounts at a time. Consider the following UDF that mocks some complex logic
that determines price discounts based on customer and date:
CREATE FUNCTION
dbo.GetDiscounts(@CustomerID
INT, @AsOfDate DATETIME)
RETURNS TABLE
AS
RETURN(
SELECT 0.1 AS Discount, 'Easter Sale' AS
Reason
UNION ALL
SELECT 0.1 AS Discount, 'Preferred Customer' AS
Reason
)
GO
Currently any customer is eligible for two discounts, 0.1
each. The total discount after applying them both is 0.19. Of course you can write a cursor or a loop to calculate it. However, the following script
calculates total discount using set-based logic, without writing a loop:
SELECT 1-EXP(SUM(LOG(1-Discount))) AS TotalDiscount FROM dbo.GetDiscounts(1, '20090320');
TotalDiscount
----------------------
0.19
(1 row(s) affected)