Complex select statement

My select statement below works...but I have to add additional logic

If the max(i.shippingAmount) =5  

AND

count(o.Quantity) <= 10

THEN

max(i.shippingAmount)

ELSE

max(i.shippingAmount) *  5 for each 10

Example

1-10 = 5
11-20 = 10
21-30 = 15
etc...
Select	o.userGUID, 
	Sum(o.Quantity) as qty, 
	sum(o.baseprice) as basePrice, 
	count(o.Quantity) as cnt,
	case when max(i.shippingAmount) =5 then 5 else max(i.shippingAmount) end as shippingAmount 
from 	cartOrdLines o
	left join dbo.ariesInventory i on o.productid = i.id
where 	userGUID = '3DB237C2-61A7-4411-A213-D321FE63A6C5'
group by userGUID

Open in new window

Larry Bristersr. DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ayman AboualnourSoftware DeveloperCommented:
try this:


Select      o.userGUID,
      Sum(o.Quantity) as qty,
      sum(o.baseprice) as basePrice,
      count(o.Quantity) as cnt,
      case when max(i.shippingAmount) =5 then (CASE WHEN count(o.Quantity) <= 10 THEN max(i.shippingAmount) ELSE max(i.shippingAmount) end) end as shippingAmount
from       cartOrdLines o
      left join dbo.ariesInventory i on o.productid = i.id
where       userGUID = '3DB237C2-61A7-4411-A213-D321FE63A6C5'
group by userGUID

Ayman AboualnourSoftware DeveloperCommented:
sorry try this:

Select      o.userGUID,
      Sum(o.Quantity) as qty,
      sum(o.baseprice) as basePrice,
      count(o.Quantity) as cnt,
      case when max(i.shippingAmount) =5 then (CASE WHEN count(o.Quantity) <= 10 THEN max(i.shippingAmount) ELSE max(i.shippingAmount) *  5 for each 10) end) end as shippingAmount
from       cartOrdLines o
      left join dbo.ariesInventory i on o.productid = i.id
where       userGUID = '3DB237C2-61A7-4411-A213-D321FE63A6C5'
group by userGUID

Mark WillsTopic AdvisorCommented:

Hmmm, on first thoughts, I would suggest trying something like :

((floor(((max(i.shippingamount))-1) / 10.0)+1)*5)

But will do some testing and get back to you...
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Mark WillsTopic AdvisorCommented:
For each 10 of what ? Quantity ?

Select	o.userGUID, 
	Sum(o.Quantity) as qty, 
	sum(o.baseprice) as basePrice, 
	count(o.Quantity) as cnt,
	case when max(i.shippingAmount) =5 then 5 else max(i.shippingAmount) end as shippingAmount ,

	CASE WHEN max(i.shippingAmount) =5  AND count(o.Quantity) <= 10
	     THEN max(i.shippingAmount)
	     ELSE ((floor(((max(i.shippingamount))-1) / 10.0)+1)*5)    -- might need to be quantity...
	END

from  	cartOrdLines o
left join ariesInventory i on o.productid = i.id
where 	userGUID = '3DB237C2-61A7-4411-A213-D321FE63A6C5'
group by userGUID

Open in new window

Olaf DoschkeSoftware DeveloperCommented:
Actually your code

Case when max(i.shippingAmount) =5 then 5 else max(i.shippingAmount) end

does EXACTLY THE SAME AS max(i.shippingAmount)

Because in case it's 5 it's 5 and in other cases it's otherwise.

Bye, Olaf.
Olaf DoschkeSoftware DeveloperCommented:
Sorry, aikmark. I admit, this was too strong. Nevertheless this is where we get in bad reputation of software development. Not only in this profession the rule is, a professional will be worth his money. And if someone does not see the wood for the trees he shouldn't be a lumberjack.

Bye, Olaf.
Mark WillsTopic AdvisorCommented:
*laughing*

Thats exactly why Irbrister is asking us the question. And Olaf, you dont need to worry about contributing unless you have something that might help.

I think (only guessing) the column :

case when max(i.shippingAmount) =5 then 5 else max(i.shippingAmount) end as shippingAmount

is an example of where the new definition needs to be, was missing the quantity qualifier, and more than likely it (possibly) should be "<= 5"  (indicating a minimum shippingamount of 5) because of the left join, and, really should be handling NULL values as well (otherwise you will get the message "Warning: Null value is eliminated by an aggregate or other SET operation." ).  

So, instead of  "max(i.shippingamount)" should ideally be "max(isnull(i.shippingamount,0))"

SELECT    o.userGUID, 
          Sum(o.Quantity) as qty, 
          sum(o.baseprice) as basePrice, 
          count(o.Quantity) as cnt,
          case when max(isnull(i.shippingamount,0)) =5 then 5 else max(isnull(i.shippingamount,0)) end as shippingAmount ,        -- maybe <= 5 ?  and retained as part of original query

          CASE WHEN max(isnull(i.shippingamount,0)) =5  AND count(o.Quantity) <= 10            -- maybe <= 5 ?
               THEN max(isnull(i.shippingamount,0))
               ELSE ((floor(((max(isnull(i.shippingamount,0)))-1) / 10.0)+1)*5)    -- put max(shippingamount) into a grouping of ten so we can multiply by 5 -- or maybe quantity ?
          END

FROM      #cartOrdLines o
LEFT JOIN #ariesInventory i on o.productid = i.id
WHERE     userGUID = '3DB237C2-61A7-4411-A213-D321FE63A6C5'
GROUP BY  userGUID

Open in new window

Olaf DoschkeSoftware DeveloperCommented:
I may be a bit exagggerating here, but actually I think in a world recession saving money trying to do things on your own is leading the wrong way. This is not the first and only question I see, which makes me think this way.

Actually the question is so weird nodody can help lrbrister unless he reverts his "developer talk" and tells us in plain english what he tries to achieve. That's the least you can expect of someone seeking for help, that he can formulate an understandable question.

Obviously it has something to do with determining shipping or packaging costs depending on shippingamount and quantity.

Count(o.quantity) also is most probably not what lrbrister wants to compute, as it's just the count of records, SUM(quantity) is the sum of all quantities.

Overall what is searched is a factor as a multiple of 5 per quantity of 10, that's how far I can be sure about the question.

Let's take this in baby steps.

  1-10 => 5
11-20 => 10
21-30 => 15
....

If that's your goal let's take it step by step. First, what would be easier is

  1-10 => 1
11-20 => 2
21-30 => 3

Because what is this? If you divide by 10 and then round up to the next integer (whole number) you get 1,2,3. That is Ceiling(n/10)

Now you want 5,10,15 instead of 1,2,3, then multiply that by 5:
Ceiling(n/10)*5

Whatever the n is, I'm quite sure it's neither max(i.Shppingamount) nor Count(o.Quantity) but most probably Sum(o.Quantity) which should determine this factor. And this is the factor of max(i.Shppingamount, so my suggestin is:

Max(i.ShippingAmount)*Ceiling(Sum(o.Quantity)/10)*5


Select      o.userGUID,
      Sum(o.Quantity) as qty,
      sum(o.baseprice) as basePrice,
      count(*) as cnt,
        Ceiling(Sum(o.Quantity)/10)*5 As ShippingAmountFactor
      Max(i.ShippingAmount)*Ceiling(Sum(o.Quantity)/10)*5 As CalculatedShippingAmount
from       cartOrdLines o
      left join dbo.ariesInventory i on o.productid = i.id
where       userGUID = '3DB237C2-61A7-4411-A213-D321FE63A6C5'
group by userGUID

Bye, Olaf.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mark WillsTopic AdvisorCommented:
ceiling() is better than mucking around with floor and adding one.

However, if shippingamount is integer then you do need to change it into decimal arithmetic to work...


declare @n int
set @n = 2
select  Ceiling(@n/10)*5 as [Olaf], Ceiling(@n/10.0)*5 as [Decimal]


So, in Olaf's code above, suggest you divide by 10.0 and not the integer 10

Olaf DoschkeSoftware DeveloperCommented:
Thanks, mark. Didn't know SQL Server wouldn't automatically "switch" to decimals, if you divide an integer by another integer.

Bye, Olaf.
Larry Bristersr. DeveloperAuthor Commented:
Hey guys,
  I've been on the road and seems that I sparked a war of words here.  I'll be home this evening and can test what you folks have proposed.

To clarify.
My customer sells chain mail jewelry.
The most he ever sends (USPS) is 10 in a box and the shipoping for each box is $5
So...
If he sells 13 pieces...the shipping needs to be calculated as $10
4 pieces as $5
69 pieces as $30

etc...
As I said...I'll be home this evening and can test your code.

Just as a casual observer who places a premium on politeness...the comments back and forth seemed a little tense at times.
If I in any way sparked that with my question...please accept my apologies.

Olaf_Doschke:
At first glance...your second paragraph on 12/26/10 12:57 PM wasn't necessary and should have been directed AT me instead of speaking of me in the third person.  However...I will do a better job of framing my question in the future.
Thanks for your attention and efforts to answer my question.
Mark WillsTopic AdvisorCommented:
OK,

That makes sense, now we need to match that requirement up against data columns. The remaining piece of the puzzle. We still appear to have a couple of "constants" ie the value of 5 and the value of 10 - we need to understand where they come from (or in fact just constant).

It sounds like it is probably Quantity that we need to group into "tens" and by the looks of it the multiply by 5 could well be multiply by shippingamount. That certainly explains that  " = 5 and quantity <=10 " example before.

But if ordering a lot of different pieces, then is it possible that different products carry different shippingamounts ? If we order a product where shippingamount is 3 and another where shippingamount is 5 and another where shippingamount is 7 then is it accurate to say the shippingamount we need to apply is 7 across all products ?

Is there any consideration for (say) weight or size of items in a box, or is it a straight 10 pieces per box regardless ?  It could also be that shippingamount represents the number of items per box rather than value of shipping a box. So that will need to be clarified (that goes back to the 5 and 10 constants mentioned before).

There could also be the case that we need to first resolve at the individual product line, could even consider resolving by groups of shippingamounts.



So, what about (and using ceiling rather than floor + 1) ...

SELECT    o.userGUID, 
          Sum(o.Quantity) as qty, 
          sum(o.baseprice) as basePrice, 
          count(o.Quantity) as cnt,

          ceiling(sum(o.quantity) / 10.0) as number_of_boxes,
          max(isnull(i.shippingAmount,0)) as max_shippingamount,
          ceiling(sum(o.quantity) / 10.0) * max(isnull(i.shippingAmount,0)) as actual_shippingamount

FROM      cartOrdLines o
LEFT JOIN ariesInventory i on o.productid = i.id
WHERE     userGUID = '3DB237C2-61A7-4411-A213-D321FE63A6C5'
GROUP BY  userGUID

Open in new window


Using the above, could you please indicate if number_of_boxes is the correct value, and, if actual_shippingamount is also correct ? Also, is there any real need for a left outer join ? Is it possible that there are items being ordered that do not exist in ariesInventory ?
Larry Bristersr. DeveloperAuthor Commented:
mark_wills:
Thanks...I'll try this when I get off the road today.
Larry Bristersr. DeveloperAuthor Commented:
Thanks folks.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.