COLUMN ppg FORMAT $999.00
COLUMN car_1_gpy FORMAT 99,999
COLUMN car_2_gpy FORMAT 99,999
COLUMN car_1_cpy FORMAT $999,999.00
COLUMN car_2_cpy FORMAT $999,999.00
COLUMN car_1_cpm FORMAT 999.00
COLUMN car_2_cpm FORMAT 999.00
SELECT
car_1,
car_2,
ppg,
mpy,
ROUND( ( mpy / car_1 ) ) car_1_gpy,
ROUND( ( mpy / car_2 ) ) car_2_gpy,
ROUND( ( ( mpy / car_1 ) * ppg ), 2 ) car_1_cpy,
ROUND( ( ( mpy / car_2 ) * ppg ), 2 ) car_2_cpy,
ROUND( ( ( mpy / car_1 ) * ppg ) / mpy, 2 ) car_1_cpm,
ROUND( ( ( mpy / car_2 ) * ppg ) / mpy, 2 ) car_2_cpm
FROM
(
SELECT
rownum car_1,
LAG( rownum, 5 ) OVER
( PARTITION BY NULL
ORDER BY rownum ) car_2
FROM dual
CONNECT BY LEVEL <= 50
) car_miles,
(
SELECT
3.99 ppg,
15000 mpy
FROM dual
) constants
/
CAR_1 CAR_2 CAR_1_GPY CAR_2_GPY CAR_1_CPY CAR_2_CPY CAR_1_CPM CAR_2_CPM
---------- ---------- --------- --------- ------------ ------------ --------- ---------
1 15,000 $59,850.00 3.99
2 7,500 $29,925.00 2.00
3 5,000 $19,950.00 1.33
4 3,750 $14,962.50 1.00
5 3,000 $11,970.00 .80
6 1 2,500 15,000 $9,975.00 $59,850.00 .67 3.99
7 2 2,143 7,500 $8,550.00 $29,925.00 .57 2.00
8 3 1,875 5,000 $7,481.25 $19,950.00 .50 1.33
9 4 1,667 3,750 $6,650.00 $14,962.50 .44 1.00
10 5 1,500 3,000 $5,985.00 $11,970.00 .40 .80
11 6 1,364 2,500 $5,440.91 $9,975.00 .36 .67
12 7 1,250 2,143 $4,987.50 $8,550.00 .33 .57
13 8 1,154 1,875 $4,603.85 $7,481.25 .31 .50
14 9 1,071 1,667 $4,275.00 $6,650.00 .28 .44
15 10 1,000 1,500 $3,990.00 $5,985.00 .27 .40
16 11 938 1,364 $3,740.63 $5,440.91 .25 .36
17 12 882 1,250 $3,520.59 $4,987.50 .23 .33
18 13 833 1,154 $3,325.00 $4,603.85 .22 .31
19 14 789 1,071 $3,150.00 $4,275.00 .21 .28
20 15 750 1,000 $2,992.50 $3,990.00 .20 .27
21 16 714 938 $2,850.00 $3,740.63 .19 .25
22 17 682 882 $2,720.45 $3,520.59 .18 .23
23 18 652 833 $2,602.17 $3,325.00 .17 .22
24 19 625 789 $2,493.75 $3,150.00 .17 .21
25 20 600 750 $2,394.00 $2,992.50 .16 .20
26 21 577 714 $2,301.92 $2,850.00 .15 .19
27 22 556 682 $2,216.67 $2,720.45 .15 .18
28 23 536 652 $2,137.50 $2,602.17 .14 .17
29 24 517 625 $2,063.79 $2,493.75 .14 .17
30 25 500 600 $1,995.00 $2,394.00 .13 .16
31 26 484 577 $1,930.65 $2,301.92 .13 .15
32 27 469 556 $1,870.31 $2,216.67 .12 .15
33 28 455 536 $1,813.64 $2,137.50 .12 .14
34 29 441 517 $1,760.29 $2,063.79 .12 .14
35 30 429 500 $1,710.00 $1,995.00 .11 .13
36 31 417 484 $1,662.50 $1,930.65 .11 .13
37 32 405 469 $1,617.57 $1,870.31 .11 .12
38 33 395 455 $1,575.00 $1,813.64 .11 .12
39 34 385 441 $1,534.62 $1,760.29 .10 .12
40 35 375 429 $1,496.25 $1,710.00 .10 .11
41 36 366 417 $1,459.76 $1,662.50 .10 .11
42 37 357 405 $1,425.00 $1,617.57 .09 .11
43 38 349 395 $1,391.86 $1,575.00 .09 .11
44 39 341 385 $1,360.23 $1,534.62 .09 .10
45 40 333 375 $1,330.00 $1,496.25 .09 .10
46 41 326 366 $1,301.09 $1,459.76 .09 .10
47 42 319 357 $1,273.40 $1,425.00 .08 .09
48 43 313 349 $1,246.88 $1,391.86 .08 .09
49 44 306 341 $1,221.43 $1,360.23 .08 .09
50 45 300 333 $1,197.00 $1,330.00 .08 .09Obviously this isn't terribly difficult. You can do lots with the results as well.
Three or four years ago I would have created a table and a function to do this. Now I can do it "virtually."
I love SQL...
*Key:
car_1 = miles for car 1
car_2 = miles for car 2
car_1_gpy = gallons per year car 1
car_2_gpy = gallons per year car 2
car_1_cpy = cost per year car 1 (gas)
car_2_cpy = cost per year car 2 (gas)
car_1_cpm = cost per mile car 1
car_2_cpm = cost per mile car 2
Wednesday, June 11, 2008
SQL for Buying a New Car
Two weeks ago I purchased a new car. The main reason behind that was that I drove a 1996 Nissan P.O.S. (not point of sale either). I now had a 30+ mile commute each way and I needed a reliable car.
A big factor was gas mileage.
Two brands stood out, Honda and Toyota. Honda has the Civic Hybrid and Toyota has the Prius (even though I don't care much for the design). The Corolla and Civic were the top two cars.
So after visiting those two dealers to look at cars, I stopped by the Mazda dealer to see if they had anything with MPG greater than 30. I drove the Mazda3 and it was nice (everything would be nice compared to my POS). Salesman offered to run the numbers and I said OK.
The point of this rambling, is that I want to know what it will cost based on mileage. Of course I didn't have my laptop, so I couldn't run the numbers (and I hadn't previously). I did it in Excel, but wanted to try it out in SQL.
Here's the result:
So what did ya get? car_1 or car_2
ReplyDeleteIt's just a shame we can't Flashback the gas prices too ;)
ReplyDelete@tim
ReplyDeleteUmmm...car 1? (the mazda 3)
@john
Yes, that would be awesome!