USP_DATALIST_EXPIRINGTRAIT

            CREATE procedure dbo.USP_DATALIST_EXPIRINGTRAIT (@TRAITTYPE tinyint = null, @VERIFIEDONLY bit = 1, @DAYSOUT tinyint = null)
            as
                set nocount on;
                declare @CURRENTDATE datetime;
                set @CURRENTDATE = getdate();

                declare @UPPERBOUND datetime;
                set @UPPERBOUND = dbo.UFN_DATE_GETLATESTTIME(@CURRENTDATE);

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


                declare @STARTDATE datetime;
                declare @ENDDATE datetime;

                declare @TRAITS table 
                    (
                        ID uniqueidentifier, 
                        VOLUNTEERID uniqueidentifier,
                        TRAITTYPEID tinyint,
                        TRAITTYPE nvarchar(25),
                        CERTIFICATION nvarchar(100),
                        CERTIFICATE nvarchar(25),
                        COURSE nvarchar(100),
                        LICENSE nvarchar(100),
                        LICENSENUMBER nvarchar(25),
                        MEDICAL nvarchar(100),
                        ADMINISTRATIVE nvarchar(100),
                        EXPIRESON datetime,
                        VERIFIED bit default(0)
                    );

                set @DAYSOUT = coalesce(@DAYSOUT, 0);

                set @STARTDATE = @LOWERBOUND;
                if @DAYSOUT = 0 --Expired

                begin
                    set @STARTDATE = null;
                end

                select @ENDDATE = 
                    case @DAYSOUT
                        when 1 then --week

                            dateadd(d, 7, @UPPERBOUND)
                        when 2 then --month

                            dateadd(m, 1, @UPPERBOUND)
                        when 3 then --6 months

                            dateadd(m, 6, @UPPERBOUND)
                        when 4 then --1 year

                            dateadd(yy, 1, @UPPERBOUND)
                        else --Expired

                            null
                    end

                --Administrative

                if coalesce(@TRAITTYPE, 0) = 0
                    insert into @TRAITS(ID, VOLUNTEERID, TRAITTYPEID, TRAITTYPE, ADMINISTRATIVE,EXPIRESON,VERIFIED)
                        select    VA.ID, 
                                VA.VOLUNTEERID,
                                0,
                                'Administrative',
                                ADMINISTRATIVE.DESCRIPTION,
                                VA.EXPIRESON,
                                VA.VERIFIED
                        from VOLUNTEERADMINISTRATIVE VA
                        inner join VOLUNTEERADMINISTRATIVECODE ADMINISTRATIVE on ADMINISTRATIVE.ID = VA.ADMINISTRATIVECODEID
                        where 
                            ((@STARTDATE is null and VA.EXPIRESON <= @UPPERBOUND) 
                                or (VA.EXPIRESON between @STARTDATE and @ENDDATE))
                            and
                                1 = case when @VERIFIEDONLY = 0 then 1 else VA.VERIFIED end;

                --Certification

                if coalesce(@TRAITTYPE, 1) = 1
                    insert into @TRAITS(ID, VOLUNTEERID, TRAITTYPEID, TRAITTYPE, CERTIFICATION, CERTIFICATE,EXPIRESON,VERIFIED)
                        select    VC.ID, 
                                VC.VOLUNTEERID,
                                1,
                                'Certification',
                                CERTIFICATION.DESCRIPTION,
                                VC.CERTIFICATE,
                                VC.EXPIRESON,
                                VC.VERIFIED
                        from VOLUNTEERCERTIFICATION VC
                        inner join VOLUNTEERCERTIFICATIONCODE CERTIFICATION on CERTIFICATION.ID = VC.CERTIFICATIONCODEID
                        where 
                            ((@STARTDATE is null and VC.EXPIRESON <= @UPPERBOUND) 
                                or (VC.EXPIRESON between @STARTDATE and @ENDDATE))
                            and
                                1 = case when @VERIFIEDONLY = 0 then 1 else VC.VERIFIED end;

                --Course

                if coalesce(@TRAITTYPE, 2) = 2
                    insert into @TRAITS(ID, VOLUNTEERID, TRAITTYPEID, TRAITTYPE, COURSE,EXPIRESON,VERIFIED)
                        select    VC.ID, 
                                VC.VOLUNTEERID,
                                2,
                                'Course',
                                COURSE.DESCRIPTION,
                                VC.EXPIRESON,
                                VC.VERIFIED
                        from VOLUNTEERCOURSE VC
                        inner join VOLUNTEERCOURSECODE COURSE on COURSE.ID = VC.COURSECODEID
                        where 
                            ((@STARTDATE is null and VC.EXPIRESON <= @UPPERBOUND) 
                                or (VC.EXPIRESON between @STARTDATE and @ENDDATE))
                            and
                                1 = case when @VERIFIEDONLY = 0 then 1 else VC.VERIFIED end;

                --License

                if coalesce(@TRAITTYPE, 4) = 4
                    insert into @TRAITS(ID, VOLUNTEERID, TRAITTYPEID, TRAITTYPE, LICENSE, LICENSENUMBER,EXPIRESON,VERIFIED)
                        select    VL.ID, 
                                VL.VOLUNTEERID,
                                4,
                                'License',
                                LICENSE.DESCRIPTION,
                                VL.LICENSENUMBER,
                                VL.EXPIRESON,
                                VL.VERIFIED
                        from VOLUNTEERLICENSE VL
                        inner join VOLUNTEERLICENSECODE LICENSE on LICENSE.ID = VL.LICENSECODEID
                        where 
                            ((@STARTDATE is null and VL.EXPIRESON <= @UPPERBOUND) 
                                or (VL.EXPIRESON between @STARTDATE and @ENDDATE))
                            and
                                1 = case when @VERIFIEDONLY = 0 then 1 else VL.VERIFIED end;

                --Medical

                if coalesce(@TRAITTYPE, 5) = 5
                    insert into @TRAITS(ID, VOLUNTEERID, TRAITTYPEID, TRAITTYPE, MEDICAL,EXPIRESON,VERIFIED)
                        select    VM.ID,
                                VM.VOLUNTEERID,
                                5,
                                'Medical',
                                MEDICAL.DESCRIPTION,
                                VM.EXPIRESON,
                                VM.VERIFIED
                        from VOLUNTEERMEDICAL VM
                        inner join VOLUNTEERMEDICALCODE MEDICAL on MEDICAL.ID = VM.MEDICALCODEID
                        where 
                            ((@STARTDATE is null and VM.EXPIRESON <= @UPPERBOUND) 
                                or (VM.EXPIRESON between @STARTDATE and @ENDDATE))
                            and
                                1 = case when @VERIFIEDONLY = 0 then 1 else VM.VERIFIED end;

                --Select final results    

                select 
                        TRAITS.ID,
                        TRAITS.VOLUNTEERID,
                        CONSTITUENT.NAME,
                        TRAITS.TRAITTYPEID,
                        TRAITS.TRAITTYPE,
                        coalesce(TRAITS.ADMINISTRATIVE,TRAITS.CERTIFICATION,TRAITS.COURSE,TRAITS.LICENSE,TRAITS.MEDICAL),
                        coalesce(TRAITS.CERTIFICATE,TRAITS.LICENSENUMBER),
                        TRAITS.EXPIRESON,
                        TRAITS.VERIFIED
                from @TRAITS TRAITS
                inner join dbo.CONSTITUENT
                    on CONSTITUENT.ID = TRAITS.VOLUNTEERID
                inner join dbo.VOLUNTEERDATERANGE
                    on VOLUNTEERDATERANGE.CONSTITUENTID = CONSTITUENT.ID
                where CONSTITUENT.ISINACTIVE = 0 
                        and (VOLUNTEERDATERANGE.DATEFROM <= @UPPERBOUND or VOLUNTEERDATERANGE.DATEFROM is null) 
                        and (VOLUNTEERDATERANGE.DATETO >= @LOWERBOUND or VOLUNTEERDATERANGE.DATETO is null)
                order by TRAITS.EXPIRESON, coalesce(TRAITS.CERTIFICATION,TRAITS.COURSE,TRAITS.LICENSE,TRAITS.MEDICAL);