Selecting a previous record - 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)

Selecting a previous record

Author

Topic

mlevier
Starting Member

33 Posts

Posted - 2007-10-02 : 09:52:36

How can I select a previous record in a table?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-10-02 : 09:55:37

What do you mean by previous? In set-based theory, physical location of the records in the table is immaterial.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

mlevier
Starting Member

33 Posts

Posted - 2007-10-02 : 09:57:38

The previous record based on datetime

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-10-02 : 09:58:43

Please post your table structure with sample data and expected output.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

mlevier
Starting Member

33 Posts

Posted - 2007-10-02 : 10:02:03

Table: Calls
Fields:TelephoneNumber, Datetime
I want to find the datediff between the previous call with the same telephone number.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-10-02 : 22:29:24

[code]DECLARE @Calls TABLE
(
TelephoneNumber varchar(20),
CallDate datetime
)

INSERT INTO @Calls
SELECT '11111111', '20071001 12:34' UNION ALL
SELECT '22222222', '20071001 03:50' UNION ALL
SELECT '11111111', '20071002 15:00' UNION ALL
SELECT '22222222', '20071001 12:34' UNION ALL
SELECT '22222222', '20071002 23:04' UNION ALL
SELECT '22222222', '20071003 20:00'

SELECT TelephoneNumber, DATEDIFF(minute, MIN(CallDate), MAX(CallDate))
FROM
(
SELECT TelephoneNumber, CallDate
FROM @Calls c
WHERE CallDate IN (SELECT TOP 2 CallDate FROM @Calls x WHERE x.TelephoneNumber = c.TelephoneNumber)
) a
GROUP BY TelephoneNumber
/*
TelephoneNumber
-------------------- -----------
11111111 1586
22222222 524
*/[/code]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources