Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 1 to 5 of 5 total |
Conditional statement for DBISAM 3 |
Fri, Nov 21 2008 3:09 AM | Permanent Link |
Jack Williamson | 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 |
Fri, Nov 21 2008 5:29 AM | Permanent Link |
"Frans van Daalen" | "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 |
Fri, Nov 21 2008 6:31 AM | Permanent Link |
"John Hay" | 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 |
Sat, Nov 22 2008 2:27 AM | Permanent Link |
Jack Williamson | "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 |
Sat, Nov 22 2008 2:30 AM | Permanent Link |
Jack Williamson | "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 |
This web page was last updated on Wednesday, May 15, 2024 at 08:40 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |