Monday, October 23, 2017

Query to display Calander for Current Year Using SQL

  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);

Sample Output:-

No comments:

Post a Comment

SupplierAddressImportTemplate.xlsm South Africa Suburb Field mapping in POZ_SUPPLIER_ADDRESSES_INT

Suburb mpping in Supplier Address Import Template will be mapped to Address Element Attribute2 (HZ_LOCATIONS. ADDR_ELEMENT_ATTRIBUTE2)