time difference within a group

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

General SQL Server Forums

New to SQL Server Programming

time difference within a group

Author

Topic

BendJoe
Posting Yak Master

128 Posts

Posted - 2010-03-25 : 14:05:00

I have a table
id,worktype,timestamp

i need to select
count(id),worktype, difference(timestamp last item in group,timestamp first item in group) group by
worktype.
the timestamp is of type datetime.
How can I do it?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-25 : 14:07:48

[code]select worktype,count(id) as cnt, datediff(dd,MIN(timestamp),MAX(timestamp)) AS Diff
FROM table
GROUP BY worktype[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

BendJoe
Posting Yak Master

128 Posts

Posted - 2010-03-25 : 14:39:43

quote:
Originally posted by visakh16

select worktype,count(id) as cnt, datediff(dd,MIN(timestamp),MAX(timestamp)) AS Diff
FROM table
GROUP BY worktype

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Thanks
Is there a way I can convert the datediff in seconds into hh:mm:ss format?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-25 : 14:49:33

yes


SELECT worktype,cnt,CAST((DATEDIFF(dd,0,Diff)* 24) + CONVERT(varchar(2),DATEADD(dd,-1 * DATEDIFF(dd,0,Diff),Diff),108)*1 AS varchar(4)) + ':' + RIGHT(CONVERT(varchar(8),DATEADD(dd,-1 * DATEDIFF(dd,0,Diff),Diff),108),5)
FROM
(
select worktype,count(id) as cnt, dateadd(ss,datediff(ss,MIN(timestamp),MAX(timestamp)),0) AS Diff
FROM table
GROUP BY worktype
)t

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources