| Author |
 Topic  |
|
|
mrgalvan
Starting Member
9 Posts |
 Posted - 09/27/2006 : 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)
USA
6997 Posts |
Posted - 09/27/2006 : 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
876 Posts |
Posted - 09/28/2006 : 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
United Kingdom
22191 Posts |
Posted - 09/28/2006 : 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
Sweden
29138 Posts |
Posted - 09/28/2006 : 03:58:20
|
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 |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 09/28/2006 : 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
Sweden
29138 Posts |
Posted - 09/28/2006 : 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 likeselect 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 |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 09/28/2006 : 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
Sweden
29138 Posts |
Posted - 09/28/2006 : 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 |
Edited by - SwePeso on 09/28/2006 05:31:05 |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 09/28/2006 : 05:33:32
|
| Excellent! "Write-only code" !! |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
6997 Posts |
Posted - 09/28/2006 : 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 |
Edited by - Michael Valentine Jones on 09/28/2006 09:26:24 |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 09/28/2006 : 09:30:35
|
| Very elegant! |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 09/28/2006 : 09:37:26
|
Nice solution!
Peter Larsson
Helsingborg, Sweden |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
6997 Posts |
Posted - 09/28/2006 : 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 |
Edited by - Michael Valentine Jones on 09/28/2006 09:54:58 |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
6997 Posts |
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 09/28/2006 : 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)
USA
6997 Posts |
Posted - 09/28/2006 : 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 - 09/28/2006 : 18:23:10
|
you are all awesome. THANKS!  |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 09/29/2006 : 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! |
|
|
| |
Topic |
|
|
|
rounding query or better?
