rounding query or better? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

rounding query or better?

Author

Topic

mrgalvan
Starting Member

9 Posts

Posted - 2006-09-27 : 18:22:36

I need help on this query. I'm trying to have a number rounded, so I can truncate the decimal. The reason I want to do this is that it is for a planning function and I need it to round to a number that is divisible by an order minimum qty. Example: I show a need for 2611 items, but the item is only ordered in qtys of 100, so I'd need 2600 instead of 2611, because the vendor won't let me order out of qty. So, my query would take 2611 / minimum order qty (100) which would be 26.11 somehow take off the .11 then multiply back by 100, which would give me 2600.

use mas500test_app
-- UPDATE timItem
-- SET UserFld3 = 1
Select distinct I.ItemID, V.VendID, D.ShortDesc, B.Name as ItemBuyer, BV.Name as VendorBuyer,
IC.ItemClassID, PPL.PurchProdLineID, isNUll(BI.QtyOnHand,0) AS QtyOnHand,
IV.QtyOnPO, IV.QtyONSo, IV.QtyONBo, W.WhseID, ((IV.QtyOnPO + isNUll(BI.QtyOnHand,0) - IV.QtyOnSO - IV.QtyOnBO)) as Available,
IV.MaxStockQty, IV.MinStockQty, IV.MaxStockQty - IV.MinStockQty AS SafetyStock, I.UserFld6 as Rank, I.UserFld3,
-- Case
-- WHEN I.UserFld3 = 0
-- THEN '1'
-- ELSE I.UserFld3
-- END
-- as PackQty,

CASE
WHEN (IV.MaxStockQty - IV.MinStockQty) <> 0
THEN ((IV.QtyOnPO + isNUll(BI.QtyOnHand,0) - IV.QtyOnSO - IV.QtyOnBO) / (IV.MaxStockQty - IV.MinStockQty))
ELSE 0
END
AS MonthsOnHand,

CASE
WHEN ((IV.QtyOnPO + isNUll(BI.QtyOnHand,0) - IV.QtyOnSO - IV.QtyOnBO)) < IV.MinStockQty
THEN IV.MaxStockQty - ((IV.QtyOnPO + isNUll(BI.QtyOnHand,0) - IV.QtyOnSO - IV.QtyOnBO)) --* I.UserFld3

END
as QtyNeed, I.StdBinQty,

CASE
WHEN ((IV.QtyOnPO + isNUll(BI.QtyOnHand,0) - IV.QtyOnSO - IV.QtyOnBO)) < I.StdBinQty
THEN I.StdBinQty
WHEN ((IV.QtyOnPO + isNUll(BI.QtyOnHand,0) - IV.QtyOnSO - IV.QtyOnBO)) > I.StdBinQty
****** This is the number I need rounded ***** THEN ROUND(IV.MaxStockQty - (IV.QtyOnPO + isNUll(BI.QtyOnHand,0) - IV.QtyOnSO - IV.QtyOnBO) / I.userfld3), -2)))
END
AS OrdQty

from timwhsepurchprodln WPL
INNER JOIN TAPvENDOR v ON WPL.PrimaryVendKey = V.VendKey
INNER JOIN timPurchProdLine PPL ON WPL.PurchProdLineKey = PPL.PurchProdLinekey
inner join timInventory IV ON PPL.PurchProdLinekey = IV.PurchProdLinekey
INNER Join timBuyer B ON IV.BuyerKey = B.BuyerKey
INNER Join timBuyer BV ON V.BuyerKey = BV.BuyerKey
INNER JOIN timItem I ON I.ItemKey = IV.ItemKey
INNER JOIN timItemClass IC ON I.ItemClassKey = IC.ItemClassKey
INNER JOIN timWarehouse W ON W.WhseKey = IV.WhseKey
INNER JOIN timItemDescription D ON I.ItemKey = D.ItemKey
INNER JOIN timItemUnitOfMeas IUOM ON I.ItemKey = IUOM.ItemKey
INNER JOIN tciUnitMeasure UM ON IUOM.TargetUnitMeasKey = UM.UnitMeasKey
LEFT JOIN
(SELECT ItemKey, SUM(QtyOnHand) AS QtyOnHand
FROM timWhseBinInvt
GROUP BY ItemKey) BI ON BI.ItemKey = I.ItemKey
where IV.WhseKey = 22
and I.Status = 1
and ((IV.QtyOnPO + isNUll(BI.QtyOnHand,0) - IV.QtyOnSO - IV.QtyOnBO)) < IV.MinStockQty

order by VendID

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-09-27 : 19:10:07

I think this is a better way to do the calculation:


select
	Amt_Needed, Order_Min,
	Order_Amt=  (amt_needed/order_min)*order_min
from
	-- Test Data
	(
	select Amt_Needed = 2611, Order_Min = 100 union all
	select mt_needed = 2659, order_min = 10 union all
	select amt_needed = 212, order_min = 13 
	) a

Results:


Amt_Needed  Order_Min   Order_Amt   
----------- ----------- ----------- 
2611        100         2600
2659        10          2650
212         13          208

(3 row(s) affected)

CODO ERGO SUM

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2006-09-28 : 01:54:36

I'd be throwing a +1 in there as well. If I need 2611 of something wouldn't I'd need to order 2700 not 2600?

Kristen
Test

22859 Posts

Posted - 2006-09-28 : 03:36:05

... except where it is already an exact multiple - i.e. if you enter 2600 you don't want to have that rounded up to 2700!

This is what we do:


QtyRequired = CASE 
	WHEN       COALESCE(QtyMultiple, 1) <= 1	-- No Qty Multiple ...
		OR (QtyRequired % QtyMultiple) = 0	-- ... or already an exact multiple
	THEN QtyRequired 
	ELSE ((QtyRequired / QtyMultiple) + 1) * QtyMultiple
	END

Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-28 : 03:58:20

[code]
select Amt_Needed,
Order_Min,
case
when amt_needed % order_min = 0 then amt_needed
else order_min + amt_needed - amt_needed % order_min
end Order_Amt
from (
select 2611 Amt_Needed,
100 Order_Min
union all
select 2659,
10
union all
select 2820,
20
union all
select 212,
13
) a[/code]This is for upper limit purchase. If you want lower limit purchase, delete the calculation with variable in red.

Peter Larsson
Helsingborg, Sweden

Kristen
Test

22859 Posts

Posted - 2006-09-28 : 05:06:43

Peso: Is this minimum ordering, or ordering in "multiples" ? (I'm looking at your column naming and being pedantic about the possibly ambiguity

)

I come across suppliers who have a minimum order qty, but you can but any number you like above that. Then there are the suppliers who have a product sold in boxes of 12, and you buy in multiples of 12 (possibly also with a minimum requirements). This basically means you are buying "each", which some retailers prefer because they find it easier to decide how many to reorder; the alternative, of course, is that you buy in "boxes" - but then the retailers have to know that a box of Product X contains 12, whereas Product Y contains 24 ... etc. So when the sales unit is "boxes" you don't normally have Multiples, but there may still be a Minimum Qty.

Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-28 : 05:16:26

Yes, I interpreted order_min as the only amount available to buy. As six pack of beer is the only option to buy. No single beer.
So my calculation is in multiples of order_min, in that you are correct.

Otherwise this whole exercise is not needed, right? If it was possible to order the missing amount directly? As long as it is above order_min.

For the other case (minimum ordering), the code would look something like

select	Amt_Needed,
	Order_Min,
	case
		when amt_needed < order_min then order_min
		else amt_needed
	end Order_Amt
from	(
		select	2611 Amt_Needed,
			100 Order_Min
		union all
		select	2659,
			10
		union all
		select	2820,
			20
		union all
		select	212,
			13 
		union all
		select	5,
			24 
	) a

Peter Larsson
Helsingborg, Sweden

Kristen
Test

22859 Posts

Posted - 2006-09-28 : 05:23:23

"As long as it is above order_min"

Yup, exactly.

Don't suppose that can be done without a CASE ?

Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-28 : 05:28:33

You'll bet?

declare @test table (Amt_Needed int, Order_Min int)

insert	@test
select	2611,
	100
union all
select	2659,
	10
union all
select	2820,
	20
union all
select	212,
	13 
union all
select	5,
	24 

select	Amt_Needed,
	order_min,
	amt_needed Order_Amt
from	@test
where	amt_needed > order_min
union all
select	Amt_Needed,
	order_min,
	order_min
from	@test
where	amt_needed <= order_min

Peter Larsson
Helsingborg, Sweden

Kristen
Test

22859 Posts

Posted - 2006-09-28 : 05:33:32

Excellent! "Write-only code" !!

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-09-28 : 09:16:18

quote:
Originally posted by LoztInSpace

I'd be throwing a +1 in there as well. If I need 2611 of something wouldn't I'd need to order 2700 not 2600?

That is a businees rule, not really a SQL Server question, so I answered it the way he showed.

I would certainly make sure that is really what a purchasing agent wanted before I put it in an applicaiton.

However, this code seems to handle the case of rounding up to the next multiple of the Order_Min without using a CASE.


select
	Amt_Needed, Order_Min,
	Order_Amt=  ((amt_needed+(order_min-1))/order_min)*order_min
from
	-- Test Data
	(
	select Amt_Needed = 2611, Order_Min = 100 union all
	select Amt_Needed = 2599, Order_Min = 100 union all
	select Amt_Needed = 2600, Order_Min = 100 union all
	select Amt_Needed = 2601, Order_Min = 100 union all
	select mt_needed = 2659, order_min = 10 union all
	select amt_needed = 212, order_min = 13 
	) a

Results:


Amt_Needed  Order_Min   Order_Amt   
----------- ----------- ----------- 
2611        100         2700
2599        100         2600
2600        100         2600
2601        100         2700
2659        10          2660
212         13          221

(6 row(s) affected)

CODO ERGO SUM

Kristen
Test

22859 Posts

Posted - 2006-09-28 : 09:30:35

Very elegant!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-28 : 09:37:26

Nice solution!

Peter Larsson
Helsingborg, Sweden

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-09-28 : 09:52:23

Just to beat this in to death, this is a more general solution for when both the Amt_Needed and Order_Min are decimal numbers.


select
	Amt_Needed, Order_Min,
	Order_Amt_Round_Down= 
	convert(decimal(15,2),floor(amt_needed/order_min)*order_min),
	Order_Amt_Round_Up= 
	convert(decimal(15,2),ceiling(amt_needed/order_min)*order_min)
from
	-- Test Data
	(
	select Amt_Needed = 2611.25, Order_Min = 100.00 union all
	select Amt_Needed = 2599.11, Order_Min = 100.00 union all
	select Amt_Needed = 2600.00, Order_Min = 100.00 union all
	select Amt_Needed = 2601.20, Order_Min = 100.00 union all
	select mt_needed = 2659.00, order_min = 10.50 union all
	select mt_needed = 2659.00, order_min = 10.77 union all
	select mt_needed = 2659.00, order_min = 10.88 union all
	select amt_needed = 212.00, order_min = 13.20 
	) a

Results:


Amt_Needed Order_Min Order_Amt_Round_Down Order_Amt_Round_Up 
---------- --------- -------------------- ------------------ 
2611.25    100.00    2600.00              2700.00
2599.11    100.00    2500.00              2600.00
2600.00    100.00    2600.00              2600.00
2601.20    100.00    2600.00              2700.00
2659.00    10.50     2656.50              2667.00
2659.00    10.77     2649.42              2660.19
2659.00    10.88     2654.72              2665.60
212.00     13.20     211.20               224.40

(8 row(s) affected)

CODO ERGO SUM

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-09-28 : 10:46:13

Wow, Kristen put this thread on the New to SQL Server Sticky FAQ already!

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210

CODO ERGO SUM

Kristen
Test

22859 Posts

Posted - 2006-09-28 : 11:11:26

I think its a comprehensive discussion on the point ... sorry there are no Date Function involved!

Kristen

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-09-28 : 12:14:49

quote:
Originally posted by Kristen

I think its a comprehensive discussion on the point ... sorry there are no Date Function involved!

Kristen

Fixed the lack of Date Functions.


select
	ORDER_DATE = convert(varchar(10),ORDER_DATE,121),
	Amt_Needed, Order_Min,
	Order_Amt_Round_Down= 
	convert(decimal(15,2),floor(amt_needed/order_min)*order_min),
	Order_Amt_Round_Up= 
	convert(decimal(15,2),ceiling(amt_needed/order_min)*order_min)
from
	-- Test Data
	(
	select Amt_Needed = 2611.25, Order_Min = 100.00,
	ORDER_DATE =
	dbo.F_START_OF_DAY(dbo.F_RANDOM_DATETIME(
	dbo.F_START_OF_QUARTER(getdate()),
	dbo.F_END_OF_QUARTER(getdate()), newid()))
	union all
	select Amt_Needed = 2599.11, Order_Min = 100.00 ,
	ORDER_DATE =
	dbo.F_START_OF_DAY(dbo.F_RANDOM_DATETIME(
	dbo.F_START_OF_QUARTER(getdate()),
	dbo.F_END_OF_QUARTER(getdate()), newid()))
	union all
	select Amt_Needed = 2600.00, Order_Min = 100.00,
	ORDER_DATE =
	dbo.F_START_OF_DAY(dbo.F_RANDOM_DATETIME(
	dbo.F_START_OF_QUARTER(getdate()),
	dbo.F_END_OF_QUARTER(getdate()), newid()))
	union all
	select Amt_Needed = 2601.20, Order_Min = 100.00,
	ORDER_DATE =
	dbo.F_START_OF_DAY(dbo.F_RANDOM_DATETIME(
	dbo.F_START_OF_QUARTER(getdate()),
	dbo.F_END_OF_QUARTER(getdate()), newid()))
	union all
	select mt_needed = 2659.00, order_min = 10.50,
	ORDER_DATE =
	dbo.F_START_OF_DAY(dbo.F_RANDOM_DATETIME(
	dbo.F_START_OF_QUARTER(getdate()),
	dbo.F_END_OF_QUARTER(getdate()), newid()))
	union all
	select mt_needed = 2659.00, order_min = 10.77,
	ORDER_DATE =
	dbo.F_START_OF_DAY(dbo.F_RANDOM_DATETIME(
	dbo.F_START_OF_QUARTER(getdate()),
	dbo.F_END_OF_QUARTER(getdate()), newid()))
	union all
	select mt_needed = 2659.00, order_min = 10.88,
	ORDER_DATE =
	dbo.F_START_OF_DAY(dbo.F_RANDOM_DATETIME(
	dbo.F_START_OF_QUARTER(getdate()),
	dbo.F_END_OF_QUARTER(getdate()), newid()))
	union all
	select amt_needed = 212.00, order_min = 13.20 ,
	ORDER_DATE =
	dbo.F_START_OF_DAY(dbo.F_RANDOM_DATETIME(
	dbo.F_START_OF_QUARTER(getdate()),
	dbo.F_END_OF_QUARTER(getdate()), newid()))
	) a
where
	a.ORDER_DATE >= dbo.F_START_OF_MONTH(getdate()) and
	a.ORDER_DATE <  dateadd(mm,1,dbo.F_START_OF_MONTH(getdate()))
order by
	a.ORDER_DATE

Results:


ORDER_DATE Amt_Needed Order_Min Order_Amt_Round_Down Order_Amt_Round_Up 
---------- ---------- --------- -------------------- ------------------ 
2006-09-04 2659.00    10.77     2649.42              2660.19
2006-09-05 2601.20    100.00    2600.00              2700.00
2006-09-09 212.00     13.20     211.20               224.40
2006-09-10 2659.00    10.50     2656.50              2667.00

(4 row(s) affected)

CODO ERGO SUM

mrgalvan
Starting Member

9 Posts

Posted - 2006-09-28 : 18:23:10

you are all awesome. THANKS!

Kristen
Test

22859 Posts

Posted - 2006-09-29 : 01:14:52

You got caught in the crossfire of some rather detailed discussion, but hope you can pick some good bits out of the result!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources