need the rows with a unique value on certain columns but date column doesn't help?
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:

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.