SQL Server 2000: need to return record ID from a previous record in current query

Question

I work on a help-desk and am doing some analysis of PC repair tickets.

I am needing to dump data from our call log system that returns history of tickets for issues on computers where they were recently repaired by another team. We are simply trying to improve QA on deployed machines and this data will help.

I have the query for the analysis of tickets, but I am wanting to return the ticket number of the last PC repair case.

My current query is as follows:

SELECT 
    CallLog.CallID, 
    CallLog.CustID, 
    Subset.Rep_num, 
    Subset.FirstName, 
    Subset.LastName, 
    CallLog.OpndetailCat,
    CallLog.Tracker_Full, 
    CallLog.RecvdDate,
FROM 
    heatPrd.dbo.CallLog CallLog, 
    heatPrd.dbo.Subset Subset
WHERE 
    CallLog.CallID = Subset.CallID AND 
    CallLog.RecvdDate>='2015-10-01' AND 
    CallLog.OpnAreaCat='back from repair'
ORDER BY 
    CallLog.CallID DESC

This returns

CallID  CustID  Rep_num FirstName   LastName    OpndetailCat        Tracker_Full
2182375 1234            Sarah       Doe         Missing Email Folde 
2181831 1235            JENNIFER    Doe         ZOTHER  
2180815 1236    123     Jason       Smith       ZOTHER  
2180790 1237    124     DARCY       Doe         Wrong Proxy Config  
2180787 1239    125     Jason       Smith       ZOTHER  

I want to add a column to the query that would return something to the effect of

select max(callid) 
from calllog 
where calltype = 'in_for_service_pc' and custid = '1234'

where calltype = 'in_for_service_pc' resides on the CallLog table and custID would pull from the query result.

This is a lot of info so i hope my request is clear.

Disclaimer: Data resides in SQL Server 2000 so some of the newer commands may not work.

Answer 1

Something like this should be pretty close.

SELECT 
    cl.CallID, 
    cl.CustID, 
    s.Rep_num, 
    s.FirstName, 
    s.LastName, 
    cl.OpndetailCat,
    cl.Tracker_Full, 
    cl.RecvdDate,
    x.MaxCallID
FROM heatPrd.dbo.CallLog cl 
JOIN heatPrd.dbo.Subset s ON cl.CallID = s.CallID
left join 
(
    select max(cl2.callid) as MaxCallID
        , cl2.custid 
    from calllog cl2
    where cl2.calltype = 'in_for_service_pc' 
    group by cl2.custid
) x on x.custid = cl.custid
WHERE cl.RecvdDate >= '2015-10-01' AND 
    cl.OpnAreaCat = 'back from repair'
ORDER BY cl.CallID DESC