Query Syntax
Query Syntax
Microsoft SQL Server 2008
Microsoft SQL Server 2008
Microsoft SQL Server
Microsoft SQL Server

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: 2016-09-06

Who is Participating?

Improve company productivity with a Business Account.Sign Up

Solutions

Advertise Here

LVL 50

PaulConnect With a Mentor Commented: 2016-09-07

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.

LVL 44

zephyr_hex (Megan)DeveloperCommented: 2016-09-06

And how is "current" defined? In other words, how does one identify the employee that's currently assigned to a unit?

LVL 50

PaulCommented: 2016-09-06

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.

maximus1974Author Commented: 2016-09-07

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