Formula for Buy 3 get 1 Free

user_6862366 0
Viewed 416

I'm trying to write PL/SQL to get how much you pay if you purchase a certain amount of items. If you purchase 3 items, you get 1 free.

Therefore, for every 4th item purchased one of them is free. That means I pay for 3 items if 4 items are "purchased." If 10 items are attained then 8 should be paid for, 2 are free. 

p-b     v_p
***     ***
1        1                  = 1
2        2                  = 2
3        3                  = 3
4        3(1)               = 3
5        3(1) + 1           = 4
6        3(1) + 2           = 5
7        3(1) + 3           = 6
8        3(1) + 3(1)        = 6
9        3(1) + 3(1) + 1    = 7
10       3(1) + 3(1) + 2    = 8
11       3(1) + 3(1) + 3    = 9
12       3(1) + 3(1) + 3(1) = 9

I've got 

trunc(p-b / 3 * 2.4);

but my values are inconsistent.

Sometimes it works with

trunc(p-b / 3 * 2.25);

Am I doing the formula incorrectly? I'm supposed to be using trunc or mod.

oracle10g math plsql sql
4 Answers

I would use trunc and modulo

SELECT p-b, (trunc(p-b /4)) * 3 + mod(p-b, 4)
FROM your_table

demo

user_20912470

We can use the below formula to attain the result.

Assume n = p-b

n - ( n - mod( n, 4 ) ) / 4

Eg: 

 SELECT n, n - (n - mod(n,4))/4 result
      FROM (SELECT     LEVEL n
                  FROM DUAL
            CONNECT BY LEVEL <= 12)
user_11373080

in simple way the result is:

p-b - trunc(p-b / 4);
user_65452150

To calculate the amount of Payed/Free stuff: 

DECLARE
        nAmount NUMBER := '10';
        nPay NUMBER;
        nFree NUMBER;
BEGIN
        nPay := nAmount - TRUNC(nAmount / 4);
        nFree := TRUNC(nAmount / 4);
        DBMS_OUTPUT.PUT_LINE('Pay: ' || nPay);
        DBMS_OUTPUT.PUT_LINE('Free: ' || nFree);
END;
/
user_44532000
Related