Help with multiplying and dividing in SQL
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.
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.
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.
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.
ASKER
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.
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.
ASKER
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 ?
1.02563562356 should be 0.126
can that be done as well ?
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)
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)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Sorry it took so long...
Select @iPallet / (column1 * column2) AS 'Pallets'