Everything you need to know about backup and disaster recovery with AWS, for FREE!
Course Of The Month
1 day, 13 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
How to show only the last employee that updated the record
Posted on 2016-09-06
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
[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
Active today
And how is "current" defined? In other words, how does one identify the employee that's currently assigned to a unit?
Active today
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.
To get just "the latest" requires knowing what you mean and what columns in what tables we would use to determine latest.
Active 5 days ago
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
Active today
It takes time for a response. I live in the timezone GMT +10 and am reading this whilst having breakfast.
Active today
Does this produce what you are looking for?
Note also; you also don't need a left join if you are only wanting rows with latest employee data.
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
Note also; you also don't need a left join if you are only wanting rows with latest employee data.
Featured Post
Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses
Course of the Month1 day, 13 hours left to enroll
717 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.