Solved

# query ...

Posted on 2006-07-21
Medium Priority
234 Views
Hello
This is a stripped down example of a query

Select
(case when (iSite_Cart.RuleID = 0 or iSite_Cart.intQty < iSite_Rules.db1) then sum(iSite_Cart.intQty * iSite_Cart.Cost)
else
((case when iSite_Rules.RuleType = 0 then sum((iSite_Cart.intQty * iSite_Cart.Cost) - iSite_Rules.db2)
when iSite_Rules.RuleType = 1 then
sum((iSite_Cart.intQty * iSite_Cart.Cost) - (iSite_Cart.intQty * iSite_Cart.Cost) * (iSite_Rules.db2/100))
when iSite_Rules.RuleType = 2 then
sum((iSite_Cart.intQty * iSite_Cart.Cost) - (iSite_Rules.db2 * iSite_Cart.Cost)) end))
end)
as Total

iSite_Cart.RuleID is what rule is applied - rule 0 has zero value
iSite_Cart.intQty is the quantity in cart
iSite_Cart.Cost is the dollar amount
iSite_Rules.RuleType is the type of rule (0 = dollar amount, 1 = percent, 2 = subtract)
iSite_Rules.db1 is the value the quantity is checked against to see if rule will apply
iSite_Rules.db2 is the value to subtract or multiply percent

My question is how do I code when for ex
a person has an item that has a rule of "buy 5 get 1 free"
in this case db1 would be 5 and db2 would be 1
so when they buy 10 they would expect 2 free, but my code just gives the 1 free
I need to look at multiples I guess but don't know how ...

How to?

0
Question by:dgrafx
• 3
• 2

LVL 26

Expert Comment

ID: 17157059
Try:
FLOOR(iSite_Cart.intQty / iSite_Rules.db1)

As in:
0

LVL 26

Accepted Solution

DireOrbAnt earned 2000 total points
ID: 17157090
As in:
sum((iSite_Cart.intQty * iSite_Cart.Cost) - (iSite_Rules.db2 * iSite_Cart.Cost) * FLOOR(iSite_Cart.intQty / iSite_Rules.db1))

The other issue with your code was that it was always giving 1 free, even if less than 5 were purchased. This should work at all times :)

You could also simplify with:
sum( (iSite_Cart.intQty - iSite_Rules.db2 * FLOOR(iSite_Cart.intQty / iSite_Rules.db1)) * iSite_Cart.Cost )

0

LVL 25

Author Comment

ID: 17157280
thanks - seems to work good
what would be the syntax for
(case when iSite_Rules.RuleType = 0 then sum((iSite_Cart.intQty * iSite_Cart.Cost) - iSite_Rules.db2)
if I ever wanted to do this for type 0 which is subtracting an amount for every x number purchased
0

LVL 26

Expert Comment

ID: 17157454
To remove \$db2 for evey db1 purchased as in, buy 5 and get \$20 off:
case when iSite_Rules.RuleType = 0 then sum( iSite_Cart.intQty * iSite_Cart.Cost - iSite_Rules.db2 * FLOOR(iSite_Cart.intQty / iSite_Rules.db1) )
0

LVL 25

Author Comment

ID: 17158114
thanks a lot - works good
0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.