USP_DATALIST_RESEARCHGROUPMEMBERWEALTHCAPACITY

CREATE procedure dbo.USP_DATALIST_RESEARCHGROUPMEMBERWEALTHCAPACITY
(
    @RESEARCHGROUPID uniqueidentifier
)
as
    set nocount on;

    declare @ESTIMATEDWEALTH table
    (
        ID uniqueidentifier,
        MINAMOUNT money,
        MAXAMOUNT money
    )

    declare @MAJORGIVINGCAPACITY table
    (
        ID uniqueidentifier,
        MINAMOUNT money,
        MAXAMOUNT money
    )

    insert @ESTIMATEDWEALTH
    exec dbo.USP_ESTIMATEDWEALTH_GETRANGES

    insert @MAJORGIVINGCAPACITY
    exec dbo.USP_MAJORGIVINGCAPACITY_GETRANGES

    select 
        w.ID,
        ew.MINAMOUNT as ESTIMATEDWEALTH, 
        ew.MAXAMOUNT as ESTIMATEDWEALTHMAX,  
        mgc.MINAMOUNT as MAJORGIVINGCAPACITY, 
        mgc.MAXAMOUNT as MAJORGIVINGCAPACITYMAX, 
        orc.DESCRIPTION as OVERALLRATING, 
        w.MAJORGIVINGCAPACITYVALUE
    from 
        dbo.RESEARCHGROUPMEMBER
        left join dbo.WEALTHCAPACITY w on w.ID = RESEARCHGROUPMEMBER.CONSTITUENTID
        left join @ESTIMATEDWEALTH ew on ew.ID = w.ESTIMATEDWEALTHID
        left join @MAJORGIVINGCAPACITY mgc on mgc.ID = w.MAJORGIVINGCAPACITYID
        left join dbo.OVERALLRATINGCODE orc on orc.ID = w.OVERALLRATINGCODEID
    where 
        RESEARCHGROUPMEMBER.RESEARCHGROUPID = @RESEARCHGROUPID