number of hours a record has been open

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 2000 Forums

Transact-SQL (2000)

number of hours a record has been open

Author

Topic

jamie
Aged Yak Warrior

542 Posts

Posted - 2006-07-07 : 07:28:57

Hi, I have a helpdesk db and need to work out how many hours it takes to close some records.
I have a logdate and a closedate. how could I show this info ?

data eg :

name logdate closedate
jamie 1/4/2006 10:00:00 2/4/2006 12:30:00
jamie 3/4/2006 09:10:00 10/4/2006 15:33:00

tthank you for any help.
Jamie

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-07-07 : 07:37:33

Is this?

Select *, DateDiff(hour,logdate,closedate) as Hours from yourTable

Madhivanan

Failing to plan is Planning to fail

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-07-07 : 07:37:36

can you give expected output in your example?

or is this what you want?

select name, datediff(hh, logdate, closedate) from #test

Harsh Athalye
India.
"Nothing is Impossible"

jamie
Aged Yak Warrior

542 Posts

Posted - 2006-07-07 : 07:44:47

thank you, I think that is what I need..

jamie
Aged Yak Warrior

542 Posts

Posted - 2006-07-07 : 07:57:18

Hi, thank you for this, but I have an extra issue.
I only want to beable to add up the hours between 8am and 6pm Monday to Friday.. . is that possible ?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-07-07 : 08:26:59

Also note that DATEDIFF(hour, logdate, closedate) will return zero if less than 60 minutes between the two times.
DateDiff function calculates only fully completed, time intervals. But there is an exception. Look at these examples

select datediff(hour, '2006-07-15 16:00:00', '2006-07-15 16:59:59')  -- 0 hours
select datediff(hour, '2006-07-15 16:00:00', '2006-07-15 17:00:00')  -- 1 hour
select datediff(hour, '2006-07-15 23:59:59', '2006-07-16 00:00:01')  -- 1 hour

And how do you handle a holiday? Or Easter Monday?

Use a date table for calculating what you need.

Peter Larsson
Helsingborg, Sweden

jamie
Aged Yak Warrior

542 Posts

Posted - 2006-07-07 : 08:38:18

don't really care about holidays so much yet..
how can I get around the datediff problems you mention ?
can I show mins and hours ?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-07-07 : 08:56:01

Something like this, but this doesn't calculate weekends. for that you need a date table.

select	name,
	CASE
		WHEN DATEDIFF(day, logdate, closedate) = 0 THEN DATEDIFF(mi, logdate, closedate)
		ELSE 600 * (DATEDIFF(day, logdate, closedate) - 1) + 1080 - datediff(mi, '08:00:00', logdate) % 1440 + datediff(mi, '08:00:00', closedate) % 1440
	END Mins
FROM	MyTable

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-07-07 : 09:02:32

Or like this (assumes that neither logdate nor closedate is on weekend)

select	name,
	CASE
		WHEN DATEDIFF(day, logdate, closedate) = 0 THEN DATEDIFF(mi, logdate, closedate)
		ELSE 600 * (select count(*) - 2 from F_TABLE_DATE(logdate, closedate) where day_of_week between 2 and 6) + -- Number of minutes for whole day, not including logdate and closedate
			1080 - datediff(mi, '08:00:00', logdate) % 1440 + -- Numbers of minutes from logdate to closing at 6 pm same day.
			datediff(mi, '08:00:00', closedate) % 1440 -- Number of minutes since 8 am to closedate same day
	END Mins
FROM	MyTable

with the help of the function found here [url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519[/url].

Peter Larsson
Helsingborg, Sweden

jamie
Aged Yak Warrior

542 Posts

Posted - 2006-07-07 : 09:45:01

cheers Peter, I'll have a play with this code !

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-07-07 : 10:27:51

This seems to do the job. If you have a holiday table, that's easy to add to the join to exclude holidays.


-- Table for test data
declare @t table (
name varchar(10) not null,
logdate  datetime not null,
closedate datetime not null)

-- Load test data
insert into @t
select
'jamie', '20060104 10:00:00','20060207 12:30:00' union all
select
'bill' ,'20060303 09:10:00','20061004 15:33:00' 

declare @st datetime
declare @et datetime

-- Get daterange for date table function
select
	@st= dateadd(dd,datediff(dd,0,min(logdate)),0),
	@et =dateadd(dd,datediff(dd,0,max(closedate)),0)
from @t

-- List test data
select * from @t


select
	a.NAME,
	TOTAL_HOURS =
	-- Hours = sum of total minutes/60.
	convert(numeric(10,2),round(sum(
	datediff(mi,
	case  -- Start time on this date
	when a.logdate < b.Work_Start
	then b.Work_Start
	else a.logdate
	end,
	case   -- End time on this date
	when a.closedate < b.Work_End
	then a.closedate
	else b.Work_End
	end ))/60.000,2))
from
	@t a
	join
	(
	-- Get weekdays, and hours range on each date
	select
		DATE,
		WORK_START = DATE+'08:00:00.000',
		WORK_END = DATE+'18:00:00.000'
	from
		F_TABLE_DATE(@st,@et) bb
	where
		DAY_OF_WEEK between 2 and 6
	) b
	on
	a.LOGDATE <= b.WORK_END And a.CLOSEDATE >= b.WORK_START
group by
	a.NAME
order by
	a.NAME

Results:



(2 row(s) affected)

name       logdate                   closedate
---------- ------------------------- -------------------------
jamie      2006-01-04 10:00:00.000   2006-02-07 12:30:00.000
bill       2006-03-03 09:10:00.000   2006-10-04 15:33:00.000

(2 row(s) affected)

NAME       TOTAL_HOURS  
---------- ------------ 
bill       1536.38
jamie      242.50

(2 row(s) affected)

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources