query update help - 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

SQL Server 2005 Forums

Transact-SQL (2005)

query update help

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2008-07-06 : 08:32:20

i have a table with

id, lastname, datetime, place

now i have a blank field called lapsed time

all records should have 2 entries based on lastname

how can i generate the lapsed time between the two entries and update this field?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-06 : 23:33:05

Your problem is not clear. Please show us sample data of what you mean.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-07 : 00:00:58

something like this:-

UPDATE t
SET t.lapsed_time=DATEDIFF(ss,b.PrevTime,t.datetime)
FROM YourTable t
OUTER APPLY(SELECT TOP 1 datetime as PrevTime
            FROM YourTable 
            WHERE lastname=t.lastname
            AND id<t.id
            ORDER BY datetime DESC)b

If this is not giving desired result, please give some sample data of what you're looking at

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources