0

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.

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

3
  • 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?
    – Jen
    Apr 29 at 13:33
  • @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.
    – Paul White
    Apr 29 at 13:34
  • 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.
    – matigo
    Apr 29 at 13:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.