Solved

query ...

Posted on 2006-07-21
5
224 Views
Last Modified: 2012-06-27
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
Comment
Question by:dgrafx
  • 3
  • 2
5 Comments
 
LVL 26

Expert Comment

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

As in:
0
 
LVL 26

Accepted Solution

by:
DireOrbAnt earned 500 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

by:dgrafx
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

by:DireOrbAnt
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

by:dgrafx
ID: 17158114
thanks a lot - works good
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how the fundamental information of how to create a table.

947 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now