USP_SMARTFIELD_TOTALDONATIONAMOUNTRAISEDPARTICIPANT

CREATE procedure dbo.USP_SMARTFIELD_TOTALDONATIONAMOUNTRAISEDPARTICIPANT
(
   @TIMESPANCODE tinyint= 0,
     @ASOF datetime 
)
as

set nocount on

-- write sql to calculate the smart field values based on the parameters and ASOF date

BEGIN
    --IF @ASOF IS NULL SET @ASOF= getdate()


    SELECT    RG.ID, SUM(RS.AMOUNT) AS DONATIONRAISED 
    FROM    dbo.REVENUE R            
    join    dbo.REVENUESPLIT RS     ON R.ID = RS.REVENUEID and RS.APPLICATIONCODE = 0
  join  dbo.REVENUERECOGNITION RC ON RC.REVENUESPLITID = RS.ID
    join    dbo.REGISTRANT RG    ON RC.CONSTITUENTID = RG.CONSTITUENTID
    join    dbo.EVENTEXTENSION EE     ON RG.EVENTID = EE.EVENTID  -- this join to ensure these are registrant of FAF event    

  WHERE @TIMESPANCODE = 0
    GROUP BY RG.ID    

  UNION ALL

SELECT    RG.ID, SUM(RS.AMOUNT) AS DONATIONRAISED 
    FROM    dbo.REVENUE R            
    join    dbo.REVENUESPLIT RS     ON R.ID = RS.REVENUEID and RS.APPLICATIONCODE = 0
    join  dbo.REVENUERECOGNITION RC ON RC.REVENUESPLITID = RS.ID
    join    dbo.REGISTRANT RG    ON RC.CONSTITUENTID = RG.CONSTITUENTID
    join    dbo.EVENTEXTENSION EE     ON RG.EVENTID = EE.EVENTID  -- this join to ensure these are registrant of FAF event      

    where @TIMESPANCODE =1 and RS.DATEADDED between DATEADD(WEEK, -1, @ASOF) and @ASOF
    GROUP BY RG.ID    

UNION ALL

SELECT    RG.ID, SUM(RS.AMOUNT) AS DONATIONRAISED 
    FROM    dbo.REVENUE R            
    join    dbo.REVENUESPLIT RS     ON R.ID = RS.REVENUEID and RS.APPLICATIONCODE = 0
    join  dbo.REVENUERECOGNITION RC ON RC.REVENUESPLITID = RS.ID
    join    dbo.REGISTRANT RG    ON RC.CONSTITUENTID = RG.CONSTITUENTID
    join    dbo.EVENTEXTENSION EE     ON RG.EVENTID = EE.EVENTID  -- this join to ensure these are registrant of FAF event      

    where @TIMESPANCODE =2 and RS.DATEADDED between DATEADD(MONTH, -1, @ASOF) and @ASOF
    GROUP BY RG.ID    

END