Re: weekday betweek two date

Charles Hooper wrote:
> 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?
> 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

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.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Wed Nov 22 2006 - 09:47:18 CST