ORACLENERD: SQL: Calculate Wasted Time

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