Solved

Help with multiplying and dividing in SQL

Posted on 2013-12-19
7
223 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 26

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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 26

Accepted Solution

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…

830 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