A problem of getting the last immediate record

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)

A problem of getting the last immediate record

Author

Topic

geossl
Yak Posting Veteran

85 Posts

Posted - 2003-11-03 : 21:56:06

Dear All,
There is a table holding the record of sending or receiving items which schema is:

TrackID(Primary key)
State (S - for send, R - for Receive)
Date (DateTime of the action)
DeptID (Sending/Receiving department)
Title

A report is needed to print all the items received and the departmentID that delivers the item, i.e., getting the last record with State = 'S'

Date Delivering Dept Title
---------- --------------- ----------------
2003/1/1 Dept A An Item
2003/1/2 Dept C Vase
2003/1/8 Dept B Document

I have made the following query:
SELECT Tracking.SysDate, LastTrack.DeptID, Tracking.Title
FROM Tracking
LEFT JOIN Tracking LastTrack
ON Tracking.DocumentID = LastTrack.DocumentID
WHERE TrackRecord.TrackState = 'R'
AND LastTrack.TrackRecordID =
(
SELECT TOP 1 t.TrackRecordID
FROM TrackRecord t
WHERE t.SysDate < Tracking.SysDate
AND t.DocumentID = Tracking.DocumentID
AND t.TrackState = 'S'
ORDER BY t.SysDate DESC
)
ORDER BY Tracking.SysDate

It works but for those receiving record with the associate send record cannot be printed, i.e.,

TrackID State Date Dept Title
------- ----- -------- ------ ---------------------------
1 R 2002/1/1 Dept R Receive without last send
2 R 2002/1/2 Dept R Receive something which d

these two records cannot be printed

Is there any mistake with the left join?

Thanks.

nr
SQLTeam MVY

12543 Posts

Posted - 2003-11-03 : 22:20:21

The where clause is actioned on the result of the outer join so you will lose the records due to AND LastTrack.TrackRecordID =
(
...
LastTrack.TrackRecordID being null so this can never be true

Two ways out of this
SELECT Tracking.SysDate, LastTrack.DeptID, Tracking.Title
FROM Tracking
LEFT JOIN Tracking LastTrack
ON Tracking.DocumentID = LastTrack.DocumentID
WHERE TrackRecord.TrackState = 'R'
AND
(LastTrack.TrackRecordID is null or
LastTrack.TrackRecordID =
(
SELECT TOP 1 t.TrackRecordID
FROM TrackRecord t
WHERE t.SysDate < Tracking.SysDate
AND t.DocumentID = Tracking.DocumentID
AND t.TrackState = 'S'
ORDER BY t.SysDate DESC
))
ORDER BY Tracking.SysDate

I prefer

SELECT Tracking.SysDate, LastTrack.DeptID, Tracking.Title
FROM Tracking
LEFT JOIN Tracking LastTrack
ON Tracking.DocumentID = LastTrack.DocumentID
AND LastTrack.TrackRecordID =
(
SELECT TOP 1 t.TrackRecordID
FROM TrackRecord t
WHERE t.SysDate < Tracking.SysDate
AND t.DocumentID = Tracking.DocumentID
AND t.TrackState = 'S'
ORDER BY t.SysDate DESC
)
WHERE TrackRecord.TrackState = 'R'
ORDER BY Tracking.SysDate

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources