UFN_REVENUE_GETDONORRETENTION

CREATE function [dbo].[UFN_REVENUE_GETDONORRETENTION]
(
   @CONSTITUENTID    uniqueidentifier
  ,@EVENTID            uniqueidentifier
)
returns decimal (5, 2)
with execute as caller
as begin

    declare @PRIORYEAREVENTID as uniqueidentifier, @PERDONORRETAINED decimal (5, 2)
  SELECT @PRIORYEAREVENTID= EE.PRIORYEAREVENTID FROM EVENT E JOIN EVENTEXTENSION EE ON E.ID= EE.EVENTID WHERE E.ID= @EVENTID

  ;WITH GET_DONOR_EVENT
  AS
  (    SELECT          'PE' AS EVENTSTATE, RR.CONSTITUENTID, ER.TRANSACTIONTYPE, ER.[ORIGINAL AMOUNT], ER.[APPLIED AMOUNT], ER.REVENUESPLITID, ER.CONSTITUENTID AS DCONSTITUENTID
      FROM              [DBO].[UFN_REVENUE_EVENT](@PRIORYEAREVENTID) AS ER
      INNER JOIN    REVENUERECOGNITION RR    ON     RR.REVENUESPLITID= ER.REVENUESPLITID    
      WHERE              RR.CONSTITUENTID = @CONSTITUENTID
      UNION
    SELECT          'CE' AS EVENTSTATE, RR.CONSTITUENTID, ER.TRANSACTIONTYPE, ER.[ORIGINAL AMOUNT], ER.[APPLIED AMOUNT], ER.REVENUESPLITID, ER.CONSTITUENTID AS DCONSTITUENTID
      FROM              [DBO].[UFN_REVENUE_EVENT](@EVENTID) AS ER
      INNER JOIN    REVENUERECOGNITION RR    ON RR.REVENUESPLITID= ER.REVENUESPLITID    
      WHERE              RR.CONSTITUENTID = @CONSTITUENTID
  )
  , GET_DONOR_COUNT
  AS
  (    
      SELECT          COUNT(DISTINCT DCONSTITUENTID)  AS NumDonorsCEPE, (SELECT COUNT (DISTINCT DCONSTITUENTID) FROM GET_DONOR_EVENT WHERE EVENTSTATE= 'PE') AS NumDonorsPE
      FROM              GET_DONOR_EVENT 
      WHERE              EVENTSTATE= 'PE' 
      AND                  DCONSTITUENTID IN (SELECT DCONSTITUENTID FROM GET_DONOR_EVENT WHERE EVENTSTATE= 'CE') 
  )

      SELECT        @PERDONORRETAINED= (case when ISNULL(NumDonorsCEPE, '')= '' OR ISNULL(NumDonorsPE, '')= '' THEN 0.0 ELSE cast(NumDonorsCEPE as float)/cast(NumDonorsPE as float) END) 
    FROM          GET_DONOR_COUNT

    RETURN
    (
        @PERDONORRETAINED
    )
end