USP_DATALIST_FAFSPONSORSHIPPERFORMANCE

CREATE procedure dbo.USP_DATALIST_FAFSPONSORSHIPPERFORMANCE(@EVENTID uniqueidentifier) 
as
    set nocount on;

    select EST.ID,
           EST.NAME,
           case when COUNT(ES.ID) is not null then COUNT(ES.ID) else 0 end as [COUNT],
           case when COUNT(SD.ID) is not null then COUNT(SD.ID) else 0 end as [VOLUNTARYDONATIONCOUNT],
           case when SUM(ES.FEESAMOUNT) is not null then SUM(ES.FEESAMOUNT) else 0 end as [AMOUNT],
           case when SUM(SD.VOLUNTARYDONATION) is not null then SUM(SD.VOLUNTARYDONATION) else 0 end as [VOLUNTARYDONATIONAMOUNT]

    from dbo.EVENTSPONSORSHIPTYPE EST (nolock)
    left outer join dbo.EVENTSPONSOR ES (nolock) on EST.ID = ES.EVENTSPONSORSHIPTYPEID
    left outer join dbo.SPONSORDONATION SD (nolock) on ES.ID = SD.SPONSORID and SD.VOLUNTARYDONATION > 0

    where EST.EVENTID = @EVENTID
    group by EST.ID, EST.NAME, EST.FEESAMOUNT
    order by EST.FEESAMOUNT