Solution #1: Using Math formula and MODEL clause:
WITH m AS (
SELECT 63 AS cents
FROM dual
)
SELECT cents "Change",
Q "Quarters",
D "Dimes",
N "Nickels",
P "Pennies"
FROM m
MODEL
DIMENSION BY(0 AS dummy)
MEASURES(
cents,
CAST(0 AS NUMBER(3)) AS Q,
CAST(0 AS NUMBER(3)) AS D,
CAST(0 AS NUMBER(3)) AS N,
CAST(0 AS NUMBER(3)) AS P
)
RULES (
Q[0]=FLOOR(CENTS[0]/25),
D[0]=FLOOR((CENTS[0]-Q[0]*25)/10),
N[0]=FLOOR((CENTS[0]-Q[0]*25-D[0]*10)/5),
P[0]=(CENTS[0]-Q[0]*25-D[0]*10-N[0]*5)
)
Change | Quarters | Dimes | Nickels | Pennies | 63 | 2 | 1 | 0 | 3 |
---|
Solution #1a: Using Math formula and MODEL clause - for all change values between 1 and 99
WITH m AS (
SELECT LEVEL cents
FROM dual
CONNECT BY LEVEL<=99
)
SELECT cents "Change",
Q "Quarters",
D "Dimes",
N "Nickels",
P "Pennies"
FROM m
MODEL
PARTITION BY(ROWNUM AS rn)
DIMENSION BY(0 AS dummy)
MEASURES(
cents,
CAST(0 AS NUMBER(3)) AS Q,
CAST(0 AS NUMBER(3)) AS D,
CAST(0 AS NUMBER(3)) AS N,
CAST(0 AS NUMBER(3)) AS P
)
RULES (
Q[0]=FLOOR(CENTS[0]/25),
D[0]=FLOOR((CENTS[0]-Q[0]*25)/10),
N[0]=FLOOR((CENTS[0]-Q[0]*25-D[0]*10)/5),
P[0]=(CENTS[0]-Q[0]*25-D[0]*10-N[0]*5)
)
ORDER BY 1
Change | Quarters | Dimes | Nickels | Pennies | 1 | 0 | 0 | 0 | 1 | 2 | 0 | 0 | 0 | 2 | 3 | 0 | 0 | 0 | 3 | 4 | 0 | 0 | 0 | 4 | 5 | 0 | 0 | 1 | 0 | 6 | 0 | 0 | 1 | 1 | 7 | 0 | 0 | 1 | 2 | 8 | 0 | 0 | 1 | 3 | 9 | 0 | 0 | 1 | 4 | 10 | 0 | 1 | 0 | 0 | 11 | 0 | 1 | 0 | 1 | 12 | 0 | 1 | 0 | 2 | 13 | 0 | 1 | 0 | 3 | 14 | 0 | 1 | 0 | 4 | 15 | 0 | 1 | 1 | 0 | 16 | 0 | 1 | 1 | 1 | 17 | 0 | 1 | 1 | 2 | 18 | 0 | 1 | 1 | 3 | 19 | 0 | 1 | 1 | 4 | 20 | 0 | 2 | 0 | 0 | 21 | 0 | 2 | 0 | 1 | 22 | 0 | 2 | 0 | 2 | 23 | 0 | 2 | 0 | 3 | 24 | 0 | 2 | 0 | 4 | 25 | 1 | 0 | 0 | 0 | 26 | 1 | 0 | 0 | 1 | 27 | 1 | 0 | 0 | 2 | 28 | 1 | 0 | 0 | 3 | 29 | 1 | 0 | 0 | 4 | 30 | 1 | 0 | 1 | 0 | 31 | 1 | 0 | 1 | 1 | 32 | 1 | 0 | 1 | 2 | 33 | 1 | 0 | 1 | 3 | 34 | 1 | 0 | 1 | 4 | 35 | 1 | 1 | 0 | 0 | 36 | 1 | 1 | 0 | 1 | 37 | 1 | 1 | 0 | 2 | 38 | 1 | 1 | 0 | 3 | 39 | 1 | 1 | 0 | 4 | 40 | 1 | 1 | 1 | 0 | 41 | 1 | 1 | 1 | 1 | 42 | 1 | 1 | 1 | 2 | 43 | 1 | 1 | 1 | 3 | 44 | 1 | 1 | 1 | 4 | 45 | 1 | 2 | 0 | 0 | 46 | 1 | 2 | 0 | 1 | 47 | 1 | 2 | 0 | 2 | 48 | 1 | 2 | 0 | 3 | 49 | 1 | 2 | 0 | 4 | 50 | 2 | 0 | 0 | 0 | 51 | 2 | 0 | 0 | 1 | 52 | 2 | 0 | 0 | 2 | 53 | 2 | 0 | 0 | 3 | 54 | 2 | 0 | 0 | 4 | 55 | 2 | 0 | 1 | 0 | 56 | 2 | 0 | 1 | 1 | 57 | 2 | 0 | 1 | 2 | 58 | 2 | 0 | 1 | 3 | 59 | 2 | 0 | 1 | 4 | 60 | 2 | 1 | 0 | 0 | 61 | 2 | 1 | 0 | 1 | 62 | 2 | 1 | 0 | 2 | 63 | 2 | 1 | 0 | 3 | 64 | 2 | 1 | 0 | 4 | 65 | 2 | 1 | 1 | 0 | 66 | 2 | 1 | 1 | 1 | 67 | 2 | 1 | 1 | 2 | 68 | 2 | 1 | 1 | 3 | 69 | 2 | 1 | 1 | 4 | 70 | 2 | 2 | 0 | 0 | 71 | 2 | 2 | 0 | 1 | 72 | 2 | 2 | 0 | 2 | 73 | 2 | 2 | 0 | 3 | 74 | 2 | 2 | 0 | 4 | 75 | 3 | 0 | 0 | 0 | 76 | 3 | 0 | 0 | 1 | 77 | 3 | 0 | 0 | 2 | 78 | 3 | 0 | 0 | 3 | 79 | 3 | 0 | 0 | 4 | 80 | 3 | 0 | 1 | 0 | 81 | 3 | 0 | 1 | 1 | 82 | 3 | 0 | 1 | 2 | 83 | 3 | 0 | 1 | 3 | 84 | 3 | 0 | 1 | 4 | 85 | 3 | 1 | 0 | 0 | 86 | 3 | 1 | 0 | 1 | 87 | 3 | 1 | 0 | 2 | 88 | 3 | 1 | 0 | 3 | 89 | 3 | 1 | 0 | 4 | 90 | 3 | 1 | 1 | 0 | 91 | 3 | 1 | 1 | 1 | 92 | 3 | 1 | 1 | 2 | 93 | 3 | 1 | 1 | 3 | 94 | 3 | 1 | 1 | 4 | 95 | 3 | 2 | 0 | 0 | 96 | 3 | 2 | 0 | 1 | 97 | 3 | 2 | 0 | 2 | 98 | 3 | 2 | 0 | 3 | 99 | 3 | 2 | 0 | 4 |
---|
Solution #2: Using Enhanced Math formula
WITH a AS (
SELECT 63 cents
FROM dual
)
SELECT a.cents "Change",
FLOOR(a.cents/25) "Quarters",
FLOOR(MOD(a.cents,25)/10) "Dimes",
FLOOR(MOD(MOD(a.cents,25),10)/5) "Nickels",
MOD(MOD(MOD(a.cents,25),10),5) "Pennies"
FROM a
Change | Quarters | Dimes | Nickels | Pennies | 63 | 2 | 1 | 0 | 3 |
---|
Solution #2a: Using Enhanced Math formula - for all change amount between 1 and 99
WITH a AS (
SELECT LEVEL cents
FROM dual
CONNECT BY LEVEL<100
)
SELECT a.cents "Change",
FLOOR(a.cents/25) "Quarters",
FLOOR(MOD(a.cents,25)/10) "Dimes",
FLOOR(MOD(MOD(a.cents,25),10)/5) "Nickels",
MOD(MOD(MOD(a.cents,25),10),5) "Pennies"
FROM a
ORDER BY a.cents
Change | Quarters | Dimes | Nickels | Pennies | 1 | 0 | 0 | 0 | 1 | 2 | 0 | 0 | 0 | 2 | 3 | 0 | 0 | 0 | 3 | 4 | 0 | 0 | 0 | 4 | 5 | 0 | 0 | 1 | 0 | 6 | 0 | 0 | 1 | 1 | 7 | 0 | 0 | 1 | 2 | 8 | 0 | 0 | 1 | 3 | 9 | 0 | 0 | 1 | 4 | 10 | 0 | 1 | 0 | 0 | 11 | 0 | 1 | 0 | 1 | 12 | 0 | 1 | 0 | 2 | 13 | 0 | 1 | 0 | 3 | 14 | 0 | 1 | 0 | 4 | 15 | 0 | 1 | 1 | 0 | 16 | 0 | 1 | 1 | 1 | 17 | 0 | 1 | 1 | 2 | 18 | 0 | 1 | 1 | 3 | 19 | 0 | 1 | 1 | 4 | 20 | 0 | 2 | 0 | 0 | 21 | 0 | 2 | 0 | 1 | 22 | 0 | 2 | 0 | 2 | 23 | 0 | 2 | 0 | 3 | 24 | 0 | 2 | 0 | 4 | 25 | 1 | 0 | 0 | 0 | 26 | 1 | 0 | 0 | 1 | 27 | 1 | 0 | 0 | 2 | 28 | 1 | 0 | 0 | 3 | 29 | 1 | 0 | 0 | 4 | 30 | 1 | 0 | 1 | 0 | 31 | 1 | 0 | 1 | 1 | 32 | 1 | 0 | 1 | 2 | 33 | 1 | 0 | 1 | 3 | 34 | 1 | 0 | 1 | 4 | 35 | 1 | 1 | 0 | 0 | 36 | 1 | 1 | 0 | 1 | 37 | 1 | 1 | 0 | 2 | 38 | 1 | 1 | 0 | 3 | 39 | 1 | 1 | 0 | 4 | 40 | 1 | 1 | 1 | 0 | 41 | 1 | 1 | 1 | 1 | 42 | 1 | 1 | 1 | 2 | 43 | 1 | 1 | 1 | 3 | 44 | 1 | 1 | 1 | 4 | 45 | 1 | 2 | 0 | 0 | 46 | 1 | 2 | 0 | 1 | 47 | 1 | 2 | 0 | 2 | 48 | 1 | 2 | 0 | 3 | 49 | 1 | 2 | 0 | 4 | 50 | 2 | 0 | 0 | 0 | 51 | 2 | 0 | 0 | 1 | 52 | 2 | 0 | 0 | 2 | 53 | 2 | 0 | 0 | 3 | 54 | 2 | 0 | 0 | 4 | 55 | 2 | 0 | 1 | 0 | 56 | 2 | 0 | 1 | 1 | 57 | 2 | 0 | 1 | 2 | 58 | 2 | 0 | 1 | 3 | 59 | 2 | 0 | 1 | 4 | 60 | 2 | 1 | 0 | 0 | 61 | 2 | 1 | 0 | 1 | 62 | 2 | 1 | 0 | 2 | 63 | 2 | 1 | 0 | 3 | 64 | 2 | 1 | 0 | 4 | 65 | 2 | 1 | 1 | 0 | 66 | 2 | 1 | 1 | 1 | 67 | 2 | 1 | 1 | 2 | 68 | 2 | 1 | 1 | 3 | 69 | 2 | 1 | 1 | 4 | 70 | 2 | 2 | 0 | 0 | 71 | 2 | 2 | 0 | 1 | 72 | 2 | 2 | 0 | 2 | 73 | 2 | 2 | 0 | 3 | 74 | 2 | 2 | 0 | 4 | 75 | 3 | 0 | 0 | 0 | 76 | 3 | 0 | 0 | 1 | 77 | 3 | 0 | 0 | 2 | 78 | 3 | 0 | 0 | 3 | 79 | 3 | 0 | 0 | 4 | 80 | 3 | 0 | 1 | 0 | 81 | 3 | 0 | 1 | 1 | 82 | 3 | 0 | 1 | 2 | 83 | 3 | 0 | 1 | 3 | 84 | 3 | 0 | 1 | 4 | 85 | 3 | 1 | 0 | 0 | 86 | 3 | 1 | 0 | 1 | 87 | 3 | 1 | 0 | 2 | 88 | 3 | 1 | 0 | 3 | 89 | 3 | 1 | 0 | 4 | 90 | 3 | 1 | 1 | 0 | 91 | 3 | 1 | 1 | 1 | 92 | 3 | 1 | 1 | 2 | 93 | 3 | 1 | 1 | 3 | 94 | 3 | 1 | 1 | 4 | 95 | 3 | 2 | 0 | 0 | 96 | 3 | 2 | 0 | 1 | 97 | 3 | 2 | 0 | 2 | 98 | 3 | 2 | 0 | 3 | 99 | 3 | 2 | 0 | 4 |
---|
Solution #3: Using Cartesian Product and Top Record pattern approach
WITH r AS (
SELECT LEVEL-1 n
FROM dual
CONNECT BY LEVEL<=20
), x AS (
SELECT q.n "Quarters", d.n "Dimes", n.n "Nickels", p.n "Pennies",
RANK() OVER(ORDER BY q.n + d.n + n.n + p.n) rk
FROM r q, r d, r n, r p
WHERE q.n<=3
AND d.n<=9
AND n.n<=19 --not really needed
AND p.n<=4
AND q.n*25 + d.n*10 + n.n*5 + p.n = 63 --amount of change
)
SELECT "Quarters", "Dimes", "Nickels", "Pennies"
FROM x
WHERE rk=1
Quarters | Dimes | Nickels | Pennies | 2 | 1 | 0 | 3 |
---|
Solution #3a: Using Cartesian Product and Top Record pattern approach - for all change amounts between 1 and 99 cents
WITH r AS ( -- this range is to be reused 5 times in this query
SELECT LEVEL-1 n
FROM dual
CONNECT BY LEVEL<=100
), x AS (
SELECT c.n "Change", q.n "Quarters", d.n "Dimes",
n.n "Nickels", p.n "Pennies",
RANK() OVER(PARTITION BY c.n ORDER BY q.n + d.n + n.n + p.n) rk
FROM r q, r d, r n, r p, r c
WHERE q.n<=3
AND d.n<=9
AND n.n<=19 --now it is needed
AND p.n<=4 AND q.n*25 + d.n*10 + n.n*5 + p.n = c.n --amount of change
AND c.n>0
)
SELECT "Change", "Quarters", "Dimes", "Nickels", "Pennies"
FROM x
WHERE rk=1
ORDER BY 1
Change | Quarters | Dimes | Nickels | Pennies | 1 | 0 | 0 | 0 | 1 | 2 | 0 | 0 | 0 | 2 | 3 | 0 | 0 | 0 | 3 | 4 | 0 | 0 | 0 | 4 | 5 | 0 | 0 | 1 | 0 | 6 | 0 | 0 | 1 | 1 | 7 | 0 | 0 | 1 | 2 | 8 | 0 | 0 | 1 | 3 | 9 | 0 | 0 | 1 | 4 | 10 | 0 | 1 | 0 | 0 | 11 | 0 | 1 | 0 | 1 | 12 | 0 | 1 | 0 | 2 | 13 | 0 | 1 | 0 | 3 | 14 | 0 | 1 | 0 | 4 | 15 | 0 | 1 | 1 | 0 | 16 | 0 | 1 | 1 | 1 | 17 | 0 | 1 | 1 | 2 | 18 | 0 | 1 | 1 | 3 | 19 | 0 | 1 | 1 | 4 | 20 | 0 | 2 | 0 | 0 | 21 | 0 | 2 | 0 | 1 | 22 | 0 | 2 | 0 | 2 | 23 | 0 | 2 | 0 | 3 | 24 | 0 | 2 | 0 | 4 | 25 | 1 | 0 | 0 | 0 | 26 | 1 | 0 | 0 | 1 | 27 | 1 | 0 | 0 | 2 | 28 | 1 | 0 | 0 | 3 | 29 | 1 | 0 | 0 | 4 | 30 | 1 | 0 | 1 | 0 | 31 | 1 | 0 | 1 | 1 | 32 | 1 | 0 | 1 | 2 | 33 | 1 | 0 | 1 | 3 | 34 | 1 | 0 | 1 | 4 | 35 | 1 | 1 | 0 | 0 | 36 | 1 | 1 | 0 | 1 | 37 | 1 | 1 | 0 | 2 | 38 | 1 | 1 | 0 | 3 | 39 | 1 | 1 | 0 | 4 | 40 | 1 | 1 | 1 | 0 | 41 | 1 | 1 | 1 | 1 | 42 | 1 | 1 | 1 | 2 | 43 | 1 | 1 | 1 | 3 | 44 | 1 | 1 | 1 | 4 | 45 | 1 | 2 | 0 | 0 | 46 | 1 | 2 | 0 | 1 | 47 | 1 | 2 | 0 | 2 | 48 | 1 | 2 | 0 | 3 | 49 | 1 | 2 | 0 | 4 | 50 | 2 | 0 | 0 | 0 | 51 | 2 | 0 | 0 | 1 | 52 | 2 | 0 | 0 | 2 | 53 | 2 | 0 | 0 | 3 | 54 | 2 | 0 | 0 | 4 | 55 | 2 | 0 | 1 | 0 | 56 | 2 | 0 | 1 | 1 | 57 | 2 | 0 | 1 | 2 | 58 | 2 | 0 | 1 | 3 | 59 | 2 | 0 | 1 | 4 | 60 | 2 | 1 | 0 | 0 | 61 | 2 | 1 | 0 | 1 | 62 | 2 | 1 | 0 | 2 | 63 | 2 | 1 | 0 | 3 | 64 | 2 | 1 | 0 | 4 | 65 | 2 | 1 | 1 | 0 | 66 | 2 | 1 | 1 | 1 | 67 | 2 | 1 | 1 | 2 | 68 | 2 | 1 | 1 | 3 | 69 | 2 | 1 | 1 | 4 | 70 | 2 | 2 | 0 | 0 | 71 | 2 | 2 | 0 | 1 | 72 | 2 | 2 | 0 | 2 | 73 | 2 | 2 | 0 | 3 | 74 | 2 | 2 | 0 | 4 | 75 | 3 | 0 | 0 | 0 | 76 | 3 | 0 | 0 | 1 | 77 | 3 | 0 | 0 | 2 | 78 | 3 | 0 | 0 | 3 | 79 | 3 | 0 | 0 | 4 | 80 | 3 | 0 | 1 | 0 | 81 | 3 | 0 | 1 | 1 | 82 | 3 | 0 | 1 | 2 | 83 | 3 | 0 | 1 | 3 | 84 | 3 | 0 | 1 | 4 | 85 | 3 | 1 | 0 | 0 | 86 | 3 | 1 | 0 | 1 | 87 | 3 | 1 | 0 | 2 | 88 | 3 | 1 | 0 | 3 | 89 | 3 | 1 | 0 | 4 | 90 | 3 | 1 | 1 | 0 | 91 | 3 | 1 | 1 | 1 | 92 | 3 | 1 | 1 | 2 | 93 | 3 | 1 | 1 | 3 | 94 | 3 | 1 | 1 | 4 | 95 | 3 | 2 | 0 | 0 | 96 | 3 | 2 | 0 | 1 | 97 | 3 | 2 | 0 | 2 | 98 | 3 | 2 | 0 | 3 | 99 | 3 | 2 | 0 | 4 |
---|