| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: weekday betweek two date
kandy71_at_gmail.com wrote:
> Hi,
> In a tbale I have two dates, start and end.
> I need to count the days between this two dates? Easy (done)
> Now I need to know how many days are normal week day and weekends.
> I really do not know how to do.
> Can you give me some hint?
CREATE TABLE T4 (
START_DATE DATE,
FINISH_DATE DATE);
INSERT INTO
T4
SELECT
TRUNC(SYSDATE-5*(50-ROWNUM)) START_DATE,
TRUNC(SYSDATE-5*(50-ROWNUM*1.5)) FINISH_DATE
FROM
DBA_OBJECTS
WHERE
ROWNUM <=10;
SELECT
*
FROM
T4;
START_DATE FINISH_DATE
==================== ==================== 22-MAR-2006 00:00:00 24-MAR-2006 00:00:00 27-MAR-2006 00:00:00 01-APR-2006 00:00:00 01-APR-2006 00:00:00 08-APR-2006 00:00:00 06-APR-2006 00:00:00 16-APR-2006 00:00:00 11-APR-2006 00:00:00 23-APR-2006 00:00:00 16-APR-2006 00:00:00 01-MAY-2006 00:00:00 21-APR-2006 00:00:00 08-MAY-2006 00:00:00 26-APR-2006 00:00:00 16-MAY-2006 00:00:0001-MAY-2006 00:00:00 23-MAY-2006 00:00:00 06-MAY-2006 00:00:00 31-MAY-2006 00:00:00 10 ROWS SELECTED Do not use this in production:
START_DATE FINISH_DATE INTERMEDIATE_DATE DAY_OF_WEEK IS_WEEKEND IS_WEEKDAY
6-May-2006 31-May-2006 6-May-2006 7 1 0 6-May-2006 31-May-2006 7-May-2006 1 1 0 6-May-2006 31-May-2006 8-May-2006 2 0 1 6-May-2006 31-May-2006 9-May-2006 3 0 1 6-May-2006 31-May-2006 10-May-2006 4 0 1 6-May-2006 31-May-2006 11-May-2006 5 0 1 6-May-2006 31-May-2006 12-May-2006 6 0 1 6-May-2006 31-May-2006 13-May-2006 7 1 0 6-May-2006 31-May-2006 14-May-2006 1 1 0 6-May-2006 31-May-2006 15-May-2006 2 0 1 6-May-2006 31-May-2006 16-May-2006 3 0 1 6-May-2006 31-May-2006 17-May-2006 4 0 1 6-May-2006 31-May-2006 18-May-2006 5 0 1 6-May-2006 31-May-2006 19-May-2006 6 0 1 6-May-2006 31-May-2006 20-May-2006 7 1 0 6-May-2006 31-May-2006 21-May-2006 1 1 0 6-May-2006 31-May-2006 22-May-2006 2 0 1 6-May-2006 31-May-2006 23-May-2006 3 0 1 6-May-2006 31-May-2006 24-May-2006 4 0 1 6-May-2006 31-May-2006 25-May-2006 5 0 1 6-May-2006 31-May-2006 26-May-2006 6 0 1 6-May-2006 31-May-2006 27-May-2006 7 1 0 6-May-2006 31-May-2006 28-May-2006 1 1 0 6-May-2006 31-May-2006 29-May-2006 2 0 1 6-May-2006 31-May-2006 30-May-2006 3 0 1 6-May-2006 31-May-2006 31-May-2006 4 0 1 1-May-2006 23-May-2006 27-May-2006 7 1 0 1-May-2006 23-May-2006 28-May-2006 1 1 0 1-May-2006 23-May-2006 29-May-2006 2 0 1 1-May-2006 23-May-2006 30-May-2006 3 0 1 1-May-2006 23-May-2006 31-May-2006 4 0 1 1-May-2006 23-May-2006 1-Jun-2006 5 0 1 1-May-2006 23-May-2006 2-Jun-2006 6 0 1 1-May-2006 23-May-2006 3-Jun-2006 7 1 0 1-May-2006 23-May-2006 4-Jun-2006 1 1 0 1-May-2006 23-May-2006 5-Jun-2006 2 0 1 1-May-2006 23-May-2006 6-Jun-2006 3 0 1 1-May-2006 23-May-2006 7-Jun-2006 4 0 1 1-May-2006 23-May-2006 8-Jun-2006 5 0 1 1-May-2006 23-May-2006 9-Jun-2006 6 0 1 1-May-2006 23-May-2006 10-Jun-2006 7 1 0 1-May-2006 23-May-2006 11-Jun-2006 1 1 0 1-May-2006 23-May-2006 12-Jun-2006 2 0 1 1-May-2006 23-May-2006 13-Jun-2006 3 0 1 1-May-2006 23-May-2006 14-Jun-2006 4 0 1 1-May-2006 23-May-2006 15-Jun-2006 5 0 1 1-May-2006 23-May-2006 16-Jun-2006 6 0 1 1-May-2006 23-May-2006 17-Jun-2006 7 1 0 1-May-2006 23-May-2006 18-Jun-2006 1 1 0 26-Apr-2006 16-May-2006 14-Jun-2006 4 0 1 26-Apr-2006 16-May-2006 15-Jun-2006 5 0 1...
What is happening in the above? We need a source for a counter, that
can count from 1 to the number of days - the DBA_OBJECTS, or any other
large table/view can provide that by accessing the ROWNUM pseudo
column. We then join that counter to the table in question by
specifying a range for the join. Using TO_CHAR, we convert the
offsetted date to a day of the week, 1 as Sunday and 7 as Saturday. By
using DECODE to pick out the 1s and 7s, we can tell if the day is a
weekday or a weekend. If we then SUM the last two columns, we can find
the number of week days and weekend days, or you can add this to what
is being selected to find the answer as we display the results of the
above SQL statement:
SUM(DECODE(TO_CHAR(START_DATE+(ROWNUM-1),'D'),1,1,7,1,0)) OVER
(PARTITION BY START_DATE,FINISH_DATE) WEEKEND_DAYS,
SUM(DECODE(TO_CHAR(START_DATE+(ROWNUM-1),'D'),1,0,7,0,1)) OVER
(PARTITION BY START_DATE,FINISH_DATE) WEEK_DAYS
The above method will not scale very well. Mathemathical methods will
scale much better. A possible starting point:
SELECT
START_DATE,
FINISH_DATE,
FINISH_DATE-START_DATE DAYS,
NEXT_DAY(START_DATE,'MONDAY') NEXT_MONDAY,
TRUNC((FINISH_DATE-(NEXT_DAY(START_DATE,'MONDAY')))/7*5)
FROM
T4;
Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc.
Received on Wed Nov 22 2006 - 09:10:23 CST
 |
 |