USP_SMARTFIELD_TOTALDONATIONAMOUNTRAISEDPARTICIPANT_TIMESPAN

CREATE procedure dbo.USP_SMARTFIELD_TOTALDONATIONAMOUNTRAISEDPARTICIPANT_TIMESPAN
(
     @WEEKORMONTHCODE tinyint= 1,
     @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()

    IF @WEEKORMONTHCODE= 1

        SELECT    RG.ID, SUM(RS.AMOUNT) AS REGISTRANTDONATION 
        FROM    dbo.REVENUE R            (NOLOCK)
        JOIN    dbo.REVENUESPLIT RS        (NOLOCK) ON R.ID = RS.REVENUEID
        JOIN    dbo.REGISTRANT RG        (NOLOCK) ON R.CONSTITUENTID = RG.CONSTITUENTID
        JOIN    dbo.EVENTEXTENSION EE    (NOLOCK) ON RG.EVENTID = EE.EVENTID
        WHERE    RS.TYPECODE = 0
        AND        R.TRANSACTIONTYPECODE = 0
        AND        RG.DATEADDED    < getdate() -7
        GROUP BY RG.ID
        ORDER BY RG.ID        
    ELSE

        SELECT    RG.ID, SUM(RS.AMOUNT) AS REGISTRANTDONATION 
        FROM    dbo.REVENUE R            (NOLOCK)
        JOIN    dbo.REVENUESPLIT RS        (NOLOCK) ON R.ID = RS.REVENUEID
        JOIN    dbo.REGISTRANT RG        (NOLOCK) ON R.CONSTITUENTID = RG.CONSTITUENTID
        JOIN    dbo.EVENTEXTENSION EE    (NOLOCK) ON RG.EVENTID = EE.EVENTID
        WHERE    RS.TYPECODE = 0
        AND        R.TRANSACTIONTYPECODE = 0
        AND        RG.DATEADDED    BETWEEN    CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,-1,getdate()))-1),DATEADD(mm,-1,getdate())),101)  
                                AND        CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(getdate())),getdate()),101) 
        GROUP BY RG.ID
        ORDER BY RG.ID            
END