INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Hello, I am trying to check the last 5 values for each employee and hold a matching value
2

Hello, I am trying to check the last 5 values for each employee and hold a matching value

Hello, I am trying to check the last 5 values for each employee and hold a matching value

(OP)
Hello,

I am trying to check the last 5 values of a column for each employee and if they match then i want to show that value, if they don't match, i want to show the most recent value.





thanks in advance!

D

RE: Hello, I am trying to check the last 5 values for each employee and hold a matching value

So I go to work on a solution to assist you in this and either I am missing something or your requirements are overly complicated.

If the last 5 records Hours column for a particular employee match, the most recent Hours record is the value you want.
If the last 5 records Hours column for a particular employee DO NOT match, the most recent Hours record is the value you want.

Isn't that the same no matter what??? Just grab the last record for each employee and use the Hours value from there.

If my understanding is wrong, please provide some more details.

Robert "Wizard" Johnson III
U.S. Military Vets MC
CSM, CSPO, MCPD, CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
Data Integration Engineer

RE: Hello, I am trying to check the last 5 values for each employee and hold a matching value

That is true unless he chose 15 for 345 for some other reason then it being the last record. Like it being the lowest value. Then you could just grab the min value.

Simi

RE: Hello, I am trying to check the last 5 values for each employee and hold a matching value

(OP)
Ah yes, I see what you mean, and sorry for that. I guess I should say I'd like to have a null when the last 5 values do not match.

RE: Hello, I am trying to check the last 5 values for each employee and hold a matching value

OK. Now the requirement makes sense logically so that we can come up with a solution. One more follow-up...what version of SQL are you using? It can make a difference in the solution.

Robert "Wizard" Johnson III
U.S. Military Vets MC
CSM, CSPO, MCPD, CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
Data Integration Engineer

RE: Hello, I am trying to check the last 5 values for each employee and hold a matching value

While waiting for a version response, here is one possible solution for SQL 2012 or newer. I've added some comments, but please let me know if you need further explanation.

CODE

--Create a table variable to hold our test data.
DECLARE @Times TABLE
(
	EmployeeId		INT,
	WeekNumber		INT,
	HoursWorked		INT
);

--Populate the table variable with the test data we are working with
INSERT INTO	@Times (EmployeeId, WeekNumber, HoursWorked)
VALUES
(123, 1, 10), (123, 2, 10), (123, 3, 20), (123, 4, 40), (123, 5, 40), (123, 6, 40),
(123, 7, 40), (123, 8, 40), (234, 1, 20), (234, 2, 20), (234, 3, 25), (234, 4, 25),
(234, 5, 25), (234, 6, 25), (234, 7, 25), (234, 8, 25), (345, 1, 10), (345, 2, 30),
(345, 3, 15), (345, 4, 30), (345, 5, 40), (345, 6, 20), (345, 7, 25), (345, 8, 15);

--Read the table variable to ensure our raw data is accurate for our test cases
--SELECT
--	ti.EmployeeId,
--    ti.WeekNumber,
--    ti.HoursWorked
--FROM @Times ti;

--Create a table variable to hold the most recent 5 weeks for each employee.
--We will use this for further calcualtions/comparisons to get the final results
--If your employee count is large, this may be better served with a temp table
DECLARE @MostRecent5Weeks TABLE
(
	EmployeeId					INT,
	WeekNumber					INT,
	HoursWorked					INT
);

--Put the most recent five weeks for each employee into our table variable for use
--Order the records by the employee id and the week number in descending order so we get the newest weeks first
--Only keep the most recent 5 weeks for each employee
WITH cteMostRecentWeeks AS
(
	SELECT
		ti.EmployeeId,
		ti.WeekNumber,
		ti.HoursWorked,
		RANK() OVER(PARTITION BY ti.EmployeeId ORDER BY ti.WeekNumber DESC) 'SortOrder'
	FROM @Times ti
)
INSERT INTO @MostRecent5Weeks
(
    EmployeeId,
    WeekNumber,
    HoursWorked
)
SELECT
	mrw.EmployeeId,
    mrw.WeekNumber,
    mrw.HoursWorked
FROM cteMostRecentWeeks mrw
WHERE mrw.SortOrder <= 5;

--Read the table variable to ensure our data is accurate for our test cases
--SELECT
--	mr5w.EmployeeId,
--    mr5w.WeekNumber,
--    mr5w.HoursWorked
--FROM @MostRecent5Weeks mr5w;

--Create a table varibale to hold our comparisons
--SameHoursAsPreviousWeek typed as TINYINT to be able to SUM it later
DECLARE @Comparisons TABLE
(
	EmployeeId					INT,
	WeekNumber					INT,
	HoursWorked					INT,
	SameHoursAsPreviousWeek		TINYINT
);

--Populate the Comparisons table
--Uses LAG function to compare the values between the two records
INSERT INTO @Comparisons
(
    EmployeeId,
    WeekNumber,
    HoursWorked,
    SameHoursAsPreviousWeek
)
SELECT
	mr5w.EmployeeId,
	mr5w.WeekNumber,
	mr5w.HoursWorked,
	CASE
		WHEN mr5w.HoursWorked = LAG(HoursWorked, 1, mr5w.HoursWorked) OVER (PARTITION BY mr5w.EmployeeId ORDER BY mr5w.WeekNumber) THEN 1
		ELSE 0
	END 'SameHoursAsPreviousWeek'
FROM @MostRecent5Weeks mr5w;

--Read the table variable to ensure our data is accurate for our test cases
--SELECT
--	co.EmployeeId,
--    co.WeekNumber,
--    co.HoursWorked,
--    co.SameHoursAsPreviousWeek
--FROM @Comparisons co;

--Our final query that gives us the expected results
--If the SUM of the SameHoursAsPreviousWeek = 5, the the last 5 weeks were the same
--If the SUM is less than 5, the last 5 weeks do not match and return a NULL
WITH cteSummaries AS
(
	SELECT
		co.EmployeeId,
		MAX(co.WeekNumber) 'MostRecentWeek',
		SUM(co.SameHoursAsPreviousWeek) 'NumberOfMatchingWeeks'
	FROM @Comparisons co
	GROUP BY
		co.EmployeeId
)
SELECT
	co.EmployeeId,
	CASE
		WHEN su.NumberOfMatchingWeeks = 5 THEN co.HoursWorked
		ELSE NULL
	END 'FinalHoursWorkedResult'
FROM @Comparisons co
INNER JOIN cteSummaries su
	ON co.EmployeeId = su.EmployeeId
		AND co.WeekNumber = su.MostRecentWeek; 

Robert "Wizard" Johnson III
U.S. Military Vets MC
CSM, CSPO, MCPD, CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
Data Integration Engineer

RE: Hello, I am trying to check the last 5 values for each employee and hold a matching value

(OP)
We are using SQL Server 2012, thanks, I will take a look at this

RE: Hello, I am trying to check the last 5 values for each employee and hold a matching value

(OP)
Perfect! This works swimmingly well!!!!!

RE: Hello, I am trying to check the last 5 values for each employee and hold a matching value

Here's another version. This one doesn't require extra table variable (or temp tables), so it's all in 1 query.

CODE

SET NOCOUNT ON;
Declare @Temp Table(Employee Int, Week Int, Hours Int);

Insert Into @Temp Values(123,1,10)
Insert Into @Temp Values(123,2,10)
Insert Into @Temp Values(123,3,20)
Insert Into @Temp Values(123,4,40)
Insert Into @Temp Values(123,5,40)
Insert Into @Temp Values(123,6,40)
Insert Into @Temp Values(123,7,40)
Insert Into @Temp Values(123,8,40)

Insert Into @Temp Values(234,1,20)
Insert Into @Temp Values(234,2,20)
Insert Into @Temp Values(234,3,25)
Insert Into @Temp Values(234,4,25)
Insert Into @Temp Values(234,5,25)
Insert Into @Temp Values(234,6,25)
Insert Into @Temp Values(234,7,25)
Insert Into @Temp Values(234,8,25)

Insert Into @Temp Values(345,1,10)
Insert Into @Temp Values(345,2,30)
Insert Into @Temp Values(345,3,15)
Insert Into @Temp Values(345,4,30)
Insert Into @Temp Values(345,5,40)
Insert Into @Temp Values(345,6,20)
Insert Into @Temp Values(345,7,23)
Insert Into @Temp Values(345,8,15)

; With Data As
(
  Select  *, 
          Row_Number() Over (Partition By Employee Order BY Week DESC) As RowId
  From    @Temp
), DistinctHours As
(
  Select  Employee, 
          Count(Distinct Hours) As DistinctHours
  From    Data
  Where   RowId <= 5
  Group By Employee
  Having  Count(Distinct Hours) = 1
)
Select  Data.Employee,
        Case When DistinctHours.DistinctHours = 1 Then Data.Hours End As Hours
From    Data
        Left Join DistinctHours
          On Data.Employee = DistinctHours.Employee
Where   RowId = 1 

The first part of the query just allows us to get the 5 "most recent weeks". The second part performs a distinct count on hours. If the distinct count is 1, then they are all the same. We then join back to the original query to get the most recent Hours for the final display.

I should also mention that this works with SQL2005 or newer.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

RE: Hello, I am trying to check the last 5 values for each employee and hold a matching value

Thank you George. I wanted a single-query solution and couldn't "see" it. The concept of the DISTINCT Hours was my missing link. I like your solution.

Robert "Wizard" Johnson III
U.S. Military Vets MC
CSM, CSPO, MCPD, CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
Data Integration Engineer

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close