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?
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
 
PortletPaulfreelancerCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
PortletPaulfreelancerCommented:
It takes time for a response. I live in the timezone GMT +10 and am reading this whilst having breakfast.
1
 
PortletPaulfreelancerCommented:
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 ConnectWise

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
 
PortletPaulfreelancerCommented:
Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.