UFN_PROSPECTTEAM_SELECT

CREATE function dbo.UFN_PROSPECTTEAM_SELECT
(
    @PROSPECTID uniqueidentifier,
    @VIEWINACTIVE bit = 0
)  
returns @ProspectTeam TABLE
(
    ID uniqueidentifier NOT NULL,
    MEMBERID uniqueidentifier NOT NULL,
    NAME nvarchar(700) NULL,
    ROLEDESCRIPTION nvarchar(100) NOT NULL,
    DATEFROM datetime NULL,
    DATETO datetime NULL,
    ORGANIZATIONALSITE nvarchar(1024) NOT NULL,
  TIMEFRAME nvarchar(8) NOT NULL
)
with execute as caller
as
begin

-- set the start/end boundaries of DATEFROM/DATETO based on whether we want to include inactive members

declare @now DateTime = GetDate()
--declare @start DateTime = case @viewinactive when 0 then @now else cast('1753-01-01' as datetime) end 

--declare @end DateTime = case @viewinactive when 0 then @now else cast ('2999-12-31' as datetime) end

--set @start = dbo.UFN_DATE_GETEARLIESTTIME(@start)

--set @end = dbo.UFN_DATE_GETLATESTTIME(@end)

declare @minCompareResult int = case @viewinactive when 0 then 0 else 1 end

insert @ProspectTeam
select
    P.ID,
    P.MEMBERID,
    P.NAME,
    P.ROLEDESCRIPTION,
    P.DATEFROM,
    P.DATETO,
    P.SITENAME ORGANIZATIONALSITE,
    case dbo.UFN_DATE_COMPARETODATERANGE(@now, P.DATEFROM, P.DATETO) when -1 then 'Future' when 0 then 'Current' when 1 then 'Prior' else '' end TIMEFRAME
from
    dbo.V_PROSPECTTEAM P
where
    PROSPECTID = @PROSPECTID
    and
    dbo.UFN_DATE_COMPARETODATERANGE(@now, P.DATEFROM, P.DATETO) <= @minCompareResult

return
end