Business days

Eddy D. Sanchez wrote:

Hello...
I need to get the a total number of business days (from monday to
friday) between two dates.
Someone can help me please.

Whether you're using PHP or not, this page may be of some help:

http://www.php.net/manual/en/function.date.php

There are a couple of examples there of how to do it. Maybe something
there can be of some use.

b

On Wed, 25 Apr 2007, Eddy D. Sanchez wrote:

I need to get the a total number of business days (from monday to friday)
between two dates. Someone can help me please.

Joe Celko's "SQL for Smarties, 2nd Edition" has exactly this solution.
Check it out!

Rich

--
Richard B. Shepard, Ph.D. | The Environmental Permitting
Applied Ecosystem Services, Inc. | Accelerator(TM)
<http://www.appl-ecosys.com&gt; Voice: 503-667-4517 Fax: 503-667-8863

am Wed, dem 25.04.2007, um 21:01:13 -0400 mailte Eddy D. Sanchez folgendes:

Hello...
I need to get the a total number of business days (from monday to
friday) between two dates.
Someone can help me please.

For instance the number of business days between 2007-04-01 and
2007-04-30:

select sum(case when extract (dow from foo) in(1,2,3,4,5) then 1 else 0 end)
from (select ('2007-04-01'::date + (generate_series(0,'2007-04-30'::date - '2007-04-01'::date)||'days')::interval) as foo) foo;

Unregardedly Easter!

Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net

-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160

- -- I need to get the a total number of business days (from monday to
- -- friday) between two dates.
- -- Someone can help me please.

A simplistic approach that counts a "business day" as being Monday
through Friday would be something like this:

CREATE OR REPLACE FUNCTION bizdays(date,date)
RETURNS BIGINT
LANGUAGE SQL AS
$_$
SELECT count(*) FROM
(SELECT extract('dow' FROM $1+x) AS dow
FROM generate_series(0,$2-$1) x) AS foo
WHERE dow BETWEEN 1 AND 5;
$_$;

CREATE OR REPLACE FUNCTION bizdays(text,text)
RETURNS BIGINT LANGUAGE SQL AS
$_$
SELECT bizdays($1::date,$2::date);
$_$;

SELECT bizdays('20070401','20070407');

However, you quickly run into the problem of holidays. While you
could construct a helper table listing all the holidays, ones that
don't fall on the same day every year (e.g. Easter) will trip
you up. A possible solution is to write a plperlu function that
makes a call to Date::Manip, which can tell you the number of
business days between two date while excluding holidays, and which
allows you to specify exactly which days are considered a holiday.

- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200704261426
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iD8DBQFGMO9ivJuQZxSWSsgRA8HXAKDSY7vylo/EqQ+fjjwvlrJDdg/S2QCfcaM9
OKi3YW1IWOAc0Nfi9xBjuTc=
=aIqg
-----END PGP SIGNATURE-----

- -- I need to get the a total number of business days (from monday to
- -- friday) between two dates.
- -- Someone can help me please.

A simplistic approach that counts a "business day" as being Monday
through Friday would be something like this:

However, you quickly run into the problem of holidays. While you
could construct a helper table listing all the holidays, ones that
don't fall on the same day every year (e.g. Easter) will trip
you up.

Er, isn't Easter usually on a Sunday? Anyway, I also found this, the
first hit if you google "sql holidays":

http://www.sqlmag.com/Article/ArticleID/25899/sql_server_25899.html

The big ugly union might need to be munged a bit, but most of the non-
weekend US holidays seem to be there.

- John D. Burger
MITRE

-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160

However, you quickly run into the problem of holidays. While you
could construct a helper table listing all the holidays, ones that
don't fall on the same day every year (e.g. Easter) will trip
you up.

Er, isn't Easter usually on a Sunday?

I meant the same numerical date, e.g. Christmas is always December 25th,
and so is a little easier programatically than the rules for Easter. If
you meant that Sunday is never a business day, then yes, it was a bad
example. :)

Anyway, I also found this, the first hit if you google "sql holidays":
http://www.sqlmag.com/Article/ArticleID/25899/sql_server_25899.html

The big ugly union might need to be munged a bit, but most of the non-
weekend US holidays seem to be there.

Sure, that's an alternative, but it seems a bit too much reinventing an
already existing wheel. I was amused to see the script had the ill-fated
Lee-Jackson-King day in it. Ideally, we'd want a Postgres table that
describes the rules for each holiday, and then a function that reads it
on the fly. Perhaps a project for another day...

- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200704261706
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iD4DBQFGMRUnvJuQZxSWSsgRAwn3AJ9o1CCb2x3Asn1U70xyphetZ6a2XgCY5fuG
coAVQiUyFWqKyJWCpJBanA==
=gmZi
-----END PGP SIGNATURE-----

On 26/04/07, Greg Sabino Mullane <greg@turnstep.com> wrote:

-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160

However, you quickly run into the problem of holidays. While you
could construct a helper table listing all the holidays, ones that
don't fall on the same day every year (e.g. Easter) will trip
you up.

Er, isn't Easter usually on a Sunday?

I meant the same numerical date, e.g. Christmas is always December 25th,
and so is a little easier programatically than the rules for Easter. If
you meant that Sunday is never a business day, then yes, it was a bad
example. :)

Anyway, I also found this, the first hit if you google "sql holidays":
http://www.sqlmag.com/Article/ArticleID/25899/sql_server_25899.html

The big ugly union might need to be munged a bit, but most of the non-
weekend US holidays seem to be there.

Sure, that's an alternative, but it seems a bit too much reinventing an
already existing wheel. I was amused to see the script had the ill-fated
Lee-Jackson-King day in it. Ideally, we'd want a Postgres table that
describes the rules for each holiday, and then a function that reads it
on the fly. Perhaps a project for another day...

More complicated than that....

Easter read Good Friday and Easter Monday.

Christmas Eve (does it count or not)

Christmas Day, Boxing Day if it falls on a Weekend, Bank holidays are
applied in loo on the following monday and tuesday as necessary.

There are some quite good list available but you will have to work out
what your local logic actually is.

Peter.

am Fri, dem 27.04.2007, um 8:18:55 +0100 mailte Peter Childs folgendes:

Sure, that's an alternative, but it seems a bit too much reinventing an
already existing wheel. I was amused to see the script had the ill-fated
Lee-Jackson-King day in it. Ideally, we'd want a Postgres table that
describes the rules for each holiday, and then a function that reads it
on the fly. Perhaps a project for another day...

More complicated than that....

Easter read Good Friday and Easter Monday.

Some times ago, i have written a little function to calculate easter and
other feasts.

http://a-kretschmer.de/diverses.shtml

HTH, Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net