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

[dinzana]

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

 

[SgtJarrow]

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

 

[simian336]

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

 

[dinzana]

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.

 

[SgtJarrow]

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

 

[SgtJarrow]

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.

--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

 

[dinzana]

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

 

[dinzana]

Perfect! This works swimmingly well!!!!!

 

[gmmastros]

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

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

 

[SgtJarrow]

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