Solved

SQL query - inclue only work days

Posted on 2006-10-26
6
1,026 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 500 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 92

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
Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

 

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
IF SQL Query 12 29
SQL Server In place upgrade from 2012 to 2014 12 22
Need age at date of document 5 20
Merge join vs exist 3 27
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
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…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

726 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