Thursday, September 17, 2009

SQL: Calculate Wasted Time

Inspired by this today:



I answered with this:



I started to do it in SQL, but then I got impatient. I finished it in Excel just to get it done. Some time later, I decided to do it in SQL...just because it's fairly easy.

I know there are better/different ways to do it, so share please.


SELECT
ROUND( d * 10 * 14 * sal_per_minute, 2 ) yearly_cost,
ROUND( ( d / 4 ) * 10 * 14 * sal_per_minute, 2 ) quarterly_cost,
ROUND( ( d / 12 ) * 10 * 14 * sal_per_minute, 2 ) monthly_cost,
ROUND( 5 * 10 * 14 * sal_per_minute, 2 ) weekly_cost,
ROUND( 10 * 14 * sal_per_minute, 2 ) daily_cost
FROM
(
SELECT
COUNT( CASE
WHEN TO_CHAR( s, 'D' ) NOT IN ( 1, 7 )
THEN 1
ELSE NULL
END ) - 10 d,
MAX( sal ) sal_per_minute
FROM
(
SELECT
TO_DATE( '31-DEC-2008', 'DD-MON-YYYY' ) + rownum s,
( 100000 / 2080 ) / 60 sal
FROM dual
CONNECT BY LEVEL <= 365
)
);

YEARLY_COST QUARTERLY_COST MONTHLY_COST WEEKLY_COST DAILY_COST
----------- -------------- ------------ ----------- ----------
28157.05 7039.26 2346.42 560.9 112.18
Those minutes are precious...so don't waste them.

No comments: