SQL Query - Pulling Last Value for Certain Records

Ignition
1

Hello all,

I'm struggling with pulling out the last value for certain records. What I'm trying to do is pull out the last "CurrentHours" value for each "EID". I'm running something like this but I'm not sure if the "Last_Value" function is the correct thing for me. The hard part is that an employee may not sign up at the most current date, so I can't just select TOP(X). I need to fetch that most recent date of signup (DateTime) and the CurrentHours associated with it.

SELECT DISTINCT Fname, Lname, EID, TradeSkill, Shift, LAST_VALUE(CurrentHours) OVER (PARTITION BY EID ORDER BY DateTime desc) as CurrentHours, DateTime
FROM [Maintenance].[dbo].[MOS_Signup]
ORDER BY MOS_Signup.DateTime desc ```
2

That looks like MS SQL, so I imagine that you are looking for SELECT TOP 1

1 Like
3

The problem with SELECT TOP 1 is that it will only give me one EID's values, not each EID in the table values.

4

Have you tried using a subquery?

SELECT [Fname], [Lname], [EID], [TradeSkill], [Shift], [CurrentHours], [DateTime]
FROM 	(SELECT [Fname], [Lname], [EID], [TradeSkill], [Shift], [CurrentHours], [DateTime],
		ROW_NUMBER() OVER (PARTITION BY [EID] ORDER BY [DateTime] DESC) AS RowNum
	FROM [Maintenance].[dbo].[MOS_Signup]) AS Subquery
WHERE
	RowNum = 1
ORDER BY
	[DateTime] DESC
1 Like
5

I would normally subquery with group by EID to get the max(DateTime) and join that back to the full table to get the other columns. If you have an index on EID and DateTime, it should be very efficient.

2 Likes
6

I'm now running this query, only running into one problem...When people change EID (Clock Number)..It's still getting old Clock Numbers. Is there a way to cut this out? Another subquery?

SELECT old.Fname, old.Lname, old.EID, old.TradeSkill, old.Shift, old.CurrentHours, old.DateTime
FROM [Maintenance].[dbo].[MOS_Signup] old
INNER JOIN (
	SELECT EID, max(DateTime) as MaxDate 
	FROM [Maintenance].[dbo].[MOS_Signup]
	GROUP BY EID
) new ON old.EID = new.EID and old.DateTime = new.MaxDate
WHERE old.TradeSkill = 0 or old.TradeSkill = 1 or old.TradeSkill = 3 or old.TradeSkill = 4
ORDER BY old.Lname asc
7

What's the unique part? Fname and Lname? If so, just group by those in the subquery instead.

8

The EID is the unique part, the only problem is when someone gets a replacement they keep their first, last name and change clock number so now there's a duplicate in a sense.

9

I would say if the same name can have multiple EIDs, then EID is not unique. :man_shrugging:

I can't advise you further. Just choose which is more important to your case.

10

The combination of EID, Fname, and Lname is unique.

Try this:

SELECT old.Fname, old.Lname, old.EID, old.TradeSkill, old.Shift, old.CurrentHours, old.DateTime
FROM [Maintenance].[dbo].[MOS_Signup] old
INNER JOIN (
	SELECT EID,Fname,Lname, max(DateTime) as MaxDate 
	FROM [Maintenance].[dbo].[MOS_Signup]
	GROUP BY EID,Fname,Lname
) new ON old.EID = new.EID and old.DateTime = new.MaxDate
WHERE old.TradeSkill = 0 or old.TradeSkill = 1 or old.TradeSkill = 3 or old.TradeSkill = 4
ORDER BY old.Lname asc