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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2004-07-13 : 10:12:25
|
| Rob writes "Database: SQL Server 2000OS: Windows 2000I would like to know if anyone has an idea how to do the following: I need to determine the time elapsed between 2 dates. This time CANNOT include weekends or holidays. This is for reporting on a HelpDesk Ticket tracking system and want the abiltiy to determine how long the ticket has been open before the submitting user is contacted. Two fields will be involved: datTicketOpened, datContactUser. The real trick is when a ticket is opened for expample on Friday (4:30 pm) and the user is contacted on Monday (8:30 am), the time elapsed should only return approx. 16 hours because of the weekend. Any ideas or suggestions would be greatly appreciated." |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-07-13 : 13:52:06
|
| You need to make a tally table with your weekends and holidays. You can then find DATEDIFF on your datarange minus the count of records in tally table between that range. I would put a Holiday/Weekend designator on it also to differentiate the two.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
 |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
jrockfl
Posting Yak Master
223 Posts |
Posted - 2004-07-14 : 07:38:42
|
| I had to create something similiar with Cold Fusion. I had to determine how many business days are in the month and subtract any holidays. For me, this month has 21 business days. We had the Monday July 5th off.This calculates the first day and last day of the month. Then loops through and adds a day, except if it is a weekend. Then subtract any holidays.Your start date would be when the user submitted the ticket, and the end date would be when the user was contacted or the ticket was closed.I'm sure you are coding this in ASP, so you should be able to translate it from CF to ASP.The holiday part needs to be written in my application.<!--- This section is for the number of business days and days remaining ---><cfset startdate = DateFormat(CreateDateTime(year(now()), month(now()), 1, 0, 0, 0),"M/DD/YY")><cfset enddate = DateFormat(DateAdd("d", -1, DateAdd('m', 1, startdate)),"M/DD/YY")><!--- This month has 1 holiday off ---><cfset holidays = 1><!--- Calculate number of business days in the month ---><cfset currentdate = "#startDate#"><cfset totaldays = 0><cfloop from="1" to="#datediff('d',startdate,enddate)#" index="i"> <cfif "1|7" contains dayofweek(currentdate)> <cfset currentdate = dateadd('d',1,currentdate)> <cfelse> <cfset totaldays = totaldays + 1> <cfset currentdate = dateadd('d',1,currentdate)> </cfif></cfloop><!--- Final output of total days is month, subtract any holidays ---><cfset totaldays = totaldays - holidays> |
|
|
|
|
|
|
|
|
Time Elapsed Between Dates