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 2000 Forums

Transact-SQL (2000)

three latest calls

Author

Topic

supe
Starting Member

4 Posts

Posted - 2007-12-18 : 10:04:43

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 : 10:10:59

Cross post:-

[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=94375[/url]

supe
Starting Member

4 Posts

Posted - 2007-12-18 : 10:16:49

Thankyou and sorry for posting twice but this is a sql 2000 db, which doesnt support row_number.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-18 : 10:51:41

Ok do like this in 2000

CREATE TABLE #temp
(
RowNo int IDENTITY(1,1),
ID int,
OutboundNoID int,
RowCount int,
Seq int
)

INSERT INTO #temp(ID,OutboundNoID)
SELECT ID,OutboundNoID
FROM Outboundcalls
ORDER BY OutboundNoID,ID DESC

UPDATE t
SET t.RowCount=tmp.RowCount
FROM #temp t
INNER JOIN
(
SELECT OutboundNoID,COUNT(ID) AS 'RowCount'
FROM Outboundcalls
GROUP BY OutboundNoID
)tmp
ON tmp.OutboundNoID=t.OutboundNoID

UPDATE t
SET t.Seq=t.RowNo-Dec.decr
FROM #temp t
INNER JOIN
(
SELECT t1.OutboundNoID,t1.RowNo,SUM(tmp.RowCount) AS 'decr'
FROM #temp t1
INNER JOIN
(
SELECT OutboundNoID,RowCount AS 'RowCount'
FROM temp
GROUP BY OutboundNoID,RowCount
)tmp
ON tmp.OutboundNoID>t1.OutboundNoID
GROUP BY t1.OutboundNoID,t1.RowNo
)Dec
ON t.OutboundNoID=Dec.OutboundNoID
AND t.RowNo=Dec.RowNo

SELECT t1.ID,t2.ID,t3.ID FROM #temp t1
INNER JOIN #temp t2
ON t1.OutboundNoID =t2.OutboundNoID
AND t2.Seq=2
INNER JOIN #temp t3
ON t1.OutboundNoID =t3.OutboundNoID
AND t3.Seq=3
WHERE t1.Seq=1

supe
Starting Member

4 Posts

Posted - 2007-12-19 : 13:55:27

I can see what you're trying to do, but your seq column in your second update isnt doing what you want it to. The seq column is unique for every row and works more like a row count starting at negative total calls to positive total calls.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-19 : 15:21:55

Some sample data would be great!
We know your expected output.

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-19 : 15:31:01

Or try this

-- Initialize staging area
DECLARE @Stage TABLE (RowID INT IDENTITY(1, 1), OutboundNoID INT, ID INT)

INSERT		@Stage
		(
			OutboundNoID,
			ID
		)
SELECT		OutboundNoID,
		ID
FROM		OutboundCalls
ORDER BY	EndTime DESC

-- Show the expected output
SELECT		q.OutboundNoID,
		MAX(CASE s.RowID - q.MinRowID WHEN 0 THEN s.ID ELSE 0 END),
		MAX(CASE s.RowID - q.MinRowID WHEN 1 THEN s.ID ELSE 0 END),
		MAX(CASE s.RowID - q.MinRowID WHEN 2 THEN s.ID ELSE 0 END)
FROM		(
			SELECT		OutboundNoID,
					MIN(RowID) AS MinRowID,
					MIN(RowID) + 2 AS MaxRowID
			FROM		@Stage
			GROUP BY	OutboundNoID
			HAVING		COUNT(*) >= 3
		) AS q
INNER JOIN	@Stage AS s ON s.OutboundNoID = q.OutboundNoID
WHERE		s.RowID BETWEEN q.MinRowID AND q.MaxRowID
		AND s.RowID - q.MinRowID BETWEEN 0 AND 2

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