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

Or, similarly, using the 'D' option to give the day number in the week (1=Monday...7=Sunday):

select count(1) NUM_WEEKDAYS
  from DUAL
 where TO_CHAR(sysdate + level - 1, 'D') in ('1', '2', '3', '4', '5') connect by level <= (LAST_DAY(sysdate) - sysdate) + 1

On Mon, Aug 4, 2008 at 10:26 AM, Ian Cary <ian.cary_at_ons.gsi.gov.uk> wrote:

> 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.
>

--
http://www.freelists.org/webpage/oracle-l