Such good luck I will try another.
I have a bunch of People and each person can have 0 to many visits and each visit can have 0 to many tasks.
Table 1 looks like this
Col1 Col 2
personID Name etc.
Table 2 looks like
Col 1 Col 2 Col 3
personID VisitID VisitDate etc
Col 1 Col 2 Col 3 Col 4 Col 5 Col 6
VisitID TaskSeqNum TaskDate TaskTime Value1 Value2
I want the last TaskSeqNum for the last visitID for a set of personID's where value 1 and value 2 are not null (they can still have value 3 and value 4 but I don't care) and the TaskDate of the TaskSeqNum cannot be more than a year from a given date range. Also value 1 and 2 are integers and Value 1 must be less than X AND value 2 must be less then Y. If they don't have a task I don't want them. They will almost assuredly have a visit but if they don't I don't want them.
You cannot make the assumption that any of the keys will be sequential but they should be (personID VisitID TaskSeqNum are varchar)
I need a T-SQL query that will do this. I have seen a potential solution using ROW_NUMBER and RANKING but I don't understand it. I don't want to muddy the waters so I will not post the potential.