Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

How to show only the last employee that updated the record

Posted on 2016-09-06
7
Medium Priority
?
85 Views
Last Modified: 2016-09-12
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
0
Comment
Question by:maximus1974
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
7 Comments
 
LVL 44

Expert Comment

by:zephyr_hex (Megan)
ID: 41786913
And how is "current" defined?  In other words, how does one identify the employee that's currently assigned to a unit?
1
 
LVL 49

Expert Comment

by:PortletPaul
ID: 41787010
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
 

Author Comment

by:maximus1974
ID: 41787896
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 49

Expert Comment

by:PortletPaul
ID: 41788711
It takes time for a response. I live in the timezone GMT +10 and am reading this whilst having breakfast.
1
 
LVL 49

Accepted Solution

by:
PortletPaul earned 2000 total points
ID: 41788729
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
 
LVL 49

Expert Comment

by:PortletPaul
ID: 41795127
Thanks!
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question