[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

How to show only the last employee that updated the record

Posted on 2016-09-06
7
Medium Priority
?
90 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
  • 4
6 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
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
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

Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
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
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Suggested Courses

591 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