14 Replies Latest reply: Jan 21, 2013 7:30 AM by Etbin RSS

    Using case with other tables

    985940
      Hi all,

      I have a query as follows:
      SELECT mydate Date_REC, SUM(d_num) intake, to_char(to_date(mydate, 'fmDD/MM/YYYY'), 'fmD') Daynum, to_char(to_date(mydate, 'fmDD/MM/YYYY'), 'DY') Day from (
      SELECT
      (case when to_char(to_date(date_rec, 'fmDD/MM/YYYY'), 'fmD') = '7' then to_date(date_rec, 'fmDD/MM/YYYY')+2 when to_char(to_date(date_rec, 'fmDD/MM/YYYY'), 'fmD') = '1' then to_date(date_rec, 'fmDD/MM/YYYY')+1 else to_date(date_rec, 'fmDD/MM/YYYY') end) as mydate,
      ...
      ...
      FROM
      tableA a LEFT JOIN tableB b
      On.. etc
      Basically, when the results are pulled, the dates are checked for each row, and if it's saturday (day = 7) then the date is changed to a monday (+2 days). If it's a sunday (day=1) then +1 day is added to make it a monday. This seems to work ok and is exactly what I want to see in the grouped results.

      Now, additionally to that, I want to bring in another table, called 'holidays'. This is a list of public holidays, with the structure (Date, Description).

      My requirements for each row are:

      Check if the date is either a saturday, a sunday or a day in the holidays table. If any of those is true, I want the date to become the next working day, as long as it isn't also in the holidays calendar.

      Can anyone help me alter my query to do that? It's getting a bit complicated for me now.. :)

      This is for use in Application Express 4.0, Oracle 11g.

      Many thanks!
        • 1. Re: Using case with other tables
          Etbin
          Something to play with NOT TESTED!
          with
          all_days as
          (select x.the_day,
                  case when h.holiday_date is not null
                       then 'holiday'
                       else x.free_day
                  end
             from (select low_date + level - 1 the_day,
                          case when to_char(low_date + level - 1,'DY') in ('SAT','SUN')
                               then 'weekend'
                          end free_day
                     from (select min(your_date_column) low_date,
                                  max(your_date_column) - min(your_date_column) + 1 number_of_days
                             from table_data
          /*                where <some condition> if you don't want to process all table rows */
                          ),
                          dual
                   connect by level <= number_of_days
                  ) x,
                  holiday_table h
            where x.the_day = h.holiday_date(+)
          )
          select your_date_column,
                 (select min(the_day)
                    from all_days
                   where the_day >= t.your_date_column
                     and free_day is null
                 ) next_working_day
            from table_data t
          Not very efficient. You'd be better off writing a function.
          Regards

          Etbin
          • 2. Re: Using case with other tables
            985940
            Thanks for the code.

            Would a function be any more efficient, if it's being called for every row?

            I haven't used SQL functions before, so lots to learn here.

            Thanks.
            • 3. Re: Using case with other tables
              Etbin
              The answer is the usual: it depends - almost always the correct one ;)
              you must choose between using an inline view and a function (both executed for each row)
              In your case you need just the next working day: the inline view can contain many rows while the function has to look no more than five (we have holidays on two successive days) days ahead.
              Trying both options should make your decision a bit easier
              the function might look like NOT TESTED !
              function is_holiday(p_date in date) return date is
                the_holiday date;
              begin
                select holiday_date
                  into the_holiday
                  from holidays_table
                 where holiday_date = p_date; /* missing in original post */
                  
                return the_holiday;
                
              exception
                when no_data_found
                then return null;
              end;
              
              function working_day(p_date in date,p_count in number default null) return date is
                c number := nvl(abs(p_count),1);
                d date := p_date;
              begin
                if nvl(p_count,0) = 0 and to_char(p_date,'DY') not in ('SAT','SUN') and is_holiday(p_date) is null then
                  return p_date;
                end if;
              
                while c > 0
                loop
                  d := d + sign(p_count);
                  if to_char(d,'DY') not in ('SAT','SUN') and is_holiday(d) is null then
                    c := c - 1;
                  end if;
                end loop;
               
                return d;
              end;
              Regards

              Etbin

              Edited by: Etbin on 20.1.2013 13:40
              where clause added
              • 4. Re: Using case with other tables
                Solomon Yakobson
                Etbin wrote:
                select holiday_date
                into the_holiday
                from holidays_table;
                You missed WHERE clause. And your working_day function is NLS-dependent. In general, it is better to have a single calendar funtion. Something like:
                create or replace
                function calendar(
                                  p_date in date
                                 )
                  return varchar2
                  is
                      v_retval varchar2(7);
                  begin
                      select  case count(*)
                                when 1 then 'HOLIDAY'
                                else case
                                       when p_date - trunc(p_date,'iw') >= 5 then 'WEEKEND'
                                       else 'WEEKDAY'
                                     end
                              end
                        into  v_retval
                        from  holidays_table
                        where dt = trunc(p_date);
                      return v_retval;
                end;
                /
                SY.
                • 5. Re: Using case with other tables
                  Etbin
                  You missed WHERE clause
                  Thanks for catching that.
                  The query was copy/pasted from my first (not posted) example where I used bulk collect to get all rows of holidays_table into a table of dates ...
                  but I changed my mind rereading the post because of OP's I haven't used SQL functions before, so lots to learn here. and decided to provide a more elementary example.
                  The same applies to the use of nls-dependent features as they make the example (at least IMO) more readable.
                  Now the OP has the chance to compare to a state-of-the-art example. (my proposal to use non nls-dependent solutions was rejected as too difficult to grasp at first sight at the company too)

                  Regards

                  Etbin
                  • 6. Re: Using case with other tables
                    985940
                    Thanks Etbin.

                    I guess the function like that might not be the most efficient, but it at least helps to reduce complexity somewhat, for now.

                    I will play around with the function to get it how I need it.

                    I assume I can then alter my original query to something along the lines of :
                    SELECT mydate Date_REC, SUM(d_num) intake, to_char(to_date(mydate, 'fmDD/MM/YYYY'), 'fmD') Daynum, to_char(to_date(mydate, 'fmDD/MM/YYYY'), 'DY') Day from (
                    SELECT
                    my_function(date_rec) as mydate, // changed here to call function?
                    ...
                    ...
                    FROM
                    tableA a LEFT JOIN tableB b
                    On.. etc
                    • 7. Re: Using case with other tables
                      Frank Kulash
                      Hi,
                      pearlyred wrote:
                      Thanks Etbin.

                      I guess the function like that might not be the most efficient, but it at least helps to reduce complexity somewhat, for now.
                      A function will probably be slower than computing the answer in pure SQL, but a function will definitely make the code simpler. You'll have to decide whether the better performance (if any) is worth the messier code. I would start by assuming it is not; that is, write the function, test it, and see if the speed is acceptable.
                      I will play around with the function to get it how I need it.

                      I assume I can then alter my original query to something along the lines of :
                      SELECT mydate Date_REC, SUM(d_num) intake, to_char(to_date(mydate, 'fmDD/MM/YYYY'), 'fmD') Daynum, to_char(to_date(mydate, 'fmDD/MM/YYYY'), 'DY') Day from (
                      SELECT
                      my_function(date_rec) as mydate, // changed here to call function?
                      ...
                      ...
                      FROM
                      tableA a LEFT JOIN tableB b
                      On.. etc
                      Yes, you can call it like that.
                      Make your function return a DATE; then you won't have to call TO_DATE in the main query.
                      • 8. Re: Using case with other tables
                        985940
                        This works really well for showing the status of the supplied date, but how would I adapt it to return the next working day?
                        create or replace
                        function calendar(
                                          p_date in date
                                         )
                          return varchar2
                          is
                              v_retval varchar2(7);
                          begin
                              select  case count(*)
                                        when 1 then 'HOLIDAY'
                                        else case
                                               when p_date - trunc(p_date,'iw') >= 5 then 'WEEKEND'
                                               else 'WEEKDAY'
                                             end
                                      end
                                into  v_retval
                                from  holidays_table
                                where dt = trunc(p_date);
                              return v_retval;
                        end;
                        Edited by: pearlyred on 20/01/2013 14:18 - Code added
                        • 9. Re: Using case with other tables
                          985940
                          The first function and the first part of the second seem to be working ok, but if I use a weekend date, the second function falls over, no error message back, just unresponsive in my browser (using APEX SQL commands).
                          while c > 0
                            loop
                              d := d + sign(p_count);
                              if to_char(d,'DY') not in ('SAT','SUN') and is_holiday(d) is null then
                                c := c - 1;
                              end if;
                            end loop;
                           
                            return d;
                          I understand what it's doing, but it seems like the loop is getting stuck or something?

                          I'm testing it with :
                           select working_day(to_date('20/01/2013', 'DD/MM/YYYY')) from dual;
                          • 10. Re: Using case with other tables
                            Solomon Yakobson
                            What you are trying to do is best done with calendar table which has all dates for the year and an indicator column, e.g. H for holiday, D for workday, W for weekend. Then you don't need a function:
                            select  min(dt)
                              from  calendar
                              where cal_date >= date_in_question
                                and indicator = 'D'
                            /
                            SY.
                            • 11. Re: Using case with other tables
                              985940
                              That's a lot of maintenance though, to keep loading full years worth of dates for each year. I am trying to make it so that there is as little maintenance work for the users to do as possible, ie. just loading 12 dates for public holidays isn't too bad.
                              • 12. Re: Using case with other tables
                                Solomon Yakobson
                                pearlyred wrote:
                                That's a lot of maintenance though
                                Really? The statement below prefills calendar table till year 2100 with weekday/weekend indicator in two tenth of a second:
                                create table calendar
                                  as
                                    select  date '2012-12-31' + level cal_date,
                                            case date '2012-12-31' + level - trunc(date '2012-12-31' + level,'iw')
                                              when 6 then 'W'
                                              when 5 then 'W'
                                              else 'D'
                                           end indicator
                                      from  dual
                                      connect by date '2012-12-31' + level < date '2100-01-01'
                                /
                                
                                Table created.
                                
                                Elapsed: 00:00:00.20
                                SQL>
                                All you need now is update it with holidays which is same maintenance as your holiday table.

                                SY.
                                • 13. Re: Using case with other tables
                                  Solomon Yakobson
                                  Solomon Yakobson wrote:

                                  All you need now is update it with holidays which is same maintenance as your holiday table.
                                  And if you already have holiday table of (dt,description) you can:
                                  create table calendar
                                    as
                                      select  date '2012-12-31' + level cal_date,
                                              case date '2012-12-31' + level - trunc(date '2012-12-31' + level,'iw')
                                                when 6 then 'W'
                                                when 5 then 'W'
                                                else 'D'
                                             end indicator,
                                              case date '2012-12-31' + level - trunc(date '2012-12-31' + level,'iw')
                                                when 6 then 'Weekend: Sunday'
                                                when 5 then 'Weekend: Saturday'
                                                else 'Weekday: Monday - Friday'
                                             end description
                                        from  dual
                                        connect by date '2012-12-31' + level < date '2100-01-01'
                                  / 
                                  merge
                                    into calendar c
                                    using holiday h
                                    on (
                                        c.cal_date = h.dt
                                       )
                                    when matched
                                      then update
                                              set c.indicator = 'H',
                                                  c.description = h.description
                                  /
                                  SY.
                                  • 14. Re: Using case with other tables
                                    Etbin
                                    This works for me
                                    function working_day(p_date in date,p_count in number default null) return date is
                                      c number := nvl(abs(p_count),1);
                                      d date := p_date;
                                    begin
                                      if nvl(p_count,0) = 0 then
                                        if to_char(p_date,'DY') not in ('SAT','SUN') and is_holiday(p_date) is null then
                                          return p_date;
                                        else
                                          return null;
                                        end if;
                                      end if;
                                     
                                      while c > 0
                                      loop
                                        d := d + sign(p_count);
                                        if to_char(d,'DY') not in ('SAT','SUN') and is_holiday(d) is null then
                                          c := c - 1;
                                        end if;
                                      end loop;
                                     
                                      return d;
                                    end;
                                    Regards

                                    Etbin