RE: Vs: Function to calculate weekdays left in a month

From: Ian Cary <ian.cary_at_ons.gsi.gov.uk>
Date: Mon, 4 Aug 2008 10:26:09 +0100
Message-ID: <OF90BD7A9C.ABC23A6F-ON8025749B.0033136D-8025749B.0033D56F@ons.gsi.gov.uk>

I think Marks solution is more elegant but an alternative would be;

IANS> var dval varchar2(9)
IANS> exec :dval := to_char(sysdate,'dd-mon-yy');

  select count(*)
  from
  (
  select tdate
  from (select to_date('01-jan-01','dd-mon-yy') tdate from dual)   model
  return updated rows
  dimension by (tdate)
  measures (1 as dummy)
  rules (dummy[for tdate from to_date(:dval,'dd-mon-yy')

                          to last_day(to_date(:dval,'dd-mon-yy'))
             increment numtodsinterval(1,'day')] =  1 )

  )
  where to_char(tdate,'dy') not in ('sat','sun');

        COUNT(*)

---

              20

For some reason trying to use sysdate as a bound to the for condition raises an ORA-32626 error but its easy enough to use a biind variable.

Cheers,

Ian

|---------+----------------------------->


|         |           Mark.Bobak_at_proques|

|         |           t.com             |

|         |           Sent by:          |

|         |           oracle-l-bounce_at_fr|

|         |           eelists.org       |

|         |                             |

|         |                             |

|         |           04/08/2008 09:07  |

|         |           Please respond to |

|         |           Mark.Bobak        |

|         |                             |

|---------+----------------------------->
  >--------------------------------------------------------------------------------------------------------------|
  |                                                                                                              |
  |       To:       jack_at_vanzanen.com, teijo.lallukka_at_edita.fi                                                   |
  |       cc:       oracle-l_at_freelists.org                                                                       |
  |       Subject:  RE: Vs: Function to calculate weekdays left in a month                                       |
  >--------------------------------------------------------------------------------------------------------------|

Try this:
select sum((case trim(to_char(sysdate+level-1,'Day'))

                 when 'Saturday'
                      then 0
                 when 'Sunday'
                      then 0
                      else 1 end)) weekdays_left_this_month

  from dual
connect by level <= trunc(last_day(sysdate))-trunc(sysdate)+1

As written, it includes the current day in the count.

Hope that helps,

-Mark

---

From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] On Behalf Of Jack van Zanen [jack_at_vanzanen.com] Sent: Monday, August 04, 2008 1:54 AM
To: Teijo Lallukka
Cc: oracle-l_at_freelists.org
Subject: Re: Vs: Function to calculate weekdays left in a month

If only it were that simple.

That gets me all the days and I am interested in the weekdays only.

Brgds

Jack

On 04/08/2008, Teijo Lallukka <teijo.lallukka_at_edita.fi< mailto:teijo.lallukka_at_edita.fi>> wrote:
Hi!

try this, this can help you.

SQL> SELECT SYSDATE,
LAST_DAY(SYSDATE) "Last",
LAST_DAY(SYSDATE) - SYSDATE "Days Left"
FROM DUAL;

       SYSDATE Last    Days Left
1       4.8.2008 8:48:16        31.8.2008 8:48:16       27

-TL

---

Teijo Lallukka, Oracle DBA
teijo.lallukka_at_edita.fi<mailto:teijo.lallukka_at_edita.fi> http://www.edita.fi

---

www.edilex.fi<http://www.edilex.fi> | www.finlex.fi<http://www.finlex.fi> | www.credita.fi<http://www.credita.fi>

---

>>> "Jack van Zanen" <jack_at_vanzanen.com<mailto:jack_at_vanzanen.com>> 4.8.2008 8:45:39 >>>
Hi All,

I am not a programmer and before I spend a lot of time on some PL/SQL to do this I would just like to double check if anybody on this list has created such a function/proc already.

Brgds

--
J.A. van Zanen




--
J.A. van Zanen
--
http://www.freelists.org/webpage/oracle-l



This email was received from the INTERNET and scanned by the Government
Secure Intranet anti-virus service supplied by Cable&Wireless in
partnership with MessageLabs. (CCTM Certificate Number 2007/11/0032.) In
case of problems, please call your organisation’s IT Helpdesk.
Communications via the GSi may be automatically logged, monitored and/or
recorded for legal purposes.


For the latest data on the economy and society consult National Statistics at http://www.statistics.gov.uk

*********************************************************************************


Please Note:  Incoming and outgoing email messages are routinely monitored for compliance with our policy on the use of electronic communications
*********************************************************************************


Legal Disclaimer  :  Any views expressed by the sender of this message are not necessarily those of the Office for National Statistics
*********************************************************************************


The original of this email was scanned for viruses by the Government Secure Intranet virus scanning service supplied by Cable&Wireless in partnership with MessageLabs. (CCTM Certificate Number 2007/11/0032.) On leaving the GSi this email was certified virus free.
Communications via the GSi may be automatically logged, monitored and/or recorded for legal purposes.
†Ûiÿü0ÁúÞzX¬¶Ê+ƒün– {ú+iÉ^

Received on Mon Aug 04 2008 - 04:26:09 CDT