How to get total time for each employee

Question

Post reply

How to get total time for each employee

Atif

Hall of Fame

Points: 3578
More actions
August 26, 2008 at 6:38 am

#75301

I have a table like
CREATE TABLE [dbo].[test](
[empno] [varchar](20) NOT NULL,[startdate] [datetime] NULL,
[enddate] [datetime] NULL)
and it has values like
insert into test
select '1-72-0143','2002-11-12 00:00:00.000','2003-06-27 00:00:00.000'
union all
select '1-72-0143','2003-06-27 00:00:00.000','2003-08-15 00:00:00.000'
union all
select '1-72-0143','2003-08-15 00:00:00.000','2003-12-30 00:00:00.000'
union all
select '1-72-0143','2004-05-13 00:00:00.000','2006-04-29 00:00:00.000'
union all
select '1-72-0143','2006-04-29 00:00:00.000','2008-05-12 00:00:00.000'
union all
select '127-1986-1','2002-04-18 00:00:00.000','2002-08-28 00:00:00.000'
union all
select '127-1986-1','2002-08-29 00:00:00.000','2006-11-18 00:00:00.000'
union all
select '127-1986-1','2006-11-18 00:00:00.000','2007-09-03 00:00:00.000'
There are two employees with their posting start and end dates. How could i get the whole time of posting (enddate-startdate) for each employee.
Regards
DBDigger Microsoft Data Platform Consultancy.

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 1

HI there,

Is this what you looking for?

SELECT

s.[empno] ,

DATEDIFF(minute,s.[startdate],e.[enddate])

,[startdate]

,[enddate]

FROM (SELECT MAX(Enddate) as Enddate, [empno] FROM test GROUP BY [empno]) e

INNER JOIN (SELECT MIN(Startdate) as Startdate , [empno] FROM test GROUP BY [empno]) s

ON s.[empno] = e.[empno]

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

Steve Jones - SSC Editor SSC Guru Points: 736254 More actions · Answer 2

Is it time for each section added or time from first to last timestamp for each employee?

You might need to built a CTE to grab all the times and then sum those up and group by employee, depending on your needs.

Atif Hall of Fame Points: 3578 More actions · Answer 3

i require time for each section added. Because there may be gaps between the pairs of dates.

DBDigger Microsoft Data Platform Consultancy.

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 4

Hi again.

Could you give us an example of what the ouput should be?

Thanks

Chris

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

Atif Hall of Fame Points: 3578 More actions · Answer 5

Here is the way i want to calculate the dates for each employee

.

At the end i will have X.Y year as result. I require accuracy till months only.

DBDigger Microsoft Data Platform Consultancy.

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 6

WOuld this be what you want?

SELECT

[empno],

SUM(DATEDIFF(Month,[startdate],[enddate])) as tot

FROM test

GROUP BY [empno]

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

Steve Jones - SSC Editor SSC Guru Points: 736254 More actions · Answer 7

with MyCTE as

( select

empno

, datediff(d, startdate, enddate) 'worktime'

from test

)

select empno, sum(worktime) 'total'

from MyCTE

group by empno

Atif Hall of Fame Points: 3578 More actions · Answer 8

I think calculation is no correct. It could not be 61 (years) from 2002 to 2008. We have to divide the datediff with 12 and some thing like that.

DBDigger Microsoft Data Platform Consultancy.

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 9

My Answer is in Months

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

Atif Hall of Fame Points: 3578 More actions · Answer 10

I have my production server on SQL Server 2000. So no CTE here.:unsure:

DBDigger Microsoft Data Platform Consultancy.

Atif Hall of Fame Points: 3578 More actions · Answer 11

Ok , thanks alot christopher. i hope it will work.

DBDigger Microsoft Data Platform Consultancy.

Steve Jones - SSC Editor SSC Guru Points: 736254 More actions · Answer 12

Steve Jones - SSC Editor

SSC Guru

Points: 736254

August 26, 2008 at 8:29 am

#863176

Sorry about that; should have noticed the forum 🙁

Atif Hall of Fame Points: 3578 More actions · Answer 13

Its working now. and thanks for all of you.

DBDigger Microsoft Data Platform Consultancy.

cwoodbury Grasshopper Points: 11 More actions · Answer 14

Thank you very much. I am doing downtime reporting and this worked great. I just changed months to minutes to get the minutes from my DT_Start, DT_Stop.

I do the Controls Automation and have my own SQL Servers and this stuff is all new to me but with resources like this I am learning quickly.