options for time logging table design? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server Development (2000)

options for time logging table design?

Author

Topic

yonabout
Posting Yak Master

112 Posts

Posted - 2009-07-16 : 10:14:21

Hi,

I need to make a simple time logging system for internal use. I want to design the table so that it records an entry when the user logs on in the morning. The user will then make an entry each time they do something different until they logout, so each row in the table will have a single datetime entry.

I'm going to use self joins to work out how long each things taken.

Here's my 1st draft

drop table #clients
create table #clients
(
clientid int identity(1,1),
client varchar(10)
)

insert #clients
select 'Internal' union
select 'Client 1' union
select 'Client 2' union
select 'Client 3' union
select 'Client 4' 

drop table #timelog
create table 
#timelog(
TLid int identity (1,1),
Userid int,
ClientID int,
Taskid int,
Start datetime)

insert into #timelog
select 1, 5, 3, '2009-16-07 09:00:00.000' union
select 1, 1, 1, '2009-16-07 09:10:00.000' union
select 1, 2, 2, '2009-16-07 10:00:00.000' union
select 1, 5, 5, '2009-16-07 12:00:00.000' union
select 1, 5, 7, '2009-16-07 13:00:00.000' union
select 1, 1, 2, '2009-16-07 13:25:00.000' union
select 1, 3, 7, '2009-16-07 14:00:00.000' union
select 1, 1, 7, '2009-16-07 15:15:00.000' union
select 1, 3, 6, '2009-16-07 16:00:00.000' union
select 1, 2, 2, '2009-16-07 16:30:00.000' union
select 1, 4, 4, '2009-16-07 17:12:00.000' union
select 2, 5, 3, '2009-16-07 09:00:00.000' union
select 2, 5, 2, '2009-16-07 09:34:00.000' union
select 2, 5, 5, '2009-16-07 12:15:00.000' union
select 2, 5, 6, '2009-16-07 14:34:00.000' union
select 2, 5, 4, '2009-16-07 17:56:00.000'


drop table #task
create table 
#task(
taskId int identity (1,1),
Task varchar(100)
)
insert into #task
select 'Login' union
select 'Do a calc' union
select 'Process a leaver' union
select 'Lunch' union
select 'Send a letter' union
select 'Have a meeting' union
select 'Logout' 

select
tl1.userid,
c.client,
t.task,
tl1.start startTime,
min(tl2.start) StopTime,
datediff(mi, tl1.start, min(tl2.start)) DurationMins

from 
#timelog tl1
join #timelog tl2
on tl2.start > tl1.start 
join #task t
on tl1.taskid = t.taskid
join #clients c on c.clientid = tl1.clientid

where tl1.taskid <> 4

group by 
tl1.userid,
c.client,
t.task,
tl1.start

order by tl1.userid,  tl1.start

My question is, is performance going to suffer doing self joins to work out the duration of each task when there's a gazillion rows in the table, or would I be better building the app to include start and stop times against each task?

Cheers,

Yonabout

yonabout
Posting Yak Master

112 Posts

Posted - 2009-07-20 : 06:48:04

Anyone?

I'm going with a version of my first draft instead of recording stop times as well.

Cheers,

Yonabout

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources