Re: Number of days between two dates

Here is a solution I saved a long time ago, which I have had just cause to use in thr last week. It will give you the number of working days between two dates - you'll need to cater for holidays by some other means.

It's a clever solution with a full detailed explanation of how it works. I have included the full article to give due credit to the author.

-- 
+--------------------------+-------------------------+-----------------------+
| Ian Bainbridge           | bainbridge.i_at_bgers.co.uk|Phone: (44)191-216-0202|
| British Gas ERS          | Newcastle Upon Tyne, UK |            x2790      |
+--------------------------+-------------------------+-----------------------+Article 210 of comp.databases.oracle:
Newsgroups: comp.databases.oracle
Path: bgers.co.uk!uknet!pipex!uunet!noc.near.net!transfer.stratus.com!armado.oberon.com!armado!mwick
From: mwick_at_oberon.com (Mark Wick)
Subject: Re: Help SQL Query
In-Reply-To: raz_at_garfield.freac.fsu.edu's message of Tue, 24 Aug 1993 13:20:38 GMT
Message-ID: <MWICK.93Aug24123632_at_tamora.oberon.com>
Sender: news_at_oberon.com
Nntp-Posting-Host: tamora
Organization: Oberon Software Inc; 1 Cambridge Center; Cambridge, MA 01242
References: <CC9MEE.67s_at_mailer.cc.fsu.edu>
Date: Tue, 24 Aug 1993 17:36:32 GMT
Lines: 101

In article <CC9MEE.67s_at_mailer.cc.fsu.edu> raz_at_garfield.freac.fsu.edu () writes:


	   Here's a simplified version of my problem.  I have a table with
   three columns.   It looks like this:

	   SS		(Social Security #)
	   BEGINNING_DATE	(Date appointment begins)
	   END_DATE	(Date appointment ends)

   Basically this table keeps track of employees' appointment records.
   I need to calculate the number of days that the appointment last.
   I know it is possible find that by subtracting the dates such as this:

	   END_DATE - BEGINING_DATE


I answered this one a while back - I'll repost that answer:



> Well, you piqued my curiousity, so I had to give this a shot.  Hell, it

> actually works and is very fast:

> 

> assume you want to know from 2-FEB-92 to 12-DEC-93.

> 

> SELECT

> days - (2 * TRUNC (days/7)) -

>  DECODE (MOD(days,7), 0, 0,

>   DECODE (first_day, 1, 1,

>    DECODE (SIGN(MOD(days,7) + first_day - 8), -1, 0, 0, 1, 1, 2)))

> 

> FROM dual;

> 

> 

> in the above, replace 

> 

> days with: (TO_DATE ('12-DEC-93') - TO_DATE ('02-FEB-92') + 1)

> 

> The +1 is so you include the starting date as well.

> 

> first_day with:

>   TO_CHAR (TO_DATE ('02-FEB-92'), 'D')

> 

> 

> The premise behind this is simple.  To determine the number of particular

> days between 2 dates, you first find the number of weeks.  Obviously, you

> will have 5 days per week.  Then, you only need to disambiguate the MOD

> remainder of those two dates.  The approach I took was to take all of the

> days and subtract off weekends, since there is only 2 days.  If you want

> more detail, I have it, just ask.  The key to adding extra days is based on

> the first date, and is a non-continuous function:

> 

> if first_date is a 1 (Sunday), then add 1 day,

> otherwise

> 

> if mod_remainder + first_date - 8  is  < 0   add 0 days

>                                    is  = 0   add 1 day

>                                    is  > 0   add 2 days

> 

> 


For you:

SELECT SS,
days - (2 * TRUNC (days/7)) -
 DECODE (MOD(days,7), 0, 0,
  DECODE (first_day, 1, 1,
   DECODE (SIGN(MOD(days,7) + first_day - 8), -1, 0, 0, 1, 1, 2))) NUM_DAYS

FROM YOUR_TABLE;


in the above, replace 

days with: (TO_DATE (END_DATE) - TO_DATE (BEGINNING_DATE) + 1)

The +1 is so you include the starting date as well.

first_day with:
  TO_CHAR (TO_DATE (BEGINNING_DATE), 'D')
 

Have fun,


Mark


==========================================+===================================
Mark Wick                                 | VOICE:           617-494-0990
Software Engineer                         |                  617-494-5449 x171
Oberon Software, Inc.                     | FAX:             617-494-0414
One Cambridge Center, Cambridge, MA 02142 | INTERNET:        mwick_at_oberon.com
------------------------------------------+-----------------------------------
			          Cherish yesterday.
                                   Dream tomorrow.
                                     Live today.
==========================================+===================================