Find Last Assigned Worker

I have a work order worker history file.  Each time the assigned worker/vendor is changed a new record is generated.  I need to find the last worker assigned.  I have an employee table that just lists employees and a vendor table for vendors.

I know if I use

SELECT woHist.wo_id, woHist.worker_id, emp.em_name, woHist.date_assigned  
FROM woHist INNER JOIN emp on woHist.worker_ID = emp.em_id

I will get work orders and the list of assigned workers (no vendors)

How do I modify that to get the last (latest date assigned)

mlmcc
LVL 102
mlmccAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dsackerContract ERP Admin/ConsultantCommented:
I'm assuming you wouldn't need the woHist.wo_id in your query. Try this:

SELECT woHist.worker_id, emp.em_name, MAX(woHist.date_assigned) AS LatestDateAssigned
FROM woHist INNER JOIN emp on woHist.worker_ID = emp.em_id
GROUP BY  woHist.worker_id, emp.em_name

Open in new window

dsackerContract ERP Admin/ConsultantCommented:
If you want the last date to include the woHist.wo_id, simply add it back in your SELECT and at the beginning of the GROUP BY.

SELECT woHist.wo_id, woHist.worker_id, emp.em_name, MAX(woHist.date_assigned) AS LatestDateAssigned
FROM woHist INNER JOIN emp on woHist.worker_ID = emp.em_id
GROUP BY  woHist.wo_id, woHist.worker_id, emp.em_name

Open in new window

Brian CroweDatabase AdministratorCommented:
I'm a little confused.  You mention 3 tables WorkHistory, Vendor, and Employee but you don't seem to utilize the Vendor table.  It appears as if the WorkHistory table is a mana-to-many table between Vendor and Employee.  Is that correct?

"I will get work orders and the list of assigned workers (no vendors)"

What is a "work order"?
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Brian CroweDatabase AdministratorCommented:
You didn't provide much in the way of schema so I had to fill in the gaps as I understand them.

WITH cteWorkHistory AS
(
	SELECT WH.wo_id, WH.worker_id, WH.date_assigned, --Is there a vendor reference in here?
		ROW_NUMBER() OVER(PARTITION BY vendor_id ORDER BY WH.date_assigned DESC) AS RowNumber
	FROM woHist AS WH
)
SELECT V.vendor_id, V.vendor_name, E.em_id, E.em_name
FROM Vendor AS V
INNER JOIN cteWorkHistory AS WH
	ON V.Vendor_id = WH.vendor_id
	AND WH.RowNumber = 1		-- Just get the latest worker assigned
INNER JOIN Employee AS E
	ON WH.worker_id = E.em_id

Open in new window

mlmccAuthor Commented:
That will get me the last time each worker was assigned.

I think I need a subselect to get the last time a worker was assigned t hen select based on that just having trouble with the subselect.

This gets the last assigned

SELECT wohist.wo_id, Max(woHist.dateAssigned) as lastassigned FROM woHist INNER JOIN em ON woHist.worker_id = = emp.em_id

I think if I just join that as a subselect to the query on the wo_id and the dates, I will get what I want

mlmcc
mlmccAuthor Commented:
Vendor and Employee are tables of just that vendors and employees.  A Work Order can be assigned to a vendor or an employee.

I only want to see the last employee assigned because we use them as the project lead.   An employee will (should) always be assigned before a vendor is assigned.  We don't want to see the vendor as the project lead.

There is NO relationship between the employee and vendor tables
We have a single field in the Work Order History that points to either the vendor or the employee table.

Schema that is important
Work Order History
WO_ID
Worker_ID
Date_Assigned

Employee
em_id

The vendor table does really come into play in the answer.

mlmcc
mlmccAuthor Commented:
This seems to work

SELECT woHist.wo_id, woHist.worker_id, woHist.date_assigned
FROM woHiost INNER JOIN emp ON woHist.worker_id = emp.em_id  
    INNER JOIN
    (SELECT woHist1.wo_id, Max(woHist1.date_assigned) as last_assigned
          FROM woHist woHist1 INNER JOIN emp emp1 ON woHist1.worker_id = emp1.em_id  
              GROUP BY woHist1.wo_id) ProjMgr
   ON woHist.wo_id = ProjMgr.wo_id AND woHist.date_assigned = ProjMgr.last_assigned

mlmcc

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dsackerContract ERP Admin/ConsultantCommented:
Subselect is probably what you need. Join that select to your main tables, so that you're only dealing that that one record. Then you can work with whatever data you need for that one record.

I posted a  minute after you, but yep, that's the ticket.
mlmccAuthor Commented:
dsacker - You got me thinking about it the correct way.  I was trying to put the subselect in the where clause and having no success.  No sure what in you answer made me see it right but it did.

brian crowe - sorry for the confusion.

mlmcc
PortletPaulEE Topic AdvisorCommented:
ROW_NUMBER() is my "go to approach" for "first" or "last", it is very efficient, and you can use ROW_NUMBER() without using a CTE.

The advantage of this approach is you get to pull the complete row with fewer joins and passes of the data.
SELECT
      wo_id, worker_id, em_name, date_assigned
FROM (
      SELECT
            woHist.wo_id, woHist.worker_id, emp.em_name, woHist.date_assigned
          , ROW_NUMBER() OVER (ORDER BY woHist.date_assigned DESC) AS rn
      FROM woHist
      INNER JOIN emp
            ON woHist.worker_ID = emp.em_id
    ) w
WHERE w.rn = 1
;

Open in new window


+edit:
in line: DESCending order is for "latest"
and ASCending order would produce "first" (or earliest)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.