need the rows with a unique value on certain columns but date column doesn't help?

Question

I have a table with multiple records by each employee. I need to find all the records by employee where any of the columns values changes. For example, I have the following data for George:

CREATE TABLE tableName
(
 NAME varchar(300),
 HOME varchar(300),
 CURRENT varchar(300),
 EFFECTIVE_DATE varchar(300),
 NOT_TO_EXCEED_DATE varchar(300),
 FLAG varchar(300),
 GRADE varchar(300),
 POSITION varchar(300),
 LAST_PAY_PERIOD_PAID varchar(255)
);

INSERT INTO tableName ( NAME , HOME , CURRENT , EFFECTIVE_DATE , NOT_TO_EXCEED_DATE , FLAG , GRADE , POSITION , LAST_PAY_PERIOD_PAID )
VALUES
 ('GEORGE', '610422', '610422', 'NULL', 'NULL', 'N', '15', 'SUPERVISOR ACCOUNTANT', '202107'),
 ('GEORGE', '610422', '630100', '11/10/2019', '11/6/2021', 'Y', '15', 'SUPERVISOR ACCOUNTANT', '202105'),
 ('GEORGE', '610422', '610422', 'NULL', 'NULL', 'N', '15', 'SUPERVISOR ACCOUNTANT', '202105'),
 ('GEORGE', '610422', '630100', '11/10/2019', '11/6/2021', 'Y', '15', 'SUPERVISOR ACCOUNTANT', '202103'),
 ('GEORGE', '610422', '630100', '11/10/2019', '11/6/2021', 'Y', '14', ' ACCOUNTANT', '202026'),
 ('GEORGE', '610124', '630100', '11/10/2019', '11/6/2021', 'Y', '14', ' ACCOUNTANT', '202023'),
 ('GEORGE', '610124', '630100', '11/10/2019', '11/6/2021', 'Y', '14', ' ACCOUNTANT', '202024'),
 ('GEORGE', '610422', '630100', '11/10/2019', '11/6/2021', 'Y', '14', ' ACCOUNTANT', '202025'),
 ('GEORGE', '610422', '630100', '11/10/2019', '11/6/2021', 'Y', '14', ' ACCOUNTANT', '202025'),
 ('GEORGE', '610124', '630100', '11/10/2019', '11/6/2021', 'Y', '14', ' ACCOUNTANT', '202023'),
 ('GEORGE', '610124', '630100', '11/10/2019', '11/6/2021', 'Y', '14', ' ACCOUNTANT', '202024'),
 ('GEORGE', '610422', '630100', '11/10/2019', '11/6/2021', 'Y', '15', 'SUPERVISOR ACCOUNTANT', '202104'),
 ('GEORGE', '610422', '610422', 'NULL', 'NULL', 'N', '15', 'SUPERVISOR ACCOUNTANT', '202106'),
 ('GEORGE', '610124', '630100', '11/10/2019', '11/6/2021', 'Y', '14', ' ACCOUNTANT', '202025'),
 ('GEORGE', '610422', '630100', '11/10/2019', '11/6/2021', 'Y', '14', ' ACCOUNTANT', '202101'),
 ('GEORGE', '610422', '610422', 'NULL', 'NULL', 'N', '15', 'SUPERVISOR ACCOUNTANT', '202108'),
 ('GEORGE', '610422', '630100', '11/10/2019', '11/6/2021', 'Y', '14', ' ACCOUNTANT', '202102'),
 ('GEORGE', '610422', '630100', '11/10/2019', '11/6/2021', 'Y', '14', ' ACCOUNTANT', '202102'),
 ('GEORGE', '610422', '630100', '11/10/2019', '11/6/2021', 'Y', '15', 'SUPERVISOR ACCOUNTANT', '202103');

How do I get the following result data set?

NAME,HOME,CURRENT,EFFECTIVEDATE,NOTTOEXCEEDDATE,FLAG,GRADE,POSITION,LASTPAYPERIODPAID
GEORGE,610124,630100,11/10/2019,11/6/2021,Y,14, ACCOUNTANT,202023
GEORGE,610422,630100,11/10/2019,11/6/2021,Y,15,SUPERVISOR ACCOUNTANT,202103
GEORGE,610422,610422,NULL,NULL,N,15,SUPERVISOR ACCOUNTANT,202105

So far I can only get the result data set by doing a Select Distinct without the LAST_PAY_PERIOD_PAID column. Given that I do need to know when has the record changed based on the pay period, as soon as I add this column I get the first table all over again.

When there are different values for two records that are the same otherwise, the most recent LAST_PAY_PERIOD would be ok.

Answer 1

Perhaps I'm misunderstanding the objective but, to take the data that you helpfully provided in the INSERT and determine both the period when the record changed and the most recently paid period, you can do this:

SELECT 
 tn.[name], 
 tn.[home], 
 tn.[current], 
 tn.[effective_date], 
 tn.[not_to_exceed_date], 
 tn.[flag], 
 tn.[grade], 
 TRIM(tn.[position]) as [position],
 MIN(tn.[last_pay_period_paid]) as [first_pay_period_paid], 
 MAX(tn.[last_pay_period_paid]) as [recent_pay_period_paid]
FROM 
 [dbo].[tableName] tn
GROUP BY 
 tn.[name], 
 tn.[home],
 tn.[current],
 tn.[effective_date],
 tn.[not_to_exceed_date],
 tn.[flag],
 tn.[grade],
 TRIM(tn.[position])

This will give you the following output:

Output of the GROUP BY

Note that there are two records for ACCOUNTANT. This is because there are two distinct values in home. Not sure if this is due to a typo in the sample data or if the column is even necessary. Either way ... this may give you the information you seek.

• d3BI: would i also be able to filter upon the newly created last_pay_period_paid and/or recent_pay_period_paid columns? if its possible could you let me know how?
• Paul White: @Jen Rather than leaving "thank you" in a comment, vote on answers and accept the one you found most helpful. See dba.stackexchange.com/help/someone-answers in the help. If you have a new, related, or follow-up question, feel free to ask it separately.
• matigo: To filter on these columns you would need to wrap the query above into a subquery. SELECT * FROM ({above query}) tmp WHERE tmp.[first_pay_period_paid] >= {period}. However, this is not a great way forward for tables with lots of data. If you are going to be doing this for a medium-to-large company, then there are better ways to solve the problem.