calculate difference - SQL Server Forums

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

calculate difference

Author

Topic

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2008-10-27 : 10:05:24

hi

simple question


declare @temp table
(name nvarchar(10)
,[date] smalldatetime
,status nvarchar(15)
)

insert into @temp
select 'Franc','2008/10/10 14:10:23.000', 'LOGIN' union all
select 'Franc','2008/10/10 14:10:34.000', 'LOGOFF' union all
select 'Franc','2008/10/10 15:08:34.000', 'LOGIN' union all
select 'Franc','2008/10/10 15:15:34.000', 'LOGOFF' union all
select 'Franc','2008/10/10 15:45:31.000', 'LOGIN' union all
select 'Franc','2008/10/10 15:47:21.000', 'LOGOFF' 

select * from @temp

how to calculate difference in time between login and logoff time for Franc (in sql server 2000)? or to sum it up and see logged time :)

thank you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-27 : 10:11:48

[code]CREATE TABLE #Temp
(ID int identity(1,1),
name nvarchar(10)
,[date] smalldatetime
,status nvarchar(15)
)
INSERT INTO #Temp (name,[date],status)
SELECT name,[date],status
FROM @temp
ORDER BY name,[date]

SELECT t1.name, SUM(DATEDIFF(ss,t1.name,t2.name)) AS timediff
FROM #Temp t1
LEFT JOIN #Temp t2
ON t1.name=t2.name
AND t1.ID=t2.ID-1
AND t1.status='LOGIN'
AND t2.status='LOGOFF'
GROUP BY t1.name[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-27 : 10:16:12

try this too


SELECT name, SUM(DATEDIFF(ss,In,Out) AS TimeDiff
FROM
(
SELECT name,
[date] AS In,
(select min([date]) from @temp WHERE name=t.name AND status='LOGOFF' and [date]> t.[date]) as Out
FROM @temp t
WHERE t.status='LOGIN'
)t
GROUP BY t.name

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2008-10-27 : 10:16:17

Visakh16: great! :) i was in the middle of this code; virtually the same, when i saw your answer :)

minor adjustment


,SUM(DATEDIFF(ss,t1.[date],t2.[date])) AS timediff

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-27 : 10:40:51

quote:
Originally posted by slimt_slimt

Visakh16: great! :) i was in the middle of this code; virtually the same, when i saw your answer :)

minor adjustment

,SUM(DATEDIFF(ss,t1.[date],t2.[date])) AS timediff

no worries
cheers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources