RE: Count days without week-end

Posted in 2000

Demus, Alan — Thu, 28 Sep 2000 11:19:44 -0500 — source: Informix-list mailing list archive (1991-1998)

I may be rushing to judgment, but this seems to work: create procedure "demusa".noweekends(d1 date, d2 date) returning integer; define w1, w2 integer; define dt date; define wt integer; on exception return -1; end exception; if d1 > d2 then let dt = d1; let d1 = d2; let d2 = dt; end if let w1 = weekday(d1); let w2 = weekday(d2); let d1 = d1 - w1; let d2 = d2 - w2; let wt = (d2 - d1) / 7 * 5; if w2 = 6 then let w2 = 5; end if if w1 = 0 then let w1 = 1; end if let wt = wt - w1 + w2 + 1; return wt; end procedure; > -----Original Message----- > From: Sujit.Pal@bankofamerica.com [SMTP:Sujit.Pal@bankofamerica.com] > Sent: Thursday, September 28, 2000 11:11 AM > To: philippe juillot > Cc: informix-list@iiug.org > Subject: Re: Count days without week-end > > > > Phillippe > > Approach 1 - write or procure a datablade. Involves either programming > effort or > big bucks or both. > > Approach 2 - populate a table (called weekends, say) at the beginning of > the > year with all weekend dates, and then run the following... > SELECT '2000.09.26' - '2000.09.22' + 1 - > (SELECT COUNT(*) FROM weekends > WHERE weekend_dates BETWEEN > '2000.09.22' AND '2000.09.26') > FROM systables > WHERE tabid = 1; > Cleaner approaches based on the above may be possible - I am merely > extrapolating from something concerning mandatory holidays I had to do in > the > past. > > HTH > Sujit > > > > > > > philippe juillot <philippe.juillot@siemens.at> on 09/28/2000 04:32:55 AM > > Please respond to philippe juillot <philippe.juillot@siemens.at> > > To: informix-list@iiug.org > cc: > Subject: Count days without week-end > > > > > --------------C1983803407A6C285B7EE2C9 > Content-Type: text/plain; charset=us-ascii > Content-Transfer-Encoding: 7bit > > Hallo, > > We want to count the days between 2 dates in 2 ways: > > * all the days: > select '2000.09.26' - '2000.09.22' + 1 from systables where tabid = > 1; > Result ist 5 days ... no problem! > > * the days without the week-end: > Result must be 3 (Friday, Monday, Tuesday) > How can I do this? The interval between the 2 dates can be more > than 1 year. > > Many thanks for good ideas > Philippe Juillot > > --------------C1983803407A6C285B7EE2C9 > Content-Type: text/html; charset=us-ascii > Content-Transfer-Encoding: 7bit > > <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> > <HTML> > Hallo, > We want to count the days between 2 dates in 2 ways: > <UL> > <LI> > all the days: > select '2000.09.26' - '2000.09.22' + 1 from systables where tabid = 1; > Result ist 5 days ... no problem! > </LI> > > <LI> > the days without the week-end: > Result must be 3 (Friday, Monday, Tuesday) > How can I do this? The interval between the 2 dates can be more than 1 > year.</LI> > </UL> > Many thanks for good ideas > Philippe Juillot</HTML> > > --------------C1983803407A6C285B7EE2C9-- > > > > > >