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:

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

How do i get the following result data set ?

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

so far i can only get the result data set by doing a Select Distinct without the LAST_PAY_PERIOD_PAID column. as soon as i add this column i get the first table all over again.

any help is greatly appreciated

0
1

I can't see your problem

Distinct produces exactly what you want

SELECT 
DISTINCT
[HOME]
, [CURRENT]
, [EFFECTIVEDATE]
, [NOTTOEXCEEDDATE]
, [FLAG]
, [GRADE]
, [POSITION]
, [LASTPAYPERIODPAID]
FROM Table1

Result

HOME    CURRENT EFFECTIVEDATE   NOTTOEXCEEDDATE     FLAG        GRADE   POSITION    LASTPAYPERIODPAID
GEORGE  610124  630100          11/10/2019          11/6/2021   Y       14          ACCOUNTANT
GEORGE  610422  610422          (null)              (null)      N       15          SUPERVISOR ACCOUNTANT
GEORGE  610422  630100          11/10/2019           11/6/2021  Y       14          ACCOUNTANT
GEORGE  610422  630100          11/10/2019           11/6/2021  Y       15          SUPERVISOR ACCOUNTANT

George has two current numbers, that is why i don't get your result

http://sqlfiddle.com/#!18/f5920/1

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