Select MAX and MAX minus 1

[thorny00]

I have the following query, but I need to change the bathcid's each week. Is there a way to select the MAX batchid and the MAX batchid minus 1?

select distinct pin from IN_Carolina_834Elig
where convert (datetime,dob) = convert(datetime, '<<bthdat>>') and SSN = '<<Calc_MemberID>>'
and batchid in (8024, 8028)

Thanks in advance for your time and help, it is greatly appreciated.

 

[bborissov]

select distinct pin
        from IN_Carolina_834Elig
where convert (datetime,dob) = convert(datetime, '<<bthdat>>')
and SSN = '<<Calc_MemberID>>'
and batchid > (SELECT MAX(BatchId) FROM ...)-2

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.