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.
i0zX+n{+i^
Received on Mon Aug 04 2008 - 04:26:09 CDT

Original text of this message