Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Help with multiplying and dividing in SQL

Posted on 2013-12-19
7
Medium Priority
?
239 Views
Last Modified: 2014-02-14
I would like help with a select statement I can't seem to get to work.

I have a column1 with the total case count = 10
I have a column2 with the total amount of space a single case will occupy = 5 cubes

select column1 * column2 AS 'Cubes'....... this will return {50}

What I need to do is return the number of pallets it will take to put the total number of case's on.

I know I need to declare the cube amount for one pallet

DECLARE @iPallet int
SET @iPallet = '112' <-- Each pallet holds 112 cubes-->

Here is where I have to guess.

Select @iPallet / column1 * column2 AS 'Pallets'

As it is I get no results and the message reads. "Divide by zero error encountered"

Yet if I use:

Select @iPallet/column2 AS 'Pallets'

I get results (not what it needs to be, but I get them) and the message still reads "Divide by zero error encountered"

Can some one please tell me how to do this.
0
Comment
Question by:GD_GRAY
7 Comments
 
LVL 19

Expert Comment

by:jss1199
ID: 39730267
Works fine for me like:

Select @iPallet / (column1 * column2) AS 'Pallets'
0
 
LVL 27

Expert Comment

by:Shaun Kline
ID: 39730279
Sounds like a homework question but...

SELECT (column1 * column2) / <cubes per pallet> + CASE WHEN (column1 * column2) % <cubes per pallet> <> 0 THEN 1 ELSE 0 END

The part after the plus handles cubes that do not fill a complete pallet.
0
 

Author Comment

by:GD_GRAY
ID: 39730351
Shaun_Kline:

Doing it that way I get results, but I need it to divide first and if I try:

SELECT @iPallet / (column1 * column2)  + CASE WHEN (column1 * column2) % @iPallet <> 0 THEN 1 ELSE 0 END

It acts as before and returns nothing.
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 

Author Comment

by:GD_GRAY
ID: 39730376
I take that back. I just need to slide the decimal to the right and remove all the extra

1.02563562356  should be 0.126

can that be done as well ?
0
 
LVL 11

Expert Comment

by:John_Vidmar
ID: 39730392
integer <action> integer = integer

examples:
      1/2 = 0
      5/2 = 2

if you don't want that to happen then change @iPallet to numeric instead of integer.

Also, don't divide by zero:
      something / NULLIF(blah, 0)
0
 
LVL 27

Accepted Solution

by:
Shaun Kline earned 2000 total points
ID: 39730450
If you need to know how many pallets are needed for X number of cubes and you know Y cubes fit on a pallet, you need to divide the total number of cubes by the "cubes per pallet" value. This is where understanding how Units need to cancel out helps:

(x cases * y cubes/case) / (z cubes/pallet)

Also, since your columns are numeric values (you can have a partial cube?) you use the ROUND function to handle the decimals.
0
 

Author Closing Comment

by:GD_GRAY
ID: 39859316
Sorry it took so long...
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Integration Management Part 2
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…

876 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