help with query

[mikedaruke]

Hi,

I have dates like this

11-APR-08
10-APR-08
09-APR-08
08-APR-08
07-APR-08
04-APR-08
03-APR-08

I want to be able to find out how many days are between, but I cannot count Sat and Sun.

So 4/7/08 - 4/4/08 = 0

So somehow I need to compare the first date to the date under it, then if a weekend falls between, don't count those days.

What do you think

 

[SantaMufasa]

I'm a bit puzzled by your algorithm, Mike:

So 4/7/08 - 4/4/08 = 0

Those dates are Friday to Monday, so should the difference (not counting Saturday and Sunday) be 1 day difference? If the difference is 0, then would "Wednesday, April 9" to "Wednesday, April 9" have a difference of "-1"?


Please confirm before we post a suggested solution.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[SantaMufasa]

Mike,

Since I had to move on to another task presently, I'm offering one solution here that uses this algorithm: the difference between Monday and Tuesday = 1 and the difference between Friday and Monday = 1:

select * from mike_dates;

DT
---------
11-APR-08
10-APR-08
09-APR-08
08-APR-08
07-APR-08
04-APR-08
03-APR-08

7 rows selected.

create or replace function working_days(beg_dt date, end_dt date) return number is
    wk_days          number := 0;
    curr_date        date;
    day_displacement number := 1;
begin
    curr_date := beg_dt;
    while curr_date <= end_dt loop
        if to_char(curr_date,'DY') not in ('SAT','SUN') then
            wk_days := wk_days + 1;
        end if;
        day_displacement := day_displacement + 1;
        curr_date := beg_dt + day_displacement;
    end loop;
    return wk_days;
end;
/

select to_char(a.dt,'fmDy, Mon DD, yyyy') "Day A"
      ,to_char(b.dt,'fmDy, Mon DD, yyyy') "Day B"
      ,working_days(b.dt,a.dt) "Days Between"
  from mike_dates a
      ,mike_dates b
 where b.dt = (select max(dt) next_dt
                 from mike_dates
                where dt < a.dt)
/

Day A             Day B             Days Between
----------------- ----------------- ------------
Fri, Apr 11, 2008 Thu, Apr 10, 2008            1
Thu, Apr 10, 2008 Wed, Apr 9, 2008             1
Wed, Apr 9, 2008  Tue, Apr 8, 2008             1
Tue, Apr 8, 2008  Mon, Apr 7, 2008             1
Mon, Apr 7, 2008  Fri, Apr 4, 2008             1
Fri, Apr 4, 2008  Thu, Apr 3, 2008             1

6 rows selected.

I also had to presume that the order of your rows was descending since you cannot depend upon any particular physical order of rows in an Oracle table.

Let us know your thoughts.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]