How to return the most recent unfinished transaction or the last completed transaction?

[PPettit]

job  | trans_num | oper_num | start_time | end_time | emp_num
-------------------------------------------------------------
job1 | trans1    | 10       | 7:00am     | 8:00am   | Emp1        
job1 | trans2    | 20       | 8:00am     | null     | Emp1   
job1 | trans3    | 30       | 8:30am     | 9:00am   | Emp2

I'm a bit of a noob with SQL so I'm having some trouble wrapping my brain around this. I need to return the transaction number of the row with the most recent transaction number for a particular job where the job is either in progress (more important) or has no transactions that are unfinished (less important). In this case, my query should return the row containing "trans2" since Emp1 is still working on the job even though Emp2 was the last to clock in and out on the job.

Anyone have an idea about how to code this?

 

[gmmastros]

What is the data type of the start_time and end_time columns?

If it's DateTime, you could try this:

Select	TOP 1 *
From    YourTableNameHere
Order BY Coalesce(end_time, '25000101') DESC

If it's varchar, you could try this:

Select	TOP 1 *
From    YourTableNameHere
Order BY Coalesce(end_time, 'zzzzzz') DESC

If you need to query multiple jobs at the same time:

; With Data As
(
  Select  *,
          Row_Number() Over(Partition By Job Order By Coalesce(end_time, '25000101') DESC) as row_id
  From    YourTableNameHere
)
Select * From Data Where row_id = 1

If you have any questions about this, please let me know.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[TamarGranor]

I think this will give you what you want, but it's untested. I assumed that for jobs where all transactions are complete, you want to base most recent on the end time, not the start time. Also, of course, substitute the actual name of your table.

SELECT YourTable.job, trans_num
FROM YourTable
JOIN (SELECT max(start_time) AS MaxTime
FROM YourTable YT2
WHERE end_time is null
AND YT2.Job = YourTable.Job
UNION
SELECT max(end_time) AS MaxTime
FROM YourTable YT3
WHERE YT3.Job = YourTable.Job
AND NOT EXISTS (SELECT End_Time FROM YourTable YT4 WHERE End_Time is null)) MaxTimeByJob
ON YourTable.Start_Time = MaxTimeByJob.MaxTime

Tamar