asked on

SQL query - inclue only work days

Hi experts,

I have to create a query that determines the difference in time between two dates and then calculate the average time difference based on a grouping of another field. There is a trick however - the calculation of the difference in time should exclude Saturdays and Sundays. The query as follows:

SELECT t.name, AVG(DATEDIFF(dd,t.dateone,t.datetwo) AS time
FROM db.dbo.table t
GROUP BY t.name

I have no idea how to do the exclusion of Saturdays and Sundays in the calculation. Any help would be much appreciated.

Thanks!

avdvyver

ASKER

My apologies - the query as follows:

SELECT t.name, AVG(DATEDIFF(dd,t.dateone,t.datetwo)) AS time
FROM db.dbo.table t
GROUP BY t.name

ASKER CERTIFIED SOLUTION

Aneesh

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Patrick Matthews

Hi avdvyver,

Aneesh's function will get you there, unless you have to consider "holidays", days that are Mon-Fri but for
one reason or another should not be counted as work days. Will you need to do this? If so, then you would
have to maintain a table of such days, and modify Aneesh's function to refer to that table.

Regards,

Patrick

avdvyver

ASKER

Hi Patrick,

Luckly I don't have to cater for holiday during the working week.

avdvyver

ASKER

Hi aneeshattingal,

I don't want to sound stuipd ... but I don't understand where the origional query fits into your answer. I'm sure it works, I just don't know where exactly to fit my query in. :)

Aneesh

SELECT t.name, AVG(dbo.GetWorkingdays(t.dateone,t.datetwo) AS time
FROM db.dbo.table t
GROUP BY t.name