December 18, 2004 at 12:05 pm
I have a Helpdesk Application that I need to know the lapse time from DateRecieved and Assigned Date. Also from DateRecieved to DateClosed ...etc. Below gives me the time in Minutes which is what I want but I want to exclude weekends and if at all possible exclude non-working hours. I would like it to output 1 Day 12 Hours 23 Minutes??? Is this possible?
I also have a table that has all of the weekend dates in it. I am using NVARCHAR for the field types.
SELECT ROUND(DATEDIFF(s, PRBRCVDT, ASSIGNDT), 2) / 60 AS timelapse
FROM dbo.HELPDESKMAIN
WHERE (ASSIGNDT IS NOT NULL)
Thank you in advance for any help!!!
Jason
December 18, 2004 at 11:17 pm
I have used a lookup table.
CREATE TABLE dbo.BusinessDays (
BusinessDay datetime NOT NULL PRIMARY KEY CLUSTERED)
go
Load the table with dates, starting with the first business day of the year:
TRUNCATE TABLE dbo.BusinessDays
declare @i smallint
SET @i = 0
WHILE @i < 365
BEGIN
INSERT INTO dbo.BusinessDays
SELECT DateAdd(dd, @i, '2005-01-03')
SET @i = @i + 1
END
ALTER TABLE dbo.BusinessDays ADD DayNumber smallint IDENTITY (1, 1)
declare @DateReceived datetime, @DateAssigned datetime, @ElapsedDays smallint
SET @DateReceived = '2005-01-13 8:00AM'
SET @DateAssigned = '2005-01-17 12:05PM'
SET @ElapsedDays =
(SELECT DayNumber FROM dbo.BusinessDays
WHERE BusinessDay = CONVERT( varchar(8), @DateAssigned, 112)) -
(SELECT DayNumber FROM dbo.BusinessDays
WHERE BusinessDay = CONVERT( varchar(8), @DateReceived, 112))
PRINT CAST(@ElapsedDays AS varchar(3))
December 18, 2004 at 11:19 pm
That's odd, the page redacted most of my comments. Right before the ALTER TABLE you need to manually edit the table, deleting the rows which have dates that are not business days (weekends and holidays). Also, you have to figure out the time component, but I am out of time myself here. Maybe someone else can chip in. Luck, Dave
December 20, 2004 at 5:14 pm
Can you show me an example?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply