This discussion is archived
14 Replies Latest reply: Dec 28, 2012 4:01 AM by Igor S. RSS

A better way to write last_day query

Igor S. Newbie
Currently Being Moderated
Hi folks,

I am looking for a better way to write a query to find last_day in month but if its sunday or holiday it should move to day before last day.

So for example if 31 is sunday it should go for 30, if 30 is holiday it should move down to 29.

I got this so far but the connect by level is hardcoded to 15. Want to see if there is a better way to get this working:
select max(datum)
  from (    select last_day(trunc(sysdate)) - level + 1 as datum
              from dual
        connect by level < 15)
 where to_char(datum, 'day') != 'sunday'     
   and to_char(datum, 'DDMM') not in
         ('3012')
Best regards,
Igor
  • 1. Re: A better way to write last_day query
    Karthick_Arp Guru
    Currently Being Moderated
    If 30 is holiday it should move down to 29.
    How do you find if 30th is a holiday? Do you have a table defining this? If yes please share the details.
  • 2. Re: A better way to write last_day query
    BluShadow Guru Moderator
    Currently Being Moderated
    Where is your information about what is a 'holiday'?
  • 3. Re: A better way to write last_day query
    AlbertoFaenza Expert
    Currently Being Moderated
    Hi Igor,

    this condition:
    where to_char(datum, 'day') != 'sunday' 
    will not work because the TO_CHAR function return Sunday right padded with blank.

    You should modify it in this way:
    where TRIM(to_char(datum, 'day')) != 'sunday' 
    For what is regarding holidays see what have been asked by Kathick and BluShadow

    Regards.
    Al
  • 4. Re: A better way to write last_day query
    Igor S. Newbie
    Currently Being Moderated
    to_char(datum, 'DDMM') not in
             ('3012')
    Instead of '3012' there is a function that returns 1 holiday in DDMM format. So for now lets just say there is 1 possible holiday in a year.
  • 5. Re: A better way to write last_day query
    Igor S. Newbie
    Currently Being Moderated
    will not work because the TO_CHAR function return Sunday right padded with blank.

    Are you sure about this? I tried it several times and its not right padding anything. Why would it right pad?
  • 6. Re: A better way to write last_day query
    Karthick_Arp Guru
    Currently Being Moderated
    Like this
    select to_char(last_day_month, 'Day') day_is, 
           last_day_month, 
           last_day_month - case when to_char(last_day_month, 'fmday') = 'sunday' then 1
                                 when to_char(last_day_month, 'ddmm' ) = '3012'   then 1
                                 else 0 
                            end last_business_day_month
      from (
              select last_day(add_months(trunc(sysdate, 'year'), level-1)) last_day_month
                from dual
              connect by level <= 12
           )
    /
    DAY_IS    LAST_DAY_MONTH LAST_BUSINESS_DAY_MONTH
    --------- -------------- -----------------------
    Tuesday   31-JAN-12      31-JAN-12               
    Wednesday 29-FEB-12      29-FEB-12               
    Saturday  31-MAR-12      31-MAR-12               
    Monday    30-APR-12      30-APR-12               
    Thursday  31-MAY-12      31-MAY-12               
    Saturday  30-JUN-12      30-JUN-12               
    Tuesday   31-JUL-12      31-JUL-12               
    Friday    31-AUG-12      31-AUG-12               
    Sunday    30-SEP-12      29-SEP-12               
    Wednesday 31-OCT-12      31-OCT-12               
    Friday    30-NOV-12      30-NOV-12               
    Monday    31-DEC-12      31-DEC-12  
  • 7. Re: A better way to write last_day query
    Karthick_Arp Guru
    Currently Being Moderated
    Igor S. wrote:
    will not work because the TO_CHAR function return Sunday right padded with blank.

    Are you sure about this? I tried it several times and its not right padding anything. Why would it right pad?
    Yes it will. You need to use format modifier "fm" See my post above
  • 8. Re: A better way to write last_day query
    BluShadow Guru Moderator
    Currently Being Moderated
    Igor S. wrote:
    will not work because the TO_CHAR function return Sunday right padded with blank.

    Are you sure about this? I tried it several times and its not right padding anything. Why would it right pad?
    Yes. Test it...
    SQL> ed
    Wrote file afiedt.buf
    
      1  select to_char(sysdate,'Day') as today
      2        ,length(to_char(sysdate,'Day')) as len_today
      3        ,length(to_char(sysdate,'fmDay')) as len_fmtoday
      4* from dual
    SQL> /
    
    TODAY      LEN_TODAY LEN_FMTODAY
    --------- ---------- -----------
    Friday             9           6
    Using the format modifier, will prevent padding of the day names.
  • 9. Re: A better way to write last_day query
    Igor S. Newbie
    Currently Being Moderated
    I had no idea to_char with 'day' does this. Thank you for this info. How ever this solution doesnt work completly:
    select to_char(last_day_month, 'Day') day_is, 
           last_day_month, 
           last_day_month - case when to_char(last_day_month, 'fmday') = 'sunday' then 1
                                 when to_char(last_day_month, 'ddmm' ) = '2909'   then 1
                                 else 0 
                            end last_business_day_month
      from (
              select last_day(add_months(trunc(sysdate, 'year'), level-1)) last_day_month
                from dual
              connect by level <= 12
           )
    If you look at:
    30.09.2012 - 29.09.2012. The query should say next buisness day is 28.09 in case the holiday is on 29.09

    Best regards,
    Igor
  • 10. Re: A better way to write last_day query
    Suman Rana Explorer
    Currently Being Moderated
    Hi Karthick

    what if 29-SEP-12 is holiday? here 30-SEP-12 is Sunday.. or 30-APR-12 is holiday.. here 29-APR is Sunday.. how this query will handle?
  • 11. Re: A better way to write last_day query
    BluShadow Guru Moderator
    Currently Being Moderated
    This is what I came up with...
    SQL> ed
    Wrote file afiedt.buf
    
      1  with holidays as (select date '2012-12-31' hd from dual)
      2      ,dys      as (select (last_day(trunc(sysdate))+1)-rownum as dy
      3                    from   dual
      4                    connect by (last_day(sysdate)+1)-rownum >= trunc(sysdate,'MM')
      5                   )
      6  select max(dys.dy)
      7  from   dys left outer join holidays on (dys.dy = holidays.hd)
      8  where  holidays.hd is null
      9* and    to_char(dy,'fmDay') != 'Sunday'
    SQL> /
    
    MAX(DYS.DY)
    --------------------
    29-DEC-2012 00:00:00
    which is based on the last day for the current month ignoring Sundays and those dates listed in the Holidays table.

    Essentially, the priniciple is to build up a calender, and outer join that to the holidays, so you can then get the maximum date for the month you're interested in excluding the sundays and those that are marked as a holiday.
  • 12. Re: A better way to write last_day query
    BluShadow Guru Moderator
    Currently Being Moderated
    Suman Rana wrote:
    Hi Karthick

    what if 29-SEP-12 is holiday? here 30-SEP-12 is Sunday.. or 30-APR-12 is holiday.. here 29-APR is Sunday.. how this query will handle?
    Ok, more generic, where you can put in your required date...
    SQL> ed
    Wrote file afiedt.buf
    
      1  with req_date as (select date '2012-09-09' as dt from dual)
      2      ,holidays as (select date '2012-09-29' hd from dual)
      3      ,dys      as (select (last_day(trunc(dt))+1)-rownum as dy
      4                    from   req_date
      5                    connect by (last_day(dt)+1)-rownum >= trunc(dt,'MM')
      6                   )
      7  select max(dys.dy)
      8  from   dys left outer join holidays on (dys.dy = holidays.hd)
      9  where  holidays.hd is null
     10* and    to_char(dy,'fmDay') != 'Sunday'
    SQL> /
    
    MAX(DYS.DY)
    --------------------
    28-SEP-2012 00:00:00
    So, 30th Sep is ignored as it's a sunday and 29th Sep is ignored as it's listed as a holiday.
  • 13. Re: A better way to write last_day query
    BluShadow Guru Moderator
    Currently Being Moderated
    And likewise with your April example...
    SQL> ed
    Wrote file afiedt.buf
    
      1  with req_date as (select date '2012-04-03' as dt from dual)
      2      ,holidays as (select date '2012-04-30' hd from dual)
      3      ,dys      as (select (last_day(trunc(dt))+1)-rownum as dy
      4                    from   req_date
      5                    connect by (last_day(dt)+1)-rownum >= trunc(dt,'MM')
      6                   )
      7  select max(dys.dy)
      8  from   dys left outer join holidays on (dys.dy = holidays.hd)
      9  where  holidays.hd is null
     10* and    to_char(dy,'fmDay') != 'Sunday'
    SQL> /
    
    MAX(DYS.DY)
    --------------------
    28-APR-2012 00:00:00
  • 14. Re: A better way to write last_day query
    Igor S. Newbie
    Currently Being Moderated
    Yeah this is what I was looking for:
    connect by (last_day(dt)+1)-rownum >= trunc(dt,'MM')
    Wanted to remove the hardcoded 15 out of the query.

    Thank you all for helping. Thanxs Blu for the answer :)

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points