Number of working days between two date fields 3

[teralearner]

I need to calculate number of working days between two date fields. Any sugestions?
I am a SAS user. I am able to use SAS MACRO to write a query
like this:

select date2 - date1 + 1 as days0,
case when '2002-01-01' between date1 and date2 then days0 - 1 else days0 end as days1, .......

all the way to the last Sunday of 2002.
But the query failed after 56 hours with the following error:
ER_LINKDOWN (130): DBC connection broken - Unknown system error: 6128

 

[dnoeth]

What do you mean by working days?
Monday through friday/saturday?
Including/excluding public holdays, e.g. christmas/eastern/local holidays?

For a complex calculation the only way is probably to use a calendar table with info about working days.

Then it's a simple
select
count(*)
from
mycalendar
where
calendardate between :startdate and :enddate;

If you really just trying to calculate the number of days, that don't fall on Saturday or Sunday you can use following algorithm:
The number of whole weeks * 5 +
the number of remaining days, that don't fall on Saturday or Sunday

In Teradata the number of whole weeks is
((enddate - startdate + 1) / 7) * 5

and weekday is calculated by
(((startdate - DATE '0001-01-01') mod 7) + 1)
this returns the ISO weekday, i.e. Monday = 1

select
/** The number of whole weeks * 5 **/
((enddate - startdate + 1) / 7) * 5 +

/** the number of remaining days, that don't fall on Saturday or Sunday **/
(case when (enddate - startdate + 1) mod 7 < 1
or (((startdate - DATE '0001-01-01') mod 7) + 1) in (6,7)
then 0 else 1 end) +
(case when (enddate - startdate + 1) mod 7 < 2
or (((startdate + 1 - DATE '0001-01-01') mod 7) + 1) in (6,7)
then 0 else 1 end) +
(case when (enddate - startdate + 1) mod 7 < 3
or (((startdate + 2 - DATE '0001-01-01') mod 7) + 1) in (6,7)
then 0 else 1 end) +
(case when (enddate - startdate + 1) mod 7 < 4
or (((startdate + 3 - DATE '0001-01-01') mod 7) + 1) in (6,7)
then 0 else 1 end) +
(case when (enddate - startdate + 1) mod 7 < 5
or (((startdate + 4 - DATE '0001-01-01') mod 7) + 1) in (6,7)
then 0 else 1 end) +
(case when (enddate - startdate + 1) mod 7 < 6
or (((startdate + 5 - DATE '0001-01-01') mod 7) + 1) in (6,7)
then 0 else 1 end) as workingdays
from
(
select
date '2003-01-06' as startdate,
date '2003-01-17' as enddate
) dt

Dieter

 

[BillDHS]

Could you use something as simple as:

select count(*)
from sys_calendar.calendar
where calendar_date between
'2002-10-01' and '2003-02-10'
and NOT day_of_week IN (1,7)

 

[teralearner]

Thanks both of you!

 

[jgerstb]

How about something like this.

SELECT SUM(CASE WHEN day_of_week BETWEEN 2 AND 6 THEN 1 ELSE 0 END) as working_days
FROM sys_calendar.calendar
WHERE calendar_date BETWEEN '2003-01-01' AND '2003-01-31'
AND calendar_date NOT IN ('2003-01-01')

Counting days Monday thru Friday and supply a list of exceptions either hardcoded as in the example above or put them in a table.