USP_DATALIST_SCREENPLANSTEP_INPROGRESS

            CREATE procedure dbo.USP_DATALIST_SCREENPLANSTEP_INPROGRESS(@SCREENPLANID uniqueidentifier = null, @STEPCODEID uniqueidentifier = null, @DAYSOUT tinyint =  null)
            as
                set nocount on;

                declare @ENDDATE datetime;
                declare @TODAY datetime

                set @TODAY = dbo.UFN_DATE_GETEARLIESTTIME(getdate());

                set @ENDDATE = 
                    case COALESCE(@DAYSOUT, 2)
                        when 1 then --week

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

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

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

                            dateadd(yy, 1, @TODAY)
                        else
                            @TODAY
                    end

                select     case when VSPS.DUEDATE < @TODAY then 'RES:warning' else '' end,
                        VSPS.ID,
                        VSPS.DUEDATE,
                        C.NAME,
                        VT.NAME,
                        SP.NAME,
                        SCREENPLANSTEPCODE.DESCRIPTION,
                        VSPS.SCREENPLANID,
                        VSP.VOLUNTEERID,
                        case 
                            when VSPS.DUEDATE < @TODAY  then 'Overdue'
                            when VSPS.DUEDATE <= dateadd(d, 7, @TODAY) then 'One week'
                            when VSPS.DUEDATE <= dateadd(m, 1, @TODAY) then 'One month'
                            when VSPS.DUEDATE <= dateadd(m, 6, @TODAY) then 'Six months'
                            when VSPS.DUEDATE <= dateadd(yy, 1, @TODAY) then 'One year'
                            else 'over one year' end
                from dbo.VOLUNTEERSCREENPLANSTEP VSPS
                inner join dbo.VOLUNTEERSCREENPLAN VSP
                    on VSP.ID = VSPS.SCREENPLANID
                inner join dbo.SCREENPLAN SP
                    on SP.ID = VSP.SCREENPLANID
                inner join dbo.CONSTITUENT C
                    on C.ID = VSP.VOLUNTEERID
                inner join dbo.SCREENPLANSTEPCODE
                    on SCREENPLANSTEPCODE.ID = VSPS.STEPCODEID
                left outer join dbo.VOLUNTEERVOLUNTEERTYPE VVT
                    on VVT.VOLUNTEERSCREENPLANID = VSP.ID
                left outer join dbo.VOLUNTEERTYPE VT
                    on VT.ID = VVT.VOLUNTEERTYPEID
                where   C.ISINACTIVE = 0 ---active constit

                        and dbo.UFN_CONSTITUENT_ISVOLUNTEER(C.ID) = 1 --active volunteer

                        and VSPS.STATUSCODE = 0 --pending

                        and SP.ID = COALESCE(@SCREENPLANID, SP.ID)
                        and VSPS.STEPCODEID  = COALESCE(@STEPCODEID, VSPS.STEPCODEID)
                        and VSPS.DUEDATE <= @ENDDATE
                order by VSPS.DUEDATE, C.NAME;