Sql-server – need the rows with a unique value on certain columns but date column doesn’t help

distinctselectsql serversql-server-2012

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.

Best Answer

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.

Related Question