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
Go to Top of Page

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?
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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

Go to Top of Page

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
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2006-09-28 : 05:33:32
Excellent! "Write-only code" !!
Go to Top of Page

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
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2006-09-28 : 09:30:35
Very elegant!
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-28 : 09:37:26
Nice solution!


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

mrgalvan
Starting Member

9 Posts

Posted - 2006-09-28 : 18:23:10
you are all awesome. THANKS!
Go to Top of Page

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!
Go to Top of Page
   

- Advertisement -