Thread: Business days
Hello... I need to get the a total number of business days (from monday to friday) between two dates. Someone can help me please.
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> Voice: 503-667-4517 Fax: 503-667-8863
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
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