sql server - getting unique records across time when a value changes SQL -

still using 2008. have following 2 tables , need able pull out individuals when, given date, code d first time. easy d have never been e, can't figure out how include individuals first time d if previous e. have tried using generate list of es , match in others have been unsuccessful in figuring out how tie other query.

table1                                            table2 person  date1  date2  date3  date4  date5  date6  person  firstserv 1         e             e      d      d      d      1         date1 2                e      e                    d      2         date2 3                              d      d      d      3         date4 4                              d      d             4         date4 5         e                    d      d      d      5         date1 6         e      e                    d      d      6         date1 7                e      e      d      d      d      7         date2 8                              d      d      d      8         date4 9                              d      d             9         date4 10                             d      d      d     10         date4 

current code without bringing in clause (and yes know not efficient coder :) )

declare @myorder int set @myorder = (select academicorder dimtermyear dt3 termid='14/fa');  mydual ( select distinct ds.sttr_student, dt.academicorder     dbo.dimstudentterms ds          left join dimtermyear dt on dt.termid=ds.sttr_term      ds.sttr_stu_current_type = 'de' )   select distinct sa.stc_person_id,ds2.sttr_stu_current_type,dt2.academicorder     studentacadcredfrom2000 sa         left join dbo.dimstudentterms ds2 on ds2.sttr_student=sa.stc_person_id         left join dimtermyear dt2 on dt2.termid=ds2.sttr_term      (sa.stc_current_status in ('a','n') or (stc_current_status='d' , stc_grade not null))     , sa.stc_term='14/fa' , ds2.d04_first_term_enr='14/fa'     , ds2.sttr_stu_current_type='ds' , dt2.academicorder=@myorder order sa.stc_person_id 

if know in advance table 1 have 6 date columns (date1 through date6), can use series of cases follows:

with temp (     select person, firstdateofd = (         case when date1 = 'd' date1         case when date2 = 'd' date2         case when date3 = 'd' date3         case when date4 = 'd' date4         case when date5 = 'd' date5         case when date6 = 'd' date6)     table1 )  -- create lists need. -- e.g. date1_list = select person temp firstdateofd = date1