I am having difficulty setting standard retail prices after rough re=pricing.

I cannot get the following to work and assume I have misunderstaood something.

SELLPINC1 is the field for the retail price includive of VAT

UPDATE STOCK
SET SELLPINC1 =
(IF ((ROUND(SELLPINC1/10)*10) - (ROUND(SELLPINC1*100)/100) < 50 THEN (ROUND(SELLPINC1/100)*100) +
49.99 ELSE
(ROUND(SELLPINC1/100)*100) - 0.01))
WHERE SELLPINC1 > 499

This works as long as the tens and units are less than 50
If not it seems to round to the next hundred and
then add 49.99 anyway.

544.35 becomes 549.99 as intended but 564.55 is rounded
to 600 but the 1 cent is not deducted. In fact 49.99 is added. I end up with
649.99

Don't know whether my test for under 50 is at fault
or the ELSE statement.

Can someone please help?

Jack

"Jack Williamson" <jackw10@netactive.co.za> wrote in message
news:1C648467-2AD0-4660-9637-B9764E4F7F1B@news.elevatesoft.com...
>I am having difficulty setting standard retail prices after rough
>re=pricing.
>
> I cannot get the following to work and assume I have misunderstaood
> something.
>
> SELLPINC1 is the field for the retail price includive of VAT
>
> UPDATE STOCK
> SET SELLPINC1 =
> (IF ((ROUND(SELLPINC1/10)*10) - (ROUND(SELLPINC1*100)/100) < 50 THEN
> (ROUND(SELLPINC1/100)*100) +
> 49.99 ELSE
> (ROUND(SELLPINC1/100)*100) - 0.01))
> WHERE SELLPINC1 > 499
>
> This works as long as the tens and units are less than 50
> If not it seems to round to the next hundred and
> then add 49.99 anyway.
>
> 544.35 becomes 549.99 as intended but 564.55 is rounded
> to 600 but the 1 cent is not deducted. In fact 49.99 is added. I end up
> with
> 649.99
>
euh... maybe I don't understand but 564.55

round(564.55/10) = round(56.455)= 56  | 56 * 10 = 560

round(564.55/100) = round(5.6455) = 6 | 6 * 100 = 600

560 - 600 = -40 which is smaller then 50 so the result is 649.99

round(564.55/100) = round(5.6455) = 6 | 6 * 100 = 600 | 600 + 49.99

Jack
> UPDATE STOCK
> SET SELLPINC1 =
> (IF ((ROUND(SELLPINC1/10)*10) - (ROUND(SELLPINC1*100)/100) < 50 THEN
(ROUND(SELLPINC1/100)*100) +
> 49.99 ELSE
> (ROUND(SELLPINC1/100)*100) - 0.01))
> WHERE SELLPINC1 > 499
>
> This works as long as the tens and units are less than 50
> If not it seems to round to the next hundred and
> then add 49.99 anyway.
>
> 544.35 becomes 549.99 as intended but 564.55 is rounded
> to 600 but the 1 cent is not deducted. In fact 49.99 is added. I end up
with
> 649.99

If you work it through the second case gives if(560-600) < 50 add 49.99 to
600 else ....

If you want everything between 0 and 49.99 to got to 49.99 and everything
between 50 and 99.98 to got to 99.99 then I think the following should work

UPDATE STOCK
SET SELLPINC1 =
(IF(SELLPINC1- (ROUND((SELLPINC1-49.999)/100)*100) < 50 THEN
(ROUND(SELLPINC1/100)*100) +
49.99 ELSE
(ROUND(SELLPINC1/100)*100) - 0.01))
WHERE SELLPINC1 > 499

John

"Frans van Daalen" <Account@is.invalid> wrote:


"Jack Williamson" <jackw10@netactive.co.za> wrote in message
news:1C648467-2AD0-4660-9637-B9764E4F7F1B@news.elevatesoft.com...
>I am having difficulty setting standard retail prices after rough
>re=pricing.
>
> I cannot get the following to work and assume I have misunderstaood
> something.
>
> SELLPINC1 is the field for the retail price includive of VAT
>
> UPDATE STOCK
> SET SELLPINC1 =
> (IF ((ROUND(SELLPINC1/10)*10) - (ROUND(SELLPINC1*100)/100) < 50 THEN
> (ROUND(SELLPINC1/100)*100) +
> 49.99 ELSE
> (ROUND(SELLPINC1/100)*100) - 0.01))
> WHERE SELLPINC1 > 499
>
> This works as long as the tens and units are less than 50
> If not it seems to round to the next hundred and
> then add 49.99 anyway.
>
> 544.35 becomes 549.99 as intended but 564.55 is rounded
> to 600 but the 1 cent is not deducted. In fact 49.99 is added. I end up
> with
> 649.99
>
euh... maybe I don't understand but 564.55

round(564.55/10) = round(56.455)= 56  | 56 * 10 = 560

round(564.55/100) = round(5.6455) = 6 | 6 * 100 = 600

560 - 600 = -40 which is smaller then 50 so the result is 649.99

round(564.55/100) = round(5.6455) = 6 | 6 * 100 = 600 | 600 + 49.99

Thanks very much for your help. Computers often do what they are told rather than what I want!

Jack

"John Hay" <j.haywithoutdot@crbsolutionsremoveallthis.co.uk> wrote:

Jack
> UPDATE STOCK
> SET SELLPINC1 =
> (IF ((ROUND(SELLPINC1/10)*10) - (ROUND(SELLPINC1*100)/100) < 50 THEN
(ROUND(SELLPINC1/100)*100) +
> 49.99 ELSE
> (ROUND(SELLPINC1/100)*100) - 0.01))
> WHERE SELLPINC1 > 499
>
> This works as long as the tens and units are less than 50
> If not it seems to round to the next hundred and
> then add 49.99 anyway.
>
> 544.35 becomes 549.99 as intended but 564.55 is rounded
> to 600 but the 1 cent is not deducted. In fact 49.99 is added. I end up
with
> 649.99

If you work it through the second case gives if(560-600) < 50 add 49.99 to
600 else ....

If you want everything between 0 and 49.99 to got to 49.99 and everything
between 50 and 99.98 to got to 99.99 then I think the following should work

UPDATE STOCK
SET SELLPINC1 =
(IF(SELLPINC1- (ROUND((SELLPINC1-49.999)/100)*100) < 50 THEN
(ROUND(SELLPINC1/100)*100) +
49.99 ELSE
(ROUND(SELLPINC1/100)*100) - 0.01))
WHERE SELLPINC1 > 499

John

Hi John

Thanks very much. I could have stared at my statement for 6 months and not figgured it out.
That will make life a lot easier.

Regards

Jack