Count days between 2 dates on same record 2

[cmmrfrds]

In an SQL built-in function, is it possible to count the days between 2 dates on the same record and eliminate weekends from the count.

For example.
Appointment date = 1/1/2007
Arrival date = 2/5/2007
Should be 24 work days between

 

[SantaMufasa]

Cmmrfrds,

Since your request doesn't make special treatment for holidays, there are actually 26 weekdays between Jan 1, 2007, and Feb 5, 2007. Here is a function that accomplishes your original specifications:

create or replace function weekday_diff(dt1 date,dt2 date) return number is
    curr_dt  date;
    day_cnt  number := 0;
    date_beg date;
    date_end date;
begin
    date_beg := least(dt1,dt2);
    date_end := greatest(dt1,dt2);
    curr_dt  := date_beg;
    while curr_dt <= date_end loop
        if to_char(curr_dt,'DY') not in ('SAT','SUN') then
            day_cnt := day_cnt + 1;
        end if;
        curr_dt := curr_dt + 1;
    end loop;
    return day_cnt;
end;
/

Function created.

Here is a proof-of-concept using your original dates:

select weekday_diff(to_date('01/01/2007','mm/dd/yyyy')
,to_date('02/05/2007','mm/dd/yyyy')) diff from dual;

   DIFF
-------
     26

Let us know if this meets with your satisfaction.

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

 

[cmmrfrds]

Dave, I appreciate your effort, but I have a problem in that the DBA does not let us create functions. So, we need to rely on him to do this for us that is why I was hoping there was a way with built-in Oracle functions. Since you have tested the code, I will ask our DBA if he will create this function for me.

Thank you. I will let you know what happens.

 

[taupirho]

This should work for you (and no need for a function) :-

select count(*) from(
select to_date('05-feb-07') -level + 1 dte
from dual
connect by level < 1000 )
where dte >= '01-jan-07'
and to_char(dte,'dy') not in ('sat','sun')

The number in the connect by level comparison must be greater than the total number of days between the two dates you are testing against. In practice this isn't a problem as even setting this to 100000 for instance i.e going on for 300 years the response time is still reasonable.

 

[cmmrfrds]

taupirho, I don't know how to correlate this subquery into my select list. I am not sure how to pass my 2 dates into this subquery if I treat it as a column in my tables select list.

Here is generally what I tried.

Select
enc1.pat_id,
enc1.pat_enc_csn_id,
enc1.APPT_MADE_DATE,
enc1.contact_date,
(
select count(*)
from(
select
to_date(enc.contact_date) -level + 1 dte
from clarity.pat_enc enc
where enc.pat_enc_csn_id = enc1.pat_enc_csn_id
connect by level < 1000
) aloop
where dte >= enc1.APPT_MADE_DATE
and to_char(dte,'dy') not in ('sat','sun')
) as cnt
From clarity.pat_enc enc1
where rownum < 10

 

[taupirho]

Maybe more like this:-

Select
enc1.pat_id,
enc1.pat_enc_csn_id,
enc1.APPT_MADE_DATE,
enc1.contact_date,
enc.cnt
From
clarity.pat_enc enc1,
(
select count(*) cnt
from
(
select to_date(enc1.conact_date) -level + 1 dte
from dual
connect by level < 1000
)
where dte >= enc1.APPT_MADE_DATE
and to_char(dte,'dy') not in ('sat','sun')
) enc
where rownum < 10

 

[taupirho]

My last post is incorrect, I need to create a proper table with some proper data in it. Will post again later.

 

[cmmrfrds]

taupirho, I get a reference error on the inner reference to
enc1.contact_date. I appreciate trying this code but still not sure how to implement.

Dave, our DBA did create the user defined function you created and tested.

We can go with the user defined function.

 

[taupirho]

Hmmm.... trickier than it first looks

SQL> select dte2 end_date, dte1 start_date from tom
2 /

END_DATE START_DAT
--------- ---------
13-MAR-07 13-MAR-06
01-DEC-08 13-MAR-06
17-AUG-05 08-JUN-01
05-FEB-07 01-JAN-07

1 select distinct count(*) over(partition by dte2,dte1) + 1 working_days,
2 dte2 end_date,dte1 start_date
3 from (select level x from dual
4 connect by level < 10000) days, tom
5 where x <= dte2 - dte1
6* and to_char(dte1+x,'DY') not in ('SAT','SUN')
SQL> /

WORKING_DAYS END_DATE START_DAT
------------ --------- ---------
1094 17-AUG-05 08-JUN-01
26 05-FEB-07 01-JAN-07
262 13-MAR-07 13-MAR-06
711 01-DEC-08 13-MAR-06



2 caveats.

The number in the connect by level < XXXX part must be > than the max number of days between any start/end date

Duplicate start/end dates in the tables will cause it to break.