UFN_CONSTITUENT_ISADVOCATE

  create function dbo.UFN_CONSTITUENT_ISADVOCATE(@CONSTITUENTID uniqueidentifier)  
  returns bit  
  with execute as caller  
  as begin  
   declare @CURRENTDATE datetime;  
   set @CURRENTDATE = getdate();  

   declare @UPPERBOUND datetime;  
   set @UPPERBOUND = dbo.UFN_DATE_GETLATESTTIME(@CURRENTDATE);  

   declare @LOWERBOUND datetime;  
   set @LOWERBOUND = dbo.UFN_DATE_GETEARLIESTTIME(@CURRENTDATE);  

   if exists(  
    select  
     ID  
    from  
     dbo.ADVOCATERDATERANGE  
    where  
     ADVOCATERDATERANGE.CONSTITUENTID = @CONSTITUENTID and  
     (ADVOCATERDATERANGE.DATEFROM <= @UPPERBOUND or ADVOCATERDATERANGE.DATEFROM is null) and  
     (ADVOCATERDATERANGE.DATETO >= @LOWERBOUND or ADVOCATERDATERANGE.DATETO is null)  
   ) return 1;  

   return 0;  
  end