weekday betweek two date
kan...@gmail.com
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?
Charles Hooper
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:00
01-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:
SELECT
START_DATE,
FINISH_DATE,
START_DATE+(ROWNUM-1) INTERMEDIATE_DATE,
TO_CHAR(START_DATE+(ROWNUM-1),'D') DAY_OF_WEEK,
DECODE(TO_CHAR(START_DATE+(ROWNUM-1),'D'),1,1,7,1,0) IS_WEEKEND,
DECODE(TO_CHAR(START_DATE+(ROWNUM-1),'D'),1,0,7,0,1) IS_WEEKDAY
FROM
(SELECT
START_DATE,
FINISH_DATE,
FINISH_DATE-START_DATE DAYS
FROM
T4) T4,
(SELECT
ROWNUM COUNTER_NO
FROM
DBA_OBJECTS
WHERE
ROWNUM<720) DO
WHERE
DO.COUNTER_NO <= (T4.DAYS+1);
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.
Charles Hooper
> kan...@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?
Note: the posted SQL statement contains *at least one* simple
typo/error that resulted in the above sequence of dates: note that
27-May-2006 should not be listed as an intermediate date between
1-May-2006 and 23-May-2006. The output when corrected looks like this:
START_DATE FINISH_DATE INTERMEDIATE_DATE DAY_OF_WEEK IS_WEEKEND
IS_WEEKDAY WEEKEND_DAYS WEEK_DAYS
22-Mar-2006 24-Mar-2006 22-Mar-2006 4 0 1 0 3
22-Mar-2006 24-Mar-2006 23-Mar-2006 5 0 1 0 3
22-Mar-2006 24-Mar-2006 24-Mar-2006 6 0 1 0 3
27-Mar-2006 1-Apr-2006 27-Mar-2006 2 0 1 1 5
27-Mar-2006 1-Apr-2006 28-Mar-2006 3 0 1 1 5
27-Mar-2006 1-Apr-2006 29-Mar-2006 4 0 1 1 5
27-Mar-2006 1-Apr-2006 30-Mar-2006 5 0 1 1 5
27-Mar-2006 1-Apr-2006 31-Mar-2006 6 0 1 1 5
27-Mar-2006 1-Apr-2006 1-Apr-2006 7 1 0 1 5
1-Apr-2006 8-Apr-2006 1-Apr-2006 7 1 0 3 5
1-Apr-2006 8-Apr-2006 2-Apr-2006 1 1 0 3 5
1-Apr-2006 8-Apr-2006 3-Apr-2006 2 0 1 3 5
1-Apr-2006 8-Apr-2006 4-Apr-2006 3 0 1 3 5
1-Apr-2006 8-Apr-2006 5-Apr-2006 4 0 1 3 5
1-Apr-2006 8-Apr-2006 6-Apr-2006 5 0 1 3 5
1-Apr-2006 8-Apr-2006 7-Apr-2006 6 0 1 3 5
1-Apr-2006 8-Apr-2006 8-Apr-2006 7 1 0 3 5
6-Apr-2006 16-Apr-2006 6-Apr-2006 5 0 1 4 7
6-Apr-2006 16-Apr-2006 7-Apr-2006 6 0 1 4 7
6-Apr-2006 16-Apr-2006 8-Apr-2006 7 1 0 4 7
6-Apr-2006 16-Apr-2006 9-Apr-2006 1 1 0 4 7
6-Apr-2006 16-Apr-2006 10-Apr-2006 2 0 1 4 7
6-Apr-2006 16-Apr-2006 11-Apr-2006 3 0 1 4 7
6-Apr-2006 16-Apr-2006 12-Apr-2006 4 0 1 4 7
6-Apr-2006 16-Apr-2006 13-Apr-2006 5 0 1 4 7
6-Apr-2006 16-Apr-2006 14-Apr-2006 6 0 1 4 7
6-Apr-2006 16-Apr-2006 15-Apr-2006 7 1 0 4 7
6-Apr-2006 16-Apr-2006 16-Apr-2006 1 1 0 4 7
11-Apr-2006 23-Apr-2006 11-Apr-2006 3 0 1 4 9
11-Apr-2006 23-Apr-2006 12-Apr-2006 4 0 1 4 9
11-Apr-2006 23-Apr-2006 13-Apr-2006 5 0 1 4 9
11-Apr-2006 23-Apr-2006 14-Apr-2006 6 0 1 4 9
11-Apr-2006 23-Apr-2006 15-Apr-2006 7 1 0 4 9
11-Apr-2006 23-Apr-2006 16-Apr-2006 1 1 0 4 9
11-Apr-2006 23-Apr-2006 17-Apr-2006 2 0 1 4 9
11-Apr-2006 23-Apr-2006 18-Apr-2006 3 0 1 4 9
11-Apr-2006 23-Apr-2006 19-Apr-2006 4 0 1 4 9
11-Apr-2006 23-Apr-2006 20-Apr-2006 5 0 1 4 9
11-Apr-2006 23-Apr-2006 21-Apr-2006 6 0 1 4 9
11-Apr-2006 23-Apr-2006 22-Apr-2006 7 1 0 4 9
11-Apr-2006 23-Apr-2006 23-Apr-2006 1 1 0 4 9
16-Apr-2006 1-May-2006 16-Apr-2006 1 1 0 5 11
16-Apr-2006 1-May-2006 17-Apr-2006 2 0 1 5 11
16-Apr-2006 1-May-2006 18-Apr-2006 3 0 1 5 11
One of the benefits of this method is that it the output can be joined
to a table containing company holidays. But, as stated previously, a
mathematical solution is much more efficient.
Michel Cadot
<kan...@gmail.com> a écrit dans le message de news: 1164201834....@j44g2000cwa.googlegroups.com...
Have a look at the thread "Counting the number of business days between 2 dates." on AskTom:
Regards
Michel Cadot