Re: Oracle calendar teaser...

Great job Dan, I think I get the gist of it. When I get it right (IF!!) I will post the SQL here for others to see as well.

Martin

DanHW wrote:

> >I have a little teaser for y'all. I want to write an SQL script (not
> >PL/SQL) that will tell me how many weekdays (Mon-Fri) there are between
> >2 dates. Can this be done and if so how?
> >
> >Thanks in advance.
> >
> >Martin
> >
> >
> >
>
> Perhaps someone will post a tested SQL command to do it; I do not have SQL*PLUS
> at home, so I can't write an test this. However, I can describe one way;
> hopefully it will be close enough that you can fix it if it is not correct.
>
> You can easily subtract the dates to get the number of days
>
> select d2-d1 from dual;
>
> Divide by 7 to get the number of weeks, then truncate to get the number of FULL
> weeks
>
> select int((d2-d1)/7) from dual
>
> Multiply by 5 to get workdays in each week
>
> select 5 *int((d2-d1)/7) from dual
>
> Now the tricky part - you need to get the fractional part of the week (this
> will add between 0 and 6 days). There is a date format code that returns the
> day of the week a day falls on, I think it is 'W'. Likewise, I don;t remember
> Oracle's convention, but suppose it is Sunday=0, Monday is 1,.. Saturday =6.
>
> If d1 falls on Sunday, and d2 falls on Monday (ie d1 is before d2 in the week),
> we will add the number of days between, which is d2[Monday]-d1[Sunday] = 1 - 0
> = 1
>
> If d1 falls on Tuesday, and d2 falls on Monday (id d1 is later in the week than
> d2), we need to add from the last Tuesday to Saturday [when the week starts]
> and then from Sunday to Monday. This gives us 6[Saturday]- 2[Tuesday] +
> 1[Monday] - 0[Sunday] = 6 - d1 - d2.
>
> Now you can put in a correction...
> w1=int(to_char(d1,'W')) and w2= same thing for d2
>
> decode(sgn(w2,w1),0,0,-1, 6-w1-w2,1,w2-w1)+(the stuff above)
>
> I did this once a few years ago at a previous job; I don't remember if I got it
> in a single select statement or not. Hopefully, this will get you on the right
> track.
>
> Dan Hekimian-Williams
Received on Wed Nov 18 1998 - 15:54:31 CET