three latest calls - 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)

three latest calls

Author

Topic

supe
Starting Member

4 Posts

Posted - 2007-12-17 : 21:17:19

I have a table of outboundcalls, I need a query to return the ID's of the last three calls for each OutboundNoID on the same row, there could be more or less then six.

The closest I've gotton is:

select obc1.ID,obc2.ID, obc3.ID
from Outboundcalls obc1 left outer join Outboundcalls obc2 on
obc1.OutboundNoID = obc2.OutboundNoID and obc1.endtime > obc2.endtime
left outer join Outboundcalls obc3 on
obc2.OutboundNoID = obc3.OutboundNoID and obc2.endtime > obc3.endtime
order by obc1.ID desc

which returns:

6574 4810 NULL
6574 5398 4810
6573 5397 4807
6573 4807 NULL
6572 4796 NULL
6572 5393 4796
6571 4465 2812
6571 4465 3639

The first and second rows show the problem I'm having. The second row is the only result I want for ID 6574 although the first row does satisfy the query, since 4810 is in fact before 6574.

I've searched up down, far and close, PLEASE HELP!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-18 : 00:04:09

Try this:-
;
With Call_CTE (Seq,ID,OutboundNoID,EndTime) AS
(
SELECT ROW_NUMBER() OVER(PARTITION BY OutboundNoID ORDER BY EndTime DESC) AS 'Seq',
ID,
OutboundNo,
EndTime
FROM {your table}
)

SELECT c1.ID,c2.ID,c3.ID FROM Call_CTE c1
CROSS APPLY (SELECT * FROM Call_CTE WHERE OutboundNoID =c1.OutboundNoID AND Seq=2 ) c2
CROSS APPLY (SELECT * FROM Call_CTE WHERE OutboundNoID =c1.OutboundNoID AND Seq=3 ) c3
WHERE c1.Seq=1

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-18 : 03:21:37

Or this perhaps?

SELECT	ID,
	OutboundNo,
	EndTime
FROM	(
		SELECT	ROW_NUMBER() OVER (PARTITION BY OutboundNoID ORDER BY EndTime DESC) AS RecID,
			ID,
			OutboundNo,
			EndTime
		FROM	{your tablename here}
	) AS d
WHERE	RecID <= 3

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources