I am trying to extract and compare 2 sets of data from the same table in a
T-SQL (or even SP) but am running into a major head ache. The sample database
layout is as follows:

[GroupSeqNumber] = is a bit column that contains an int value for each batch
load. So one batch of 100 records get loaded they are set to one. Then
another batch of records get loaded to 2 and so forth. This is per location.

[Review] = is a bit column that sets the state of that record in a needs
review state. So there could be 1 or more batches in a review state.

[Processed] = is a bit column that states that the record has been
processed. Also there can be one or more batches that have been processed
here.

[SSN]
[FirstName]
[LastName]
[Location] sting column

What I am trying to do is retrieve all the batches that need review, only
the last batch that has been processed (the last processed batch is the
baseline) which are in the same location and determine if thier first or last
name has changed based on thier SSN.

sample data

1 | 0 | 1 | 2225553333 | John | Smith | NH
2 | 0 | 1 | 2225553333 | John | Smith | NH
3 | 1 | 0 | 2225553333 | Johny | Smith |NH
1 | 0 | 1 | 5556664444 | Andy | Doe | FL
2 | 1 | 0 | 5556664444 | Andy | Dole | FL
4 | 1 | 0 | 2225553333 | John | Apple| NH

-- This retrieves all the validated GroupSeqNumber and Location sets I need
to compare with
select Max( GroupSeqNumber) as MaxNum, Location from SampleTbl where
Processed = 1 group by MaxNum

-- Then retrieve all the review sets, again based on GroupSeqnumber and
Location as sets
select distinct GroupSeqNumber, location from SampleTbl where Review = 1

The problem I am running into is when I try to nest them as a subquery I can
only get errors.

--example
select * from  
        SampleTbl as M1,
        (select Max( S1.GroupSeqNumber) as MaxNum, S1.Location from SampleTbl as S1
where S1.Processed = 1 group by S1.Location) as SO1,  
        (select distinct B1GroupSeqNumber, B1.Location from SampleTbl as B1 where
B1.Review = 1) as BO1
where ..? not sure how I'm going to bind 3 table to compare 2 result sets.

I'm probably trying to solve it the backwards way,
any help TIA

Select  M1.* from SampleTbl as M1
left outer join -- (inner join ??)
     (select Max( S1.GroupSeqNumber) as MaxNum, S1.Location from
SampleTbl as S1
where S1.Processed = 1 group by S1.Location) as SO1
on M1.GroupSeqNumber = SO1.MaxNum
AND M1.Location  = SO1.Location
left outer join
 (select distinct B1.GroupSeqNumber, B1.Location from SampleTbl as B1
where
B1.Review = 1) as BO1
on M1.GroupSeqNumber = BO1.GroupSeqNumber
AND M1.Location  = BO1.Location

-----------------------------------------------Reply-----------------------------------------------