SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 rounding query or better?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mrgalvan
Starting Member

9 Posts

Posted - 09/27/2006 :  18:22:36  Show Profile  Reply with Quote
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)

USA
7020 Posts

Posted - 09/27/2006 :  19:10:07  Show Profile  Reply with Quote
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
Go to Top of Page

LoztInSpace
Aged Yak Warrior

938 Posts

Posted - 09/28/2006 :  01:54:36  Show Profile  Reply with Quote
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?
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 09/28/2006 :  03:36:05  Show Profile  Reply with Quote
... 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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29908 Posts

Posted - 09/28/2006 :  03:58:20  Show Profile  Visit SwePeso's Homepage  Reply with Quote

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
This is for upper limit purchase. If you want lower limit purchase, delete the calculation with variable in red.


Peter Larsson
Helsingborg, Sweden

Edited by - SwePeso on 09/28/2006 04:00:10
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 09/28/2006 :  05:06:43  Show Profile  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29908 Posts

Posted - 09/28/2006 :  05:16:26  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

Edited by - SwePeso on 09/28/2006 05:19:48
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 09/28/2006 :  05:23:23  Show Profile  Reply with Quote
"As long as it is above order_min"

Yup, exactly.

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

Kristen

Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29908 Posts

Posted - 09/28/2006 :  05:28:33  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

Edited by - SwePeso on 09/28/2006 05:31:05
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 09/28/2006 :  05:33:32  Show Profile  Reply with Quote
Excellent! "Write-only code" !!
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 09/28/2006 :  09:16:18  Show Profile  Reply with Quote
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

Edited by - Michael Valentine Jones on 09/28/2006 09:26:24
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 09/28/2006 :  09:30:35  Show Profile  Reply with Quote
Very elegant!
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29908 Posts

Posted - 09/28/2006 :  09:37:26  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Nice solution!


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 09/28/2006 :  09:52:23  Show Profile  Reply with Quote
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

Edited by - Michael Valentine Jones on 09/28/2006 09:54:58
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 09/28/2006 :  10:46:13  Show Profile  Reply with Quote
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
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 09/28/2006 :  11:11:26  Show Profile  Reply with Quote
I think its a comprehensive discussion on the point ... sorry there are no Date Function involved!

Kristen
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 09/28/2006 :  12:14:49  Show Profile  Reply with Quote
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
Go to Top of Page

mrgalvan
Starting Member

9 Posts

Posted - 09/28/2006 :  18:23:10  Show Profile  Reply with Quote
you are all awesome. THANKS!
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 09/29/2006 :  01:14:52  Show Profile  Reply with Quote
You got caught in the crossfire of some rather detailed discussion, but hope you can pick some good bits out of the result!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000