Monday, October 27, 2014

Calendar Query in Oracle


SELECT
  LPAD (MONTH, 20 - (20 - LENGTH (MONTH)) / 2) MONTH,
  "Sun",
  "Mon",
  "Tue",
  "Wed",
  "Thu",
  "Fri",
  "Sat"
FROM
  (SELECT TO_CHAR (dt, 'fmMonthfm YYYY') MONTH,
    TO_CHAR (dt + 1, 'iw') week,
    MAX (DECODE (TO_CHAR (dt, 'd'), '1', LPAD (TO_CHAR (dt, 'fmdd'), 2) ) ) "Sun",
    MAX (DECODE (TO_CHAR (dt, 'd'), '2', LPAD (TO_CHAR (dt, 'fmdd'), 2) ) ) "Mon",
    MAX (DECODE (TO_CHAR (dt, 'd'), '3', LPAD (TO_CHAR (dt, 'fmdd'), 2) ) ) "Tue",
    MAX (DECODE (TO_CHAR (dt, 'd'), '4', LPAD (TO_CHAR (dt, 'fmdd'), 2) ) ) "Wed",
    MAX (DECODE (TO_CHAR (dt, 'd'), '5', LPAD (TO_CHAR (dt, 'fmdd'), 2) ) ) "Thu",
    MAX (DECODE (TO_CHAR (dt, 'd'), '6', LPAD (TO_CHAR (dt, 'fmdd'), 2) ) ) "Fri",
    MAX (DECODE (TO_CHAR (dt, 'd'), '7', LPAD (TO_CHAR (dt, 'fmdd'), 2) ) ) "Sat"
  FROM
    (SELECT TRUNC (SYSDATE, 'y') - 1 + ROWNUM dt
    FROM all_objects
    WHERE ROWNUM <= ADD_MONTHS (TRUNC (SYSDATE, 'y'), 12) - TRUNC (SYSDATE, 'y')
    )
  GROUP BY TO_CHAR (dt, 'fmMonthfm YYYY'),
    TO_CHAR (dt + 1, 'iw')
  )
ORDER BY
  TO_DATE (MONTH, 'Month YYYY'),
  TO_NUMBER (week)

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect