5 Replies Latest reply: Dec 6, 2012 8:55 PM by 962091 RSS

    Scheduling a concurrent request for last WORKING day of each month

    962091
      I need to schedule a report to run each Monday at 6am (easy) and the last working day of each month and it is the latter I can not work out how to do.

      I have read around and understand to use Sysadmin --> Concurrent --> Manager --> Workshifts to create a special manager however still can not see how to use the logic of last working day.
        • 1. Re: Scheduling a concurrent request for last WORKING day of each month
          902404
          Hope this query helps you.

          select max(bus_day) from
          (select * from
          (select trunc(sysdate,'year') + ROWNUM - 1 bus_day,
          to_char(trunc(sysdate,'year') + ROWNUM - 1,'D') d
          from all_objects
          where rownum <=
          (select add_months(trunc(sysdate,'year'), 12) -
          trunc(sysdate,'year')
          from dual
          )
          )
          WHERE D NOT IN ('1','7')
          and to_char(bus_day,'MON') = 'NOV' -- MOnth
          )
          • 2. Re: Scheduling a concurrent request for last WORKING day of each month
            902404
            Below code gives you last working Monday of every month
            ----------------------------------------------------------------------------------
            select max(bus_day) from
            (select * from
            (select trunc(sysdate,'year') + ROWNUM - 1 bus_day,
            to_char(trunc(sysdate,'year') + ROWNUM - 1,'D') d
            from all_objects
            where rownum <=
            (select add_months(trunc(sysdate,'year'), 12) -
            trunc(sysdate,'year')
            from dual
            )
            )
            WHERE D NOT IN ('1','7')
            AND D IN (2)
            and to_char(bus_day,'MON') = 'NOV' -- MOnth
            );

            below code gives you last working day of every month
            -------------------------------------------------------------------------------------
            select max(bus_day) from
            (select * from
            (select trunc(sysdate,'year') + ROWNUM - 1 bus_day,
            to_char(trunc(sysdate,'year') + ROWNUM - 1,'D') d
            from all_objects
            where rownum <=
            (select add_months(trunc(sysdate,'year'), 12) -
            trunc(sysdate,'year')
            from dual
            )
            )
            WHERE D NOT IN ('1','7')
            --AND D IN (2)
            and to_char(bus_day,'MON') = 'NOV' -- MOnth
            );

            Thanks/Suresh
            • 3. Re: Scheduling a concurrent request for last WORKING day of each month
              962091
              Thanks Suresh
              What would be the next step to then package this so I can set up a recurring concurrent request to be emailed on the last working day of the each month?
              • 4. Re: Scheduling a concurrent request for last WORKING day of each month
                902404
                Hi,
                Could you please tell us your exact requirement which helps us to understand better.

                Pls mark helpful/correct if you like the feedback.

                /Suresh
                • 5. Re: Scheduling a concurrent request for last WORKING day of each month
                  962091
                  I have a report that has been developed for the business. The report is scheduled to run each Monday and emailed to one end user. This user then emails the report manually to a group of end users. On the last working day of the month the end user manually runs the report and manually emails it to the same group of people.

                  To remove the need for manual work through System Administration responsibility / Concurrent / Requests I can schedule the report to run each Monday and email directly to the group of end users, removing the need for the middle person to manually send on.

                  Is there a way to use the sql you have provided to package and then use in the same manner - schedule the report to run each last working day of the month and email directly to the group of end users.

                  If so are you able to provide a step by step guide to do this. Note I am not technical but can call on a colleague to help.

                  Thanks again