October 23, 2015 at 9:38 am
Hi All
Looking to update pricing with .99 for anything over a certain value and other prices for other values. This is the first pass on this I had. Does this seem a reasonable solution or how SHOULD it be done. The second part of the puzzle I have is how to resolve the output such as the price is .99 or .45 after I have added VAT. This has me a little stumped as I need to Take the Part Price and work out VAT, Add the two together then ceiling it. Then remove 1p and then remove the VAT to get the price of the part would need to be.
Sample of rounding up pricing using Ceiling. (Ceiling is being used as they want to round up...
CASE
WHEN [PPRC_WOverride] = 0 AND PPRC_Wholesale > 0.75 THEN (Ceiling(PPRC_Wholesale) - 0.01)
WHEN [PPRC_WOverride] = 0 AND PPRC_WHOLESALE BETWEEN 0.50 AND 0.74 THEN (Ceiling(PPRC_Wholesale) - 0.25)
WHEN [PPRC_WOverride] = 0 AND PPRC_WHOLESALE BETWEEN 0.26 AND 0.49 THEN (Ceiling(PPRC_Wholesale) - 0.55)
WHEN [PPRC_WOverride] = 0 AND PPRC_WHOLESALE BETWEEN 0.01 AND 0.25 THEN (Ceiling(PPRC_Wholesale) - 0.75)
ELSE PPRC_Wholesale
END AS [Wholesale]
October 23, 2015 at 10:04 am
This seems pretty simple but it would be helpful if you could include some sample data and the expected results. If you do that I'm confident that you'll get a good answer real quick.
-- Itzik Ben-Gan 2001
October 23, 2015 at 12:08 pm
Alan
The select on the table using my formula above is simple enough and returns the following.
PPRC_NumberPPRC_CurrencyPPRC_WholesaleWholesalePPRC_WOverride
1340055GBP76.8776.99000
1360025GBP137.98137.99000
What I am looking to do, is take the cost PPRC_Wholesale and do the calculation of adding on the VAT, getting the result, then using Ceiling -0.01 to get it to the .99 (As mentioned pricing up), then removing the VAT and I want to have a wholesale price that when we add the VAT back on gives us a figure of XXX.99
I suppose in theory it is something like and I have tried this and it does work (Or appears to, it is just hideous...)
((Ceiling(PPRC_Wholesale * 1.2) - PPRC_Wholesale * 0.2) - 0.01) + (PPRC_Wholesale * 0.2) AS [Wholesale RRPVAT]
October 23, 2015 at 12:25 pm
Degradable (10/23/2015)
AlanThe select on the table using my formula above is simple enough and returns the following.
PPRC_NumberPPRC_CurrencyPPRC_WholesaleWholesalePPRC_WOverride
1340055GBP76.8776.99000
1360025GBP137.98137.99000
What I am looking to do, is take the cost PPRC_Wholesale and do the calculation of adding on the VAT, getting the result, then using Ceiling -0.01 to get it to the .99 (As mentioned pricing up), then removing the VAT and I want to have a wholesale price that when we add the VAT back on gives us a figure of XXX.99
I suppose in theory it is something like and I have tried this and it does work (Or appears to, it is just hideous...)
((Ceiling(PPRC_Wholesale * 1.2) - PPRC_Wholesale * 0.2) - 0.01) + (PPRC_Wholesale * 0.2) AS [Wholesale RRPVAT]
I only got more confused with this post.
Your original formula, can be changed to this:
CASE WHEN [PPRC_WOverride] = 0
THEN CEILING(PPRC_Wholesale * 4) / 4
ELSE PPRC_Wholesale END
Your new formula, can be simplified to this:
CEILING( PPRC_Wholesale * 1.2) - .01
Here's what I used to test:
WITH
E(n) AS(
SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))E(n)
),
E2(n) AS(
SELECT a.n FROM E a, E b
),
E4(n) AS(
SELECT a.n FROM E2 a, E2 b
),
cteTally(PPRC_Wholesale, [PPRC_WOverride]) AS(
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL))/100.00 , 0
FROM E4
)
SELECT PPRC_Wholesale,
CASE WHEN [PPRC_WOverride] = 0
THEN CEILING(PPRC_Wholesale * 4) / 4
ELSE PPRC_Wholesale END,
((Ceiling(PPRC_Wholesale * 1.2) - PPRC_Wholesale * 0.2) - 0.01) + (PPRC_Wholesale * 0.2) AS [Wholesale RRPVAT] ,
CEILING( PPRC_Wholesale * 1.2) - .01 AS [Wholesale RRPVAT Simple]
FROM cteTally
October 27, 2015 at 7:42 am
I totally got myself confused with doing (Or trying to get precedence with brackets etc. Needless to say the answer I was searching for is the following.
This allows me to round up after Adding VAT, then remove 0.01 then remove the VAT and geta precise answer, which when I allow the VAT to be put back on again later (In a separate process) still ends in .99 (example £234.99
WHEN [PPRC_WOverride] = 0 AND PPRC_WHOLESALE > 0.75 THEN CAST(((Ceiling(PPRC_Wholesale * 1.2) - 0.01) * 0.8333333333)AS DECIMAL(18,4))
Regards
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply