sql server new users: FW: how to exclude weekends from a datediff? -- sql server new users

You're in the

sql server new users

group:

FW: how to exclude weekends from a datediff?

meg
11/30/2004 4:47:33 PM

sql server new users:

[quoted text, click to view]

I've created the calendar table and inserted my holidays, however I just don't know how to implement the
query. If anyone has any experience with this I would greatly appreciate it!!

Thanks,

meg

[quoted text, click to view]
news:951B92C9-446C-48D8-91E0-3E14D3FFA47A@microsoft.com...

See if this helps.

Why should I consider using an auxiliary calendar table?

http://www.aspfaq.com/show.asp?id=2519

AMB

[quoted text, click to view]

Hi, I am not sure how to implement a query where I have to find the difference between two dates but exclude
weekends and company holidays. I did figure I would use datepart or datename somehow but I'm not sure how.

I have a table that lists company holidays as well - but being a beginner at writing SQL I thought i'd ask
here how to implement it. Below is sample data for testing. What I need to do is find the service requests
(if any) that took longer than 48 hours to complete each fiscal month, if so, I have to exclude weekends and
holidays from the 48 hours to find any service requests that took more than 48 "working" hours to complete.

Thanks in advance, and let me know if you need more info.

create table ServiceRequest(

ServReqID int,

CreatedDate datetime,

CompletedDate datetime)

create table Fiscal(

FiscalMonth int,

FiscalYear int,

FiscalMonthStart datetime,

FiscalMonthEnd datetime)

create table Holiday(

HolidayStart datetime,

HolidayEnd datetime)

Insert into Holiday

Values ('2004-11-25 00:00:00.000','2004-11-26 23:59:00.000')

Insert Into ServiceRequest

Values (1, '2004-10-30 01:03:34.600','2004-10-30 01:40:06.600')

Insert Into ServiceRequest

Values (2, '2004-10-30 01:23:34.600','2004-10-30 01:43:06.307')

Insert Into ServiceRequest

Values (3, '2004-10-30 01:23:34.600','2004-10-30 01:46:07.930')

Insert Into ServiceRequest

Values (4, '2004-10-30 01:25:34.600','2004-11-01 02:46:07.590')

Insert Into ServiceRequest

Values (5, '2004-10-30 01:26:34.600','2004-11-02 01:46:08.497')

Insert Into ServiceRequest

Values (11, '2004-11-23 01:26:34.600','2004-11-29 01:46:08.497')

Insert Into ServiceRequest

Values (12, '2004-11-23 01:27:34.600','2004-11-29 00:46:08.497')

Insert Into Fiscal

Values (1,2005,'2004-10-30 00:00:00','2004-11-26 23:59:00')

--Query to find SRs that took more than 48 hours to complete:

select servicerequest.*, datediff(hh,createddate,completeddate)

from fiscal join servicerequest on createddate between fiscalmonthstart and fiscalmonthend

where fiscalmonth = 1 and fiscalyear = 2005 and

datediff(hh,createddate,completeddate) 48

Re: how to exclude weekends from a datediff?

meg
12/1/2004 9:40:05 AM

Thank you for your reply Jacco, I did go to this website and created this table, but being new to programming,
I don't know how to implement my query using the examples they are showing. Especially since I'm using hours
instead of days. If you could give me some sort of hint how to implement the query I would appreciate it.

thanks again.

[quoted text, click to view]
http://www.aspfaq.com/show.asp?id=2519
--
Jacco Schalkwijk
SQL Server MVP
[quoted text, click to view]

Re: how to exclude weekends from a datediff?

Jacco Schalkwijk
12/1/2004 11:36:20 AM

http://www.aspfaq.com/show.asp?id=2519

--
Jacco Schalkwijk
SQL Server MVP

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server new users group.

home · blog · groups

Questions? Comments? Contact the dn