• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 63
  • Last Modified:

How to show the last employee that updated a record

I want to show only the last "EMPLOYEE" that updated in the record. In other words, the last employee date stamp in field dbo.WOLABO01B.date.

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

Capture.PNG
0
maximus1974
Asked:
maximus1974
  • 2
  • 2
1 Solution
 
Russell FoxDatabase DeveloperCommented:
You can use a subquery:
SELECT TOP (100) PERCENT 
	woma.step
	, woma.wono AS WO
	, woma.item AS PN
	, woma.location
	, woma.dept AS SHOP
	, dbo.WOLABO01B.prempl AS EMPLOYEE
	, SUM(dbo.WOLABO01B.time) AS TIME
	, DATEDIFF(dd, woma.recdate, GETDATE()) AS [DAYS LEFT]
	, woma.tat
	, LastDate = (SELECT TOP 1 [date] FROM dbo.WOLABO01B WHERE wono = woma.wono WHERE [date] IS NOT NULL ORDER BY [date] DESC)
FROM dbo.WOMAST01B woma
	LEFT OUTER JOIN dbo.WOLABO01B wola
		ON woma.wono = wola.wono
WHERE woma.dept = 'AVI'
GROUP BY woma.step
	, woma.wono
	, woma.item
	, woma.location
	, woma.dept
	, woma.location2
	, wola.prempl
	, DATEDIFF(dd, woma.recdate, GETDATE())
	, woma.tat
ORDER BY [DAYS LEFT] DESC

Open in new window

0
 
SharathData EngineerCommented:
Can you provide your expected result?
0
 
maximus1974Author Commented:
is this statement correct?

LastDate = (SELECT TOP 1 [date] FROM dbo.WOLABO01B WHERE wono = woma.wono WHERE [date] IS NOT NULL ORDER BY [date] DESC)
FROM dbo.WOMAST01B woma

Getting syntax errors.
0
 
maximus1974Author Commented:
Expected result is no WONO duplicates and one employee which is the last one to clock in.
0
 
SharathData EngineerCommented:
In your SELECT clause, I don't see any DATE column but in your screenshot, you have DATE as the last column after tat. Which column is displaying the DATE in your screenshot.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now