Calculate DateDiff for Sum of Total Hrs Worked

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)

Calculate DateDiff for Sum of Total Hrs Worked

Author

Topic

c4t5m4
Starting Member

2 Posts

Posted - 2006-03-04 : 02:57:00

I need to pull all data out of a single table, but need to calculate the datediff between each login/logout session. Then, calculate the sum of total hours worked, by employee number, for individual stats as well as overall sum of hours worked for team to populate department report.

Table Structure
stat_date datetime (PK)
empno int (PK)
campaign_name varchar(10) (PK)
event_time datetime (PK)
event_code varchar(2) (PK)
phone_no varchar(10)
call_length int
disp_code tinyint

Sample Data
stat_date     empno     campaign_name     event_time        event_code
2/24/2006     999              xyz           2/24/2006 11:29:00     1 (login)
2/24/2006     999              xyz              2/24/2006 14:15:00     5 (logout)
2/24/2006     999              abc              2/24/2006 14:46:00     1
2/24/2006     999              abc              2/24/2006 18:58:00     5
2/24/2006     1021              xyz              2/24/2006 05:45:00     1
2/24/2006     1021              xyz              2/24/2006 10:00:00     5
2/24/2006     2121              abc              2/24/2006 06:33:00     1
2/24/2006     2121              abc              2/24/2006 10:35:00     5
2/24/2006     2121              abc              2/24/2006 11:11:00     1
2/24/2006     2121              abc              2/24/2006 14:53:00     5

I imagine the query would be something along the lines of:

SELECT DateDiff(minute, tmLogIn, tmLogOut), empno, campaign_name
FROM table
WHERE stat_date = '2/24/2006' and event_code IN (1,5)
GROUP BY empno, campaign_name WITH ROLLUP

The flaw in this logic is that I cannot separate each login/logout session. I also tried the following query, but do not get the expected results either:

SELECT tblStrt.tmStart, tblEnd.tmEnd, dateDiff(minute, tblStrt.tmStart, tblEnd.tmEnd)
FROM
(
 SELECT distinct(event_time) as tmStart, empno, stat_date 
 FROM table
 WHERE event_code = 1 and stat_date = '2/24/06'
) as tblStrt
INNER JOIN 
(
 SELECT distinct(event_time) as tmEnd, empno, stat_date 
 FROM table 
 WHERE event_code = 5 and stat_date = '2/24/06'
) as tblEnd
ON tblStrt.empno = tblEnd.empno and tblStrt.stat_date = tblEnd.stat_date and tblEnd.tmEnd > tblStrt.tmStart

Your ideas/suggestions would be most appreciated. Thank you for your time.

Dena

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-03-04 : 03:24:05

You can use case when....

SELECT DateDiff(minute, tmLogIn, tmLogOut), empno, campaign_name,
Case when Event_Code =1 Then 'Login'
When Event_Code = 5 Then 'Logout' End
FROM table
WHERE stat_date = '2/24/2006' and event_code IN (1,5)
GROUP BY empno, campaign_name WITH ROLLUP

Hope this works for you...

Sucess Comes to those who Believe in Beauty of their Dream..

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-04 : 06:11:18

try this

select	stat_date, empno, campaign_name, datediff(minute, tmLogIn, tmLogOut) / 60.0 as hours
from
(
	select 	stat_date, empno, campaign_name, tmLogIn = event_time, 
		tmLogOut = (select top 1 event_time 
				from 	#sample x 
				where 	x.stat_date 	= s.stat_date 
				and 	x.empno 	= s.empno 
				and 	x.campaign_name = s.campaign_name 
				and	x.event_code	= 5
				and 	x.event_time 	> s.event_time 
				order by x.event_time)
	from	#sample s
	where	event_code	= 1
) as a

----------------------------------
'KH'

c4t5m4
Starting Member

2 Posts

Posted - 2006-03-04 : 15:04:50

Thank you for your suggestions. I wasn't able to get the case statement to work. Here is the final query.


select stat_date, empno, campaign_name, datediff(minute,tmLogIn,tmLogOut)/60 as hours
from
(
	select stat_date, empno, campaign_name, tmLogIn = event_time, tmLogOut = 
		(
		select top 1 event_time
		from stats_dialer x
		where x.stat_date = s.stat_date
		and x.empno = s.empno
		and x.campaign_name = s.campaign_name
		and x.event_code = 5 
		and x.event_time > s.event_time
		order by x.event_time
		)
	from stats_dialer s
	where event_code = 1
) as a	
group by stat_date, empno, campaign_name, tmLogIn, tmLogOut
order by tmLogIn

Thanks again!
Dena

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources