How to show only the last employee that updated the record

I want to show only the last "EMPLOYEE" updated in the record. In other words, the current employee assigned to a unit. Any help will be appreciated.

SELECT     TOP (100) PERCENT dbo.WOMAST01B.step, dbo.WOMAST01B.wono AS WO, dbo.WOMAST01B.item AS PN, dbo.WOMAST01B.location, 
                      dbo.WOMAST01B.dept AS SHOP, dbo.WOLABO01B.prempl AS EMPLOYEE, SUM(dbo.WOLABO01B.time) AS TIME, DATEDIFF(dd, 
                      dbo.WOMAST01B.recdate, GETDATE()) AS [DAYS LEFT], dbo.WOMAST01B.tat
FROM         dbo.WOMAST01B LEFT OUTER JOIN
                      dbo.WOLABO01B ON dbo.WOMAST01B.wono = dbo.WOLABO01B.wono
WHERE     (dbo.WOMAST01B.dept = 'AVI')
GROUP BY dbo.WOMAST01B.step, dbo.WOMAST01B.wono, dbo.WOMAST01B.item, dbo.WOMAST01B.location, dbo.WOMAST01B.dept, 
                      dbo.WOMAST01B.location2, dbo.WOLABO01B.prempl, DATEDIFF(dd, dbo.WOMAST01B.recdate, GETDATE()), dbo.WOMAST01B.tat
ORDER BY [DAYS LEFT] DESC

Open in new window

Book1.xlsx
maximus1974Asked:
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.

zephyr_hex (Megan)DeveloperCommented:
And how is "current" defined?  In other words, how does one identify the employee that's currently assigned to a unit?
1
PortletPaulEE Topic AdvisorCommented:
You are grouping by employee, so you get a row for each employee.

To get just "the latest" requires knowing what you mean and what columns in what tables we would use to determine latest.
0
maximus1974Author Commented:
yes, my apologies. The last employee will be based on the "DATE field. I want to bring in the latest employee to update the record based on the last date stamp. screenshot attached.

SELECT     TOP (100) PERCENT dbo.WOMAST01B.step, dbo.WOMAST01B.wono AS WO, dbo.WOMAST01B.item AS PN, dbo.WOMAST01B.location, 
                      dbo.WOMAST01B.dept AS SHOP, dbo.WOLABO01B.prempl AS EMPLOYEE, SUM(dbo.WOLABO01B.time) AS TIME, DATEDIFF(dd, 
                      dbo.WOMAST01B.recdate, GETDATE()) AS [DAYS LEFT], dbo.WOMAST01B.tat, [b]dbo.WOLABO01B.date[/b]
FROM         dbo.WOMAST01B LEFT OUTER JOIN
                      dbo.WOLABO01B ON dbo.WOMAST01B.wono = dbo.WOLABO01B.wono
WHERE     (dbo.WOMAST01B.dept = 'AVI')
GROUP BY dbo.WOMAST01B.step, dbo.WOMAST01B.wono, dbo.WOMAST01B.item, dbo.WOMAST01B.location, dbo.WOMAST01B.dept, 
                      dbo.WOMAST01B.location2, dbo.WOLABO01B.prempl, DATEDIFF(dd, dbo.WOMAST01B.recdate, GETDATE()), dbo.WOMAST01B.tat, 
                      dbo.WOLABO01B.date
ORDER BY [DAYS LEFT] DESC

Open in new window

screenshot
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Get the Guide
PortletPaulEE Topic AdvisorCommented:
It takes time for a response. I live in the timezone GMT +10 and am reading this whilst having breakfast.
1
PortletPaulEE Topic AdvisorCommented:
Does this produce what you are looking for? 
SELECT
      *
FROM (
      SELECT
            dbo.WOMAST01B.step
          , dbo.WOMAST01B.wono                             AS WO
          , dbo.WOMAST01B.item                             AS PN
          , dbo.WOMAST01B.location
          , dbo.WOMAST01B.dept                             AS SHOP
          , dbo.WOLABO01B.prempl                           AS EMPLOYEE
          , dbo.WOLABO01B.time                             AS TIME
          , DATEDIFF(dd, dbo.WOMAST01B.recdate, GETDATE()) AS [DAYS LEFT]
          , dbo.WOMAST01B.tat
          , ROW_NUMBER() OVER (PARTITION BY
                  dbo.WOMAST01B.step
                , dbo.WOMAST01B.wono
                , dbo.WOMAST01B.item
                , dbo.WOMAST01B.location
                , dbo.WOMAST01B.dept
                , dbo.WOMAST01B.location2
                , dbo.WOLABO01B.prempl
            ORDER BY dbo.WOLABO01B.[date] DESC)            AS RN
      FROM dbo.WOMAST01B
            INNER JOIN dbo.WOLABO01B ON dbo.WOMAST01B.wono = dbo.WOLABO01B.wono
      WHERE (dbo.WOMAST01B.dept = 'AVI')
  ) AS D
WHERE RN = 1

Open in new window

Using row_number() over() is usually a good way to get the "first" or "latest" of something.

Note also; you also don't need a left join if you are only wanting rows with latest employee data.
0

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
PortletPaulEE Topic AdvisorCommented:
Thanks!
0
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
Query Syntax

From novice to tech pro — start learning today.