Number of working days.

[kumariaru]

Hi All,
I have to write a query for number of working days in a month.Is it possible to write a SQL with this condition.

Thank you

 

[SantaMufasa]

I have to write a query for number of working days in a month.

I have a few follow-on questions:

1) Do you want a query (e.g. SELECT working_days FROM <some table>...), or do you really mean you want a function to tell you how many working days there are between two dates (e.g., SELECT working_days(<beg date>,<end date>) from DUAL

2) When you say "working days" do you mean how many non-Saturday and non-Sunday days are there or do you want to also take into account company holidays that fall on M-F? (Example: Chrismas, New Year, et cetera) (This is a more complicated feature, BTW.)

Let us know,

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[SantaMufasa]

Also, if you opt for a function, if you invoked the function in this fashion:

...working_days(<today>,<tomorrow>)...

...and presuming that <today> and <tomorrow> are Tuesday and Wednesday, should the function return "1" or "2" days. Another way of looking at the problem:

...working_days(sysdate,sysdate)...

Presuming that sysdate is a work day, should the function return "0" or "1"?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[kumariaru]

Hi Mufasa,
I want to do the first option.To write a sql query and also with the second point considering Saturadays and sundays as non working days.

1) Do you want a query (e.g. SELECT working_days FROM <some table>...)

Right we are not really considering the M-F (holidays) as non working day.Just to select working days mean M-F...

Thank you Mufasa

 

[feherke]

Hi

[b]create or replace function[/b] workdaysbetween(p_begin [b]in date[/b],p_end [b]in date[/b]) [b]return number is[/b]
  res [b]number[/b];
  cou [b]date[/b];
[b]begin[/b]
  cou:=p_begin;
  res:=0;
  [b]while[/b] cou<p_end [b]loop[/b]
    cou:=cou+1;
    [b]if[/b] to_char(cou,[i]'d'[/i]) [b]not in[/b] (1,7) [b]then[/b]
      res:=res+1;
    [b]end if[/b];
  [b]end loop[/b];
  [b]return[/b] (res);
[b]end[/b];

Feherke.

http://rootshell.be/~feherke/

 

[feherke]

Hi

Sorry, "incrementing" the counter should be after the [tt]if[/tt].

[b]create or replace function[/b] workdaysbetween(p_begin [b]in date[/b],p_end [b]in date[/b]) [b]return number is[/b]
  res [b]number[/b];
  cou [b]date[/b];
[b]begin[/b]
  cou:=p_begin;
  res:=0;
  [b]while[/b] cou<=p_end [b]loop[/b]
    [b]if[/b] to_char(cou,[i]'d'[/i]) [b]not in[/b] (1,7) [b]then[/b]
      res:=res+1;
    [b]end if[/b];
    cou:=cou+1;
  [b]end loop[/b];
  [b]return[/b] (res);
[b]end[/b];

Feherke.

http://rootshell.be/~feherke/

 

[kumariaru]

Hi Feherke,
I will try this and will keep update ...

Thank you..

 

[SantaMufasa]

Kumariaru, to be perfectly clear:

...working_days(sysdate,sysdate)...

Presuming that sysdate is a work day, should the function return "0" or "1"?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]