adding a function to within a sql query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

adding a function to within a sql query

Author

Topic

duncant
Starting Member

18 Posts

Posted - 2012-05-31 : 06:16:04

I have a query that pulls business days from a startdate and enddate (example from the web)
And I have my query.
I would like to piece the two together but unsure of how to accomplish this.


//business days query
select (DATEDIFF(dd, @StartDate, @EndDate) + 1) -- Number of days between Start and End
-
(
	(DATEDIFF(wk, @StartDate, @EndDate)*2) --Weekend Days
	+(case when DATENAME(dw, @StartDate) = 'Sunday' then 1 else 0 end) --Taking into consideration the startdate being a sunday
	+(case when DATENAME(dw, @EndDate) = 'Saturday' then 1 else 0 end) --Taking into consideration the startdate being a saturday
)


//my query
declare @IssueType varchar(10)
declare @StartDate DateTime
declare @EndDate DateTime

set @IssueType = 'MyIssue'
set @StartDate = '1/1/2012'
set @EndDate = '3/31/2012'
select RefNo, FormReceivedThroughDoor, CallCompleteDate
	--,datediff(d, FormReceivedThroughDoor,CallCompleteDate) as DaysBetween
	from tbl_complaints
	where 
        (IssueType = @IssueType)
	and (FormReceivedThroughDoor between @StartDate and @EndDate)
	and (DATEPART(WEEKDAY, FormReceivedThroughDoor)between 2 and 6)

What I'm aiming/trying to add is the function to replace "--,datediff(d, FormReceivedThroughDoor,CallCompleteDate) as DaysBetween" so the query gets the true number of business days.

Thanks in advance

Duncan

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-05-31 : 06:28:09

What do you do about holidays? Do they count as business days?
EDIT:
http://visakhm.blogspot.com/2010/03/calculating-business-hours.html

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

duncant
Starting Member

18 Posts

Posted - 2012-05-31 : 06:39:40

At the moment I have a seperate table for holidays which I will query at the end.
How do I include the business select query to my query?

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-05-31 : 06:44:13

Combine your weekends with your holidays table, and any day that is NOT in there is a business day. easy peasy.

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

duncant
Starting Member

18 Posts

Posted - 2012-05-31 : 06:56:19

But I'm trying encorporate the DaysBetween query to the DaysBewteen column in my query?

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-05-31 : 07:36:39

You will have your calendar table that has all holidays and weekends. Instead of in between, you select where the days are NOT in the 'holiday calendar'.

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

duncant
Starting Member

18 Posts

Posted - 2012-05-31 : 09:36:41

Not really convinced that I would want to put all the weekends in a table?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources