?
Solved

SQL query - inclue only work days

Posted on 2006-10-26
6
Medium Priority
?
1,028 Views
Last Modified: 2008-01-09
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!
0
Comment
Question by:avdvyver
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 

Author Comment

by:avdvyver
ID: 17810844
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
0
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 2000 total points
ID: 17810880
use this function to get the no of working days b/w 2 dates

CREATE FUNCTION dbo.GetWorkingDays
( @StartDate datetime,
@EndDate datetime )
RETURNS INT
AS
BEGIN
DECLARE @WorkDays int, @FirstPart int
DECLARE @FirstNum int, @TotalDays int
DECLARE @LastNum int, @LastPart int
IF (DATEDIFF(day, @StartDate, @EndDate) < 2)
BEGIN
RETURN ( 0 )
END
SELECT
@TotalDays = DATEDIFF(day, @StartDate, @EndDate) - 1,
@FirstPart = CASE DATENAME(weekday, @StartDate)
WHEN 'Sunday' THEN 6
WHEN 'Monday' THEN 5
WHEN 'Tuesday' THEN 4
WHEN 'Wednesday' THEN 3
WHEN 'Thursday' THEN 2
WHEN 'Friday' THEN 1
WHEN 'Saturday' THEN 0
END,
@FirstNum = CASE DATENAME(weekday, @StartDate)
WHEN 'Sunday' THEN 5
WHEN 'Monday' THEN 4
WHEN 'Tuesday' THEN 3
WHEN 'Wednesday' THEN 2
WHEN 'Thursday' THEN 1
WHEN 'Friday' THEN 0
WHEN 'Saturday' THEN 0
END
IF (@TotalDays < @FirstPart)
BEGIN
SELECT @WorkDays = @TotalDays
END
ELSE
BEGIN
SELECT @WorkDays = (@TotalDays - @FirstPart) / 7
SELECT @LastPart = (@TotalDays - @FirstPart) % 7
SELECT @LastNum = CASE
WHEN (@LastPart <> 0) THEN @LastPart - 1
ELSE 0
END
SELECT @WorkDays = @WorkDays * 5 + @FirstNum + @LastNum
END
RETURN ( @WorkDays )
END
GO

0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 17810922
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
0
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 

Author Comment

by:avdvyver
ID: 17810931
Hi Patrick,

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

Author Comment

by:avdvyver
ID: 17811016
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. :)
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 17811025
SELECT t.name, AVG(dbo.GetWorkingdays(t.dateone,t.datetwo) AS time
FROM db.dbo.table t
GROUP BY t.name
0

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question