UFN_PROSPECTPLAN_STATUSASOF
Returns the status of a plan as of a certain date.
Return
| Return Type |
|---|
| uniqueidentifier |
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @PROSPECTPLANID | uniqueidentifier | IN | |
| @ASOF | datetime | IN |
Definition
Copy
create function dbo.UFN_PROSPECTPLAN_STATUSASOF(@PROSPECTPLANID uniqueidentifier, @ASOF datetime)
returns uniqueidentifier
with execute as caller
as begin
declare @STATUS uniqueidentifier;
-- Select the phase of the earliest incomplete step.
select top 1
@STATUS=PROSPECTPLANSTATUSCODEID
from
dbo.INTERACTION
where
PROSPECTPLANID=@PROSPECTPLANID
and (COMPLETED=0 or ACTUALDATE>@ASOF)
order by
coalesce(EXPECTEDDATE,ACTUALDATE);
if @STATUS is null begin
-- Select the phase of the most recent successful step.
select top 1
@STATUS=PROSPECTPLANSTATUSCODEID
from
dbo.INTERACTION
where
PROSPECTPLANID=@PROSPECTPLANID
and COMPLETED=1
and ACTUALDATE<=@ASOF
order by
ACTUALDATE desc;
end;
if @STATUS is null begin
-- Select the phase of the earliest step (it would have to be a failed step).
select top 1
@STATUS=PROSPECTPLANSTATUSCODEID
from
dbo.INTERACTION
where
PROSPECTPLANID=@PROSPECTPLANID
and ACTUALDATE<=@ASOF
order by
ACTUALDATE;
end;
if @STATUS is null begin
-- Select the phase of the first step.
select top 1
@STATUS=PROSPECTPLANSTATUSCODEID
from
dbo.INTERACTION
where
PROSPECTPLANID=@PROSPECTPLANID
order by
coalesce(EXPECTEDDATE,ACTUALDATE);
end;
return @STATUS;
end;