January 28, 2010 at 9:45 am
I'm trying to create a script to set all of the items in my IM_PRC table to round to the nearest .05 or .09. I don't want it to round just up, but to the nearest. The name of the table is IM_PRC, and the primary key is ITEM_NO.
Below is an example of what I want:
ITEM_NOPRC_1DESIRED RESULTS
===============================================
509854510.4210.45
420000133.163.15
560170071.241.25
5645380524.9524.95
5645383621.9621.95
5645386716.9116.89
5645388111.9111.89
5645389812.9612.95
5645400010.9810.99
5645402410.9710.99
5645404810.9810.99
564541099.989.99
5645415412.9712.99
5645417816.9416.95
5645419216.8316.85
So far I have the following statement:
update IM_PRC set PRC_1 = Round(2.*PRC_1 ,1)/2.
However, this just rounds to the nearest .05 and I want it to also round to the nearest .09 as well.
January 28, 2010 at 10:06 am
there is probably a more elegant way to do it, but i could only think of this:
DECLARE @IM_PRC TABLE(
ITEM_NO INT,
PRC_1 MONEY,
DESIRED_RESULTS MONEY )
INSERT INTO @IM_PRC
SELECT 5098545, 10.42, 10.45 UNION ALL
SELECT 42000013, 3.16, 3.15 UNION ALL
SELECT 56017007, 1.24, 1.25 UNION ALL
SELECT 56453805, 24.95, 24.95 UNION ALL
SELECT 56453836, 21.96, 21.95 UNION ALL
SELECT 56453867, 16.91, 16.89 UNION ALL
SELECT 56453881, 11.91, 11.89 UNION ALL
SELECT 56453898, 12.96, 12.95 UNION ALL
SELECT 56454000, 10.98, 10.99 UNION ALL
SELECT 56454024, 10.97, 10.99 UNION ALL
SELECT 56454048, 10.98, 10.99 UNION ALL
SELECT 56454109, 9.98, 9.99 UNION ALL
SELECT 56454154, 12.97, 12.99 UNION ALL
SELECT 56454178, 16.94, 16.95 UNION ALL
SELECT 56454192, 16.83, 16.85
SELECT
*,
FLOOR(PRC_1 * 10) * 10 As MostOfTheValue,
(PRC_1 * 100) % 10 As LastDigit,--just the last digit for the calculation
CASE
WHEN (PRC_1 * 100) % 10 > 6 --7,8,9 become 9
THEN ((FLOOR(PRC_1 * 10) * 10) + 9) / 100.0
ELSE ((FLOOR(PRC_1 * 10) * 10) + 5) / 100.0 --0,1,2,3,4,5,6 becomes 5
END AS MyCalculation
FROM @IM_PRC
Lowell
January 28, 2010 at 10:15 am
That list of items was only a sample list. I have over 100,000 items, and they are changing fairly often. Will this only work if I can past my entire table of items into your formula?
January 28, 2010 at 11:36 am
jadonr
You could use a modification of Lowell code in an UPDATE statement
For example:
UPDATE IM_PRC SET PRC_1 =
CASE
WHEN (PRC_1 * 100) % 10 > 6 --7,8,9 become 9
THEN ((FLOOR(PRC_1 * 10) * 10) + 9) / 100.0
ELSE ((FLOOR(PRC_1 * 10) * 10) + 5) / 100.0 --0,1,2,3,4,5,6 becomes 5
END
Replacing all occurances of IM_PRC with your table name
Now like any other code ...Test, test and then test again before using in a production db
2:41 PM - corrected code
January 28, 2010 at 11:36 am
i would make eiher make a view that features the calcuation, or add a persisted calculated column to my table, and always use that for your display price:
ALTER TABLE YOURTABLE ADD MyCalculatedColumn AS CASE
WHEN (PRC_1 * 100) % 10 > 6 --7,8,9 become 9
THEN ((FLOOR(PRC_1 * 10) * 10) + 9) / 100.0
ELSE ((FLOOR(PRC_1 * 10) * 10) + 5) / 100.0 --0,1,2,3,4,5,6 becomes 5
END PERSISTED
or use it in a view:
CREATE VIEW VW_YOURTABLE AS
SELECT
CASE
WHEN (PRC_1 * 100) % 10 > 6 --7,8,9 become 9
THEN ((FLOOR(PRC_1 * 10) * 10) + 9) / 100.0
ELSE ((FLOOR(PRC_1 * 10) * 10) + 5) / 100.0 --0,1,2,3,4,5,6 becomes 5
END AS MyCalculatedColumn ,
YOURTABLE .* FROM
YOURTABLE
Lowell
January 28, 2010 at 12:12 pm
IM_PRC is the name of the table. When using the statement you provided, I get the following error:
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'SELECT'.
Msg 102, Level 15, State 1, Line 8
Incorrect syntax near '.'.
January 28, 2010 at 12:20 pm
@Lowel
Thank you for the advice, but creating a view that features the calculation, or adding a persisted calculated column to my table won't really serve its purpose in my situation. At least I don't think it will. I am using software that uses the sql database and only want to round the prc_1 column to the nearest .05 or .09.
Maybe I'm missing something, since I am pretty new at this - so please correct me if I'm wrong.
January 28, 2010 at 12:30 pm
jadonr (1/28/2010)
@LowelThank you for the advice, but creating a view that features the calculation, or adding a persisted calculated column to my table won't really serve its purpose in my situation. At least I don't think it will. I am using software that uses the sql database and only want to round the prc_1 column to the nearest .05 or .09.
Maybe I'm missing something, since I am pretty new at this - so please correct me if I'm wrong.
jadnor you would know your business process better than us; if it were me, i'd always want to know the original price, and the display price.
it sounds like you do not care to keep the original price, and just need to update the price to the value from the formula? is that right? if the prices "change all the time" like you said, wouldn't you always want to have the price it was changed to, and a separate display price?
if that is right, that you want to update the PRC_1 price to the calulated formula, this is the statement:
UPDATE IM_PRC
SET PRC_1 =
CASE
WHEN (PRC_1 * 100) % 10 > 6 --7,8,9 become 9
THEN ((FLOOR(PRC_1 * 10) * 10) + 9) / 100.0
ELSE ((FLOOR(PRC_1 * 10) * 10) + 5) / 100.0 --0,1,2,3,4,5,6 becomes 5
END
Lowell
January 28, 2010 at 12:45 pm
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'SELECT'.
Msg 102, Level 15, State 1, Line 8
Incorrect syntax near '.'.
Corrected the code in my original answer
Sorry about that ... was a period . in the post ... do not know how it got there, but it did ... sorry about that
Now you know why I said test, test and test again
January 28, 2010 at 12:54 pm
@ Lowell
Works perfectly! I appreciate you trying to look out for me by using those extra safeguards - very nice of you.
What do I need to change to get the 0 and 1 also round to 9?
January 28, 2010 at 1:17 pm
jadonr (1/28/2010)
@ LowellWorks perfectly! I appreciate you trying to look out for me by using those extra safeguards - very nice of you.
What do I need to change to get the 0 and 1 also round to 9?
lol test , test and test again! i hope you tested my snippet on a copy of the data before asking me to clarify what 0 and 1 should be!
the code i posted was a best guess based on your post; from the way i read it(which may be different from the way you meant it!)
i thought you said all the numbers should end in 5 or 9. if it is 0 or 1, where should it round to? from your examples, i figured the round up to .05, but again , you'd know your battle plan better than us. from your update, i'm guessing maybe this?:
UPDATE IM_PRC
SET PRC_1 =
CASE
WHEN (PRC_1 * 100) % 10 < 2 --0,1 become 9
THEN ((FLOOR(PRC_1 * 10) * 10) + 9) / 100.0
--this?
--THEN (((FLOOR(PRC_1 * 10) * 10) -10) + 9) / 100.0
WHEN (PRC_1 * 100) % 10 > 6 --7,8,9 become 9
THEN ((FLOOR(PRC_1 * 10) * 10) + 9) / 100.0
ELSE ((FLOOR(PRC_1 * 10) * 10) + 5) / 100.0 --2,3,4,5,6 becomes 5
END
i doubt that is right, because 12.61 or 12.60 would change to 12.69, NOT 12.59; you gotta define the rules, and we can proably help with the logic.
Lowell
January 28, 2010 at 1:52 pm
Thanks, I believe this is exactly what I'm looking for. Yes, I always backup and run my tests on a "Test" database before implementing any changes. I'm sorry, I should have been more clear.
What I should have said is, I need all numbers that end in 0 or 1 to be rounded down to 9; any numbers that end in 7 or 8 to be rounded up to 9; any numbers that end in 2, 3, or 4 to be rounded up to 5; and 6 to be rounded down to 5. And, of course any numbers ending in 5 or 9 shouldn't change.
After looking closer at the changes you made, it appears that the line you have commented out should be the one I need. So my code should like like this:
UPDATE IM_PRC
SET PRC_1 =
CASE
WHEN (PRC_1 * 100) % 10 < 2 --0,1 become 9
THEN (((FLOOR(PRC_1 * 10) * 10) -10) + 9) / 100.0
WHEN (PRC_1 * 100) % 10 > 6 --7,8,9 become 9
THEN ((FLOOR(PRC_1 * 10) * 10) + 9) / 100.0
ELSE ((FLOOR(PRC_1 * 10) * 10) + 5) / 100.0 --2,3,4,5,6 becomes 5
END
Is this correct?
January 29, 2010 at 9:17 am
Ok, it is working exactly how I want! Thanks so much!!!
January 29, 2010 at 10:42 am
Solved
Viewing 14 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply