UFN_PHONEFINDER_GETSTEP

CREATE function dbo.UFN_PHONEFINDER_GETSTEP
(
    @ID uniqueidentifier
)
returns nvarchar(150)
as begin

    declare @STEP nvarchar(150) = null;
    select top(1)
        @STEP = PROCESSSTEPS.[STEP]
    from
        (
            select 
                N'Created and submitted on:' as [STEP],
                PHONEFINDERSUBMITPROCESS.[DATELASTRUN] as [DATELASTRUN],
                PHONEFINDERSUBMITPROCESS.[PHONEFINDERID]
            from
                dbo.PHONEFINDERSUBMITPROCESS

            union all

            select 
                N'Received on:' as [STEP],
                PHONEFINDERGETPROCESS.[DATELASTRUN],
                PHONEFINDERGETPROCESS.[PHONEFINDERID]
            from
                dbo.PHONEFINDERGETPROCESS

            union all

            select
                N'Resubmitted on:' as [STEP],
                PHONEFINDERRESUBMITPROCESS.[DATELASTRUN],
                PHONEFINDERRESUBMITPROCESS.[PHONEFINDERID]
            from
                dbo.PHONEFINDERRESUBMITPROCESS

            union all

            select
                N'Committed on:' as [STEP],
                BATCH.[DATECOMMITTED],
                PHONEFINDER.[ID]
            from
                dbo.PHONEFINDER
                inner join dbo.BATCH on BATCH.[ID] = PHONEFINDER.[BATCHID]
            where
                BATCH.[STATUSCODE] = 1 -- Committed


        ) as [PROCESSSTEPS]
    where
        PROCESSSTEPS.[PHONEFINDERID] = @ID and
        PROCESSSTEPS.[DATELASTRUN] is not null
    order by
        PROCESSSTEPS.[DATELASTRUN] desc;

    return coalesce(@STEP, N'Last run on:');
end