Re: Help SQL Query

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.
==========================================+===================================