I am trying to Capture historical changes in a fact table to be used as a basis for a type 2 SCD.   I would like to see only the changes in of the PCT column by employee id and a continous stream of time >=2013-01-01.  Can someone assist in the t-sql?

SELECT DISTINCT e.EmployeeID, COALESCE(e.Pct,0) Pct, c.FirstDateOfMonth, c.LastDateOfMonth
 FROM  Testdb.dbo.Calendar c
 LEFT OUTER JOIN  Testdb.dbo.T_EMP_PRODUCTIVITY e
		ON c.CalendarDate = CAST(LEFT(e.PostPeriod,4) + '-'+ RIGHT(e.PostPeriod,2)+ '-01' as datetime)
	
WHERE  c.LastDateOfMonth between' 2013-01-01' and getdate()

Employee 8 Below should have .80 from 2013-01-01 through 2014-01-31, then from beginning 2014-02-01 to 2015-02-28 it should be .5, then from 2015-03-01 to 2015-03-31 it should be .23 , then back to .5 after 2015-04-01.

This looks like a "gaps and islands" solution (as an aside, why is it called that, islands in the stream seems a better phrase...)

Apply a row_number() to order your rows, then using the "gaps and islands" approach, identify the changes in value. Once you have this set, get the min(first date) and max(last date) based on these islands

• Proposed as answer by Naomi NModerator 4 hours 1 minutes ago

This looks like a "gaps and islands" solution (as an aside, why is it called that, islands in the stream seems a better phrase...)

Apply a row_number() to order your rows, then using the "gaps and islands" approach, identify the changes in value. Once you have this set, get the min(first date) and max(last date) based on these islands

• Proposed as answer by Naomi NModerator Tuesday, August 25, 2015 3:05 AM

I'm not seeing it :(

Have you looked up "gaps and islands"?

If you're wanting further help, you need to provide suitable DDL and sample data