drop function uf_get_where_clause
go
/*
Given a profile id, get the details from
t_profile_column_values and return
a string 'where clause' 
*/
create function uf_get_where_clause(@id int)
returns varchar(8000)
as
begin
declare 
  @where varchar(8000),
  @c_name varchar(30), 
  @operator varchar(2), 
  @c_value varchar(255)

   declare c_columns cursor 
   for 
   select av.c_name, av.operator, av.c_value
   from t_profile_column_values_archive av, t_profile_archive a
   where
     a.archiveid = av.archiveid and
     a.id = @id
   order by c_name

   open c_columns

   fetch next from c_columns into @c_name, @operator, @c_value

   if @@fetch_status = 0
      select @where = 'where '

   while @@fetch_status = 0
   begin
      select @where = @where + ' '  + @c_name + ' ' + @operator +
         ' ' + @c_value 
      fetch next from c_columns into @c_name, @operator, @c_value
      if @@fetch_status = 0
         select @where = @where + ' and  '
   end

close c_columns
deallocate c_columns

return @where
end
go

drop function uf_lineage
go
/*
given a t_profile_archive_id,
report its ancestry back to generation @generation
*/
create function uf_lineage(@id  int, @generation int)
returns @lineage table
   (generation  int not null,
    id          int not null,
    parent1     int,
    parent2     int,
    score       float(8))
as
begin
   insert into @lineage(generation, id, parent1, parent2, score)
   select generation, @id, parent1, parent2, score
   from t_profile_archive
   where id = @id

   while @@rowcount > 0
   begin
      insert into @lineage(generation, id, parent1, parent2, score)
      select a.generation, a.id, a.parent1, a.parent2, a.score
      from t_profile_archive a, @lineage c 
      where (c.parent1 = a.id or c.parent2 = a.id) and
         a.id not in ( select distinct id from @lineage) and
         a.generation >= @generation
   end
   return
end
go

drop procedure up_lineage_of_best_profile
go

/*
Find the best profile in the current run, 
and report its lineage back 5 generations
*/
create procedure up_lineage_of_best_profile
as
declare  
   @child int,
   @generation_back_to int,
   @id int,
   @score float(8),
   @clause varchar(1000)
begin

select @child = min(a.id)
from t_profile_archive a,
(
/* get the profile with the best score */
select max(score) as score
from t_profile_archive
) best
   where a.score = best.score

select @generation_back_to = generation - 4
from t_profile_archive
where id = @child

select generation, score, count(*) as duplicates,
.dbo.uf_get_where_clause(id) as clause   
from .dbo.uf_lineage(@child, @generation_back_to)
group by generation, score, .dbo.uf_get_where_clause(id)
order by generation desc



end
go
