Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Help with multiplying and dividing in SQL

Posted on 2013-12-19
7
Medium Priority
?
233 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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

Enroll in September's Course of the Month

This month’s featured course covers 16 hours of training in installation, management, and deployment of VMware vSphere virtualization environments. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

705 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