USP_DATALIST_JOBVACANCY

            CREATE procedure dbo.USP_DATALIST_JOBVACANCY (@JOBID uniqueidentifier = null, @DAYSOUT tinyint =  null, @CURRENTAPPUSERID uniqueidentifier = null)
            as
                set nocount on;

                declare @FROM datetime;
                declare @TO datetime;

                set @FROM = dbo.UFN_DATE_GETEARLIESTTIME(getdate());
                set @TO = 
                    case COALESCE(@DAYSOUT, 2)
                        when 1 then --week

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

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

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

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


                select  VACANCIES.OCCURRENCEID, 
                        JOB.ID,
                        JOB.NAME, 
                        JOBOCCURRENCE.DESCRIPTION, 
                        VACANCIES.MINDATE, 
                        (select top 1 SUBNEEDED.OPENINGS
                                from dbo.[UFN_JOBOCCURRENCE_VACANTDAYS](VACANCIES.MINDATE,VACANCIES.MINDATE,null,@JOBID) SUBNEEDED
                                where SUBNEEDED.OCCURRENCEID = VACANCIES.OCCURRENCEID) OPENINGS,
                        VACANCIES.OCCURRENCEID,
                        JOBOCCURRENCE.STARTTIME,
                        JOBOCCURRENCE.ENDTIME,
                        VOLUNTEERLOCATIONCODE.DESCRIPTION,
                        DEPARTMENTCODE.DESCRIPTION
                from 
                    (select OCCURRENCEID, min(ASSIGNMENTDATE) MINDATE
                        from dbo.[UFN_JOBOCCURRENCE_VACANTDAYS](@FROM,@TO,null,@JOBID) SUB
                        group by OCCURRENCEID) VACANCIES
                inner join dbo.JOBOCCURRENCE on VACANCIES.OCCURRENCEID = JOBOCCURRENCE.ID
                inner join dbo.JOB on JOB.ID = JOBOCCURRENCE.JOBID
                left outer join VOLUNTEERLOCATIONCODE
                    on JOBOCCURRENCE.LOCATIONCODEID = VOLUNTEERLOCATIONCODE.ID
                left outer join DEPARTMENTCODE
                    on JOBOCCURRENCE.DEPARTMENTCODEID = DEPARTMENTCODE.ID
                where dbo.UFN_SITEALLOWEDFORUSER(@CURRENTAPPUSERID, JOBOCCURRENCE.SITEID)=1
                    and JOBOCCURRENCE.ISACTIVE = 1
                order by VACANCIES.MINDATE, JOB.NAME, OPENINGS, STARTTIME