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

All we need is an easy explanation of the problem, so here it is.

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.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

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

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

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.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply