1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Calculate working days based on Holiday table.

Discussion in 'SQL PL/SQL' started by Amit.Sagpariya, Feb 11, 2009.

  1. Amit.Sagpariya

    Amit.Sagpariya Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    I need to write a Oracle query/function as per below requirement. My requirement is to find out the working days for given day (sysdate) & extract previous day/week/month & year date.

    I have a holiday table, which has 2 columns.
    1. Date -> which contains date
    2. Desc -> which contains holiday desc

    For example:

    Today is 11-02-2009.

    Day : Now i want to find out previous day (ie 10-02-2009). if 10th-Feb is holiday in Holiday table it should return 09-02-2009. If 09-02-2009 is holiday in Holiday table, it should return 08-02-2009.

    Week : On 11-02-2009, its Wednesday. I need to find out last week's date which has day Wednesday (ie. 04-02-2009). If it is holiday in holiday table , then it should return 28-01-2009.

    Month : on 11-02-2009, Its 2nd week & 3rd days of the week. (ie, Mon-1, Tue-2, Wed-3). Now i want to find out date of last month's 2nd week & 3rd day, ie. 07-Jan-2009. If it is holiday in holiday list, it should return 10-Dec-2008.

    Year : It should return 11-02-2008. If it is holiday, it should return 11-02-2007.

    I know Oracle Date function can be used, but not able to find out how to go ahead if previous one is holiday. Can someone help me... i am not falimiler with Oracle more

    ---

    I have written function for Day: Logic is right but it is not returning the date.
    Code (Text):

    CREATE OR REPLACE FUNCTION ret_val (hol DATE)
       RETURN DATE
    IS
       d_date   DATE;
       m_date   DATE;
       m        NUMBER := 1;
    BEGIN
       LOOP
          DBMS_OUTPUT.put_line ('values of m - before SELECT : ' || TO_CHAR (m));

          SELECT h_date
            INTO d_date
            FROM hol_list
           WHERE TO_CHAR (h_date, 'YYYYMMDD') = TO_CHAR (hol, 'YYYYMMDD') - m;

          DBMS_OUTPUT.put_line ('d_date from hol_list table : '
                                || TO_CHAR (d_date)
                               );

          IF (d_date IS NOT NULL)
          THEN
             m_date := d_date - 1;
             DBMS_OUTPUT.put_line (   'm_date in 1st if condition : '
                                   || TO_CHAR (m_date)
                                  );
          END IF;

          IF (d_date IS NULL)
          THEN
             DBMS_OUTPUT.put_line ('entering into 2nd IF');
             m_date := hol - m;
             DBMS_OUTPUT.put_line (   'm_date in 2nd if condition : '
                                   || TO_CHAR (m_date)
                                  );
          END IF;

          DBMS_OUTPUT.put_line ('values of m - before increment: ' || TO_CHAR (m));
          m := m + 1;
          DBMS_OUTPUT.put_line ('values of m  - after increment: ' || TO_CHAR (m));
          EXIT WHEN d_date IS NULL;
       END LOOP;

       RETURN m_date;
    END;
     
  2. tyro

    tyro Forum Genius

    Messages:
    368
    Likes Received:
    20
    Trophy Points:
    260
    Location:
    India
    Hi

    you can create separate functions for all three of your requirements. I wrote one function for you. You can extend on this idea to get your week and month requirement too.

    Here's my data in holiday table
    Code (Text):

    select * from holiday

    2/4/2009 8:02:33    PM  test3
    2/10/2009 8:05:16   PM  test1
    2/9/2009 8:05:22    PM  test2
    1/7/2009            test4
    2/11/2008           test5
     
    note that today is 11th feb 2009. In my holiday table, I have 10th and 9th listed as holiday so I should get 8th Feb as my date. SO here's the function:

    Code (Text):

    CREATE OR REPLACE FUNCTION previous_day
       RETURN DATE
    AS
       CURSOR c1
       IS
          SELECT h_date
            FROM holiday;

       previous_date   DATE;
    BEGIN
       SELECT SYSDATE - 1
         INTO previous_date
         FROM DUAL;

       FOR c1rec IN c1
       LOOP
          BEGIN
             IF (TRUNC (previous_date) = TRUNC (c1rec.h_date))
             THEN
                SELECT (previous_date - 1)
                  INTO previous_date
                  FROM DUAL;
             END IF;
          END;
       END LOOP;

       RETURN previous_date;
    END previous_day;
    /
     
    Now let's check

    Code (Text):

    SELECT previous_day
      FROM DUAL;

    2/8/2009 9:17:16 PM

     
    Cheers!!! :)
     
  3. tyro

    tyro Forum Genius

    Messages:
    368
    Likes Received:
    20
    Trophy Points:
    260
    Location:
    India
    ok i went ahead and wrote the procedure for you. It outputs your date/week and year requirement correctly through dbms output.
    Code (Text):

    /* Formatted on 2009/02/11 22:06 (Formatter Plus v4.8.8) */
    CREATE OR REPLACE PROCEDURE previous_records
    AS
       CURSOR c1
       IS
          SELECT h_date
            FROM holiday;

       previous_date         DATE;
       previous_week_date    DATE;
       previous_month_date   DATE;
       previous_year_date    DATE;
    BEGIN
       SELECT SYSDATE - 1
         INTO previous_date
         FROM DUAL;

       SELECT SYSDATE - 7
         INTO previous_week_date
         FROM DUAL;

       SELECT ADD_MONTHS (SYSDATE, -1)
         INTO previous_month_date
         FROM DUAL;

       SELECT ADD_MONTHS (SYSDATE, -12)
         INTO previous_year_date
         FROM DUAL;

       FOR c1rec IN c1
       LOOP
          BEGIN
             IF (TRUNC (previous_date) = TRUNC (c1rec.h_date))
             THEN
                SELECT (previous_date - 1)
                  INTO previous_date
                  FROM DUAL;
             END IF;

             IF (TRUNC (previous_week_date) = TRUNC (c1rec.h_date))
             THEN
                SELECT (previous_week_date - 7)
                  INTO previous_week_date
                  FROM DUAL;
             END IF;

             IF (TRUNC (previous_month_date) = TRUNC (c1rec.h_date))
             THEN
                SELECT ADD_MONTHS (previous_month_date, -1)
                  INTO previous_month_date
                  FROM DUAL;
             END IF;

             IF (TRUNC (previous_year_date) = TRUNC (c1rec.h_date))
             THEN
                SELECT ADD_MONTHS (previous_year_date, - 12)
                  INTO previous_year_date
                  FROM DUAL;
             END IF;
          END;
       END LOOP;

       DBMS_OUTPUT.put_line ('Previous Day is:' || previous_date);
       DBMS_OUTPUT.put_line ('Previous Week Day is:' || previous_week_date);
       DBMS_OUTPUT.put_line ('Previous Month Day is:' || previous_month_date);
       DBMS_OUTPUT.put_line ('Previous Year Day is:' || previous_year_date);
    END previous_records;
    /
     
    Instead of dbms_output, you can insert into a table if you want. Your month requirement is a little more complicated. what i have done here is get you the same date on the previous month provided it's not a holiday. you will have to work a little to get that one right too.

    all d best.. :)

    cheers!
     
  4. Amit.Sagpariya

    Amit.Sagpariya Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    Hi both,

    Thank you very much. Its solved.
     
  5. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    OP is happy with the solution . Anyway let me try alternative of solution in SQL itself. I just produces the same scenario OP was describing.

    Code (Text):

    SQL> select * from HOLIDAY ;

    DY
    ---------
    04-FEB-09
    09-FEB-09
    10-FEB-09
    07-JAN-09

    SQL> SELECT ENT,MAX(DY)
      2   FROM (
      3  WITH INPD AS
      4  (SELECT TO_DATE('11-02-2009','DD-MM-YYYY')DY ,
      5          TO_CHAR(TO_DATE('11-02-2009','DD-MM-YYYY'),'W')WW,
      6          TO_CHAR(TO_DATE('11-02-2009','DD-MM-YYYY'),'D')DD
      7          FROM DUAL )
      8  SELECT 'DAY' ENT, DY-1 *LEVEL DY
      9  FROM  INPD
     10  CONNECT BY LEVEL<=10
     11  UNION
     12  SELECT 'WEEK', DY- (7 *LEVEL) DY
     13  FROM  INPD
     14  CONNECT BY LEVEL<=10
     15  UNION
     16  SELECT  'MONTH', TRUNC(TRUNC(ADD_MONTHS ( DY,-1*LEVEL),'MONTH') +((WW-1)*7),'DAY')+DD-1
     17  FROM  INPD
     18  CONNECT BY LEVEL<=10
     19  UNION
     20  SELECT 'YEAR' ,ADD_MONTHS ( DY,-12*LEVEL)  DY
     21  FROM  INPD
     22  CONNECT BY LEVEL<=10
     23   )x
     24  WHERE  NOT  EXISTS    ( select NULL
     25                          from  SCOTT.HOLIDAY   h
     26                          WHERE x.dy = h.dy
     27                       )
     28  GROUP BY  ent
     29  ORDER BY DECODE(ent,'DAY',1,'WEEK',2,'MONTH',2,'YEAR',4);

    ENT   MAX(DY)
    ----- ---------
    DAY   08-FEB-09
    WEEK  28-JAN-09
    MONTH 10-DEC-08
    YEAR  11-FEB-08

    SQL>
     
     
  6. tyro

    tyro Forum Genius

    Messages:
    368
    Likes Received:
    20
    Trophy Points:
    260
    Location:
    India
    hi Rajavu, that's a genius query if i may say so :D

    I figured out it could be done with a single select statement but i went for the simpler and easier to understand Procedure approach. Though yet got to learn a lot theway you cam up with this!
     
  7. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    It is better to use SQL wherever there is way. The above query go back to max 10 steps behind for each 'ent'. Also satisfy the OP's requirement on MONTH also satisfied here

    If this is genius query , there could be super genius query form some body else :)