USP_GROUPMEMBERS_GETWEALTHCAPACITY

            CREATE procedure dbo.USP_GROUPMEMBERS_GETWEALTHCAPACITY
            (
                @GROUPID uniqueidentifier
            )
            as
                set nocount on

                declare @CURRENTDATE datetime;
                set @CURRENTDATE = dbo.UFN_DATE_GETEARLIESTTIME(getdate());

                declare @MAJORGIVINGCAPACITY table
                (
                    ID uniqueidentifier,
                    MINAMOUNT money,
                    MAXAMOUNT money
                )                        
                insert into @MAJORGIVINGCAPACITY (ID, MINAMOUNT, MAXAMOUNT)
                exec dbo.USP_MAJORGIVINGCAPACITY_GETRANGES

                declare @ESTIMATEDWEALTH table
                (
                    ID uniqueidentifier,
                    MINAMOUNT money,
                    MAXAMOUNT money
                )                        
                insert into @ESTIMATEDWEALTH (ID, MINAMOUNT, MAXAMOUNT)
                exec dbo.USP_ESTIMATEDWEALTH_GETRANGES    

                select
                    C.ID,
                    C.NAME,
                    EW.MINAMOUNT as ESTIMATEDWEALTHMIN,
                    EW.MAXAMOUNT as ESTIMATEDWEALTHMAX,
                    ORC.DESCRIPTION as OVERALLRATING,
                    MGC.MINAMOUNT as MAJORGIVINGCAPACITYMIN,
                    MGC.MAXAMOUNT as MAJORGIVINGCAPACITYMAX,
                    WC.[MAJORGIVINGCAPACITYVALUE],
          WC.[ESTIMATEDWEALTHVALUE]
                from dbo.GROUPMEMBER as GM
                inner join dbo.CONSTITUENT as C on GM.MEMBERID = C.ID

                left join dbo.WEALTHCAPACITY as WC on WC.ID = C.ID
                left join dbo.OVERALLRATINGCODE as ORC on ORC.ID = OVERALLRATINGCODEID
                left join @MAJORGIVINGCAPACITY as MGC on MGC.ID = WC.MAJORGIVINGCAPACITYID
                left join @ESTIMATEDWEALTH as EW on EW.ID = WC.ESTIMATEDWEALTHID
                left join dbo.GROUPMEMBERDATERANGE as GMDR on GM.ID = GMDR.GROUPMEMBERID
                where ((GMDR.DATEFROM is null and (GMDR.DATETO is null or GMDR.DATETO > @CURRENTDATE))
                    or (GMDR.DATETO is null and (GMDR.DATEFROM is null or GMDR.DATEFROM <= @CURRENTDATE)) 
                    or (GMDR.DATEFROM <= @CURRENTDATE and GMDR.DATETO > @CURRENTDATE))
                and GM.GROUPID = @GROUPID