troubleshooting Question

Last Row Within A Row

Avatar of mike1142
mike1142 asked on
Microsoft SQL Server 2008SQL
19 Comments4 Solutions412 ViewsLast Modified:
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

Table 3
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.

Join our community to see this answer!
Unlock 4 Answers and 19 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 4 Answers and 19 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros