March 28, 2012 at 10:28 pm
Hi,
I am currently creating a rules table in our database that will decide what delivery fee a customer's order will incur based on the total of their order.
Rules are as follows:
1. If the Total Order Value is under $50 then delivery fee is $10
2. If the Total Order Value is between $50 and $60 then the delivery fee is whatever the difference is to bring the Total Order +fee to $60. So if the Order Total is $57 then the delivery fee is $3.
I am setting up my rules table as follows based on the http://msdn.microsoft.com/en-us/library/aa964135(d=printer,v=sql.90).aspx, Building a Rule Engine with SQL Server article.
If at all possible I dont want any logic at the front end - I want my rules table to control everything.
My table row for the first rule has no issues:
ID,Min,Max,Fee
(1,0,50.00,10)
But I do not know how to return the delivery fee for the second rule. My table row would be
ID,Min,Max,Fee
(2,50.00,60.00,XX.XX)
where XX.XX is the the difference between the Order Total and the $60.00. So if My Order Total was $56.50 XX.XX would be $60-$56.60=$3.40
Any one any ideas?? Not sure if it will be possible....
Thanks in advance,
JK
March 28, 2012 at 10:42 pm
I did actually work some logic into the code and it is OK
SELECT CASE
WHEN wff.ID = 1 THEN
wff.Result
WHEN wff.ID = 2 THEN
Condition3 - @OrderTotal
END As DeliveryFee
FROM wff
WHERE @OrderTotal > cast(Condition2 as money) AND @OrderTotal < cast(Condition3 as money)
March 29, 2012 at 12:06 am
So what happens with JayK's solution when there is a third rule?
I would do it like this:
DECLARE @r TABLE
(ID INT, [Min] MONEY, [MAX] MONEY, Fee MONEY)
INSERT INTO @r
SELECT 1 As ID, 0 AS [Min], 50 AS [Max], 10 As Fee
UNION ALL SELECT 2, 50, 60, NULL
UNION ALL SELECT 3, 60, 100, NULL-- Third rule breaks JayK's proposed solution
UNION ALL SELECT 3, 100, 9999, 0-- What about orders over the last rule?
DECLARE @o TABLE (OrderAmt MONEY)
INSERT INTO @o
SELECT 45 AS OrderAmt
UNION ALL SELECT 56.60
UNION ALL SELECT 75.00
UNION ALL SELECT 101.00
SELECT OrderAmt
,(SELECT TOP 1 CASE WHEN Fee IS NULL THEN [Max] - o.OrderAmt ELSE Fee END
FROM @r r WHERE o.OrderAmt > [Min]
ORDER BY [Max] DESC) AS Fee
FROM @o o
Sorry but I don't have time at the moment to read your article so I'm not sure if my suggestion to use NULL instead of XX.XX to indicate the placeholder for calculating the fee off the OrderAmt breaks the rules it establishes for your rules table (:-)), but doing so certainly does avoid the use of CAST/CONVERT (even implicitly).
Alternative to NULL you could use a fee of -1, -2, -3, etc. to specify other calculations of the fee.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
March 29, 2012 at 1:49 am
Seems a bit of an over complication to me.
--Sample data
DECLARE @orders TABLE (OrderID INT IDENTITY(1,1) PRIMARY KEY, OrderAmt MONEY);
INSERT INTO @orders
SELECT OrderAmt
FROM (VALUES(45),(56.60),(75.00),(101.00))a(OrderAmt);
--Query
SELECT OrderID, OrderAmt, fee, OrderAmt + fee AS total
FROM @orders
CROSS APPLY (SELECT CASE WHEN OrderAmt < 50 THEN 10
WHEN OrderAmt >= 50 AND OrderAmt < 60 THEN 60 - OrderAmt
ELSE 0 END) b(fee);
March 29, 2012 at 2:41 am
Complicated? You haven't seen complicated until you also introduce effective dates for the rules.
I actually support an application that has a similar look up approach to calculating a charge. And if you don't use effective dates, it means any change to the rules will be effective immediately. So if a wholesale change has to be made across a wide variety of customer accounts (yes it varies by customer account also), those changes must all be uploaded the morning that they're effective, presumably after testing in a test system first.
With effective dates, you can update your rules table ahead of time and be ready for that change when it occurs without a last minute scramble to get everything right (woe be upon you if you get it wrong and customers receive incorrect invoices).
But then, that is the whole point of creating a rules table. Flexibility... which introduces complications.
Speaking as a long-time developer that is.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
March 29, 2012 at 9:13 am
Just playing with it a bit, including an effective date.
DECLARE @Shipping AS TABLE (Minimum money null, Maximum money, ShippingCost money, UseDeminshingCalc bit, DateEffective datetime)
INSERT INTO @Shipping VALUES (0.00,60.00,10.00,1,'20120101'),(0.00,80.00,5.00,0,'20100101'),(0.00,100.00,5.00,1,'20070101')
DECLARE @orders TABLE (OrderID INT IDENTITY(1,1) PRIMARY KEY, OrderAmt MONEY, OrderDate datetime);
INSERT INTO @orders
SELECT OrderAmt, OrderDate
FROM (VALUES(45, '20080430'),(56.60, '20110528'),(56.90, getdate()),(75.00, getdate()),(101.00, getdate()))a(OrderAmt,OrderDate);
SELECT
*,
CAST((CASE WHEN S.ShippingCost IS NULL THEN 0.00
WHEN S.UseDeminshingCalc = 0 THEN S.ShippingCost
ELSE (CASE WHEN (S.Maximum - O.OrderAmt) > S.ShippingCost THEN S.ShippingCost ELSE (S.Maximum - O.OrderAmt) END)
END) AS money) ShippingCost
FROM
@orders O
LEFT JOIN
@Shipping S
ON
(SELECT Max(M.DateEffective) FROM @Shipping M WHERE M.DateEffective <= O.OrderDate) = S.DateEffective AND
O.OrderAmt BETWEEN S.Minimum AND S.Maximum
March 29, 2012 at 11:17 pm
Hi all - Thanks so much for taking the time to reply - been very helpfl. Ive been playing around with Antares686's solution today and it works like a charm!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply