USP_DATALIST_EMPLOYMENTHISTORY

This datalist returns a list of all employment history for a constituent.
Parameters

Parameter	Parameter Type	Mode	Description
@CONSTITUENTID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.
@INCLUDEINACTIVE	bit	IN	Include inactive
Definition

Copy


                CREATE procedure dbo.USP_DATALIST_EMPLOYMENTHISTORY 
                (
                    @CONSTITUENTID uniqueidentifier,
                    @INCLUDEINACTIVE bit = 0
                )
                as
                set nocount on;

                declare @CURRENTDATE date = dbo.UFN_DATE_GETEARLIESTTIME(getdate());

                select    
                    RELATIONSHIP.RELATIONSHIPSETID,
                    RELATIONSHIPJOBINFO.ID,
                    RELATIONSHIP.RECIPROCALCONSTITUENTID,
                    (
                        rtrim(NF.NAME + ' ' +
                            coalesce(
                                replace(
                                    stuff(
                                        (select '' + STATUS from
                                        (select ',(Deceased)' STATUS
                                            from dbo.DECEASEDCONSTITUENT DC
                                            where DC.ID = CONSTITUENT.ID
                                        union all
                                        select ',(Inactive)' STATUS
                                            from dbo.CONSTITUENT C
                                            where C.ID = CONSTITUENT.ID
                                            and C.ISINACTIVE=1
                                    ) as SUBQ for xml path(''))
                                    ,1,1,'')
                                , '),(', ', ')
                            ,'')
                        )
                    ) as NAME,
                    RELATIONSHIPJOBINFO.JOBTITLE,
                    dbo.UFN_JOBCATEGORYCODE_GETDESCRIPTION(RELATIONSHIPJOBINFO.JOBCATEGORYCODEID) as JOBCATEGORY,
                    dbo.UFN_CAREERLEVELCODE_GETDESCRIPTION(RELATIONSHIPJOBINFO.CAREERLEVELCODEID) as CAREERLEVEL,
                    RELATIONSHIPJOBINFO.STARTDATE,
                    RELATIONSHIPJOBINFO.ENDDATE,
                    dbo.UFN_JOBSCHEDULECODE_GETDESCRIPTION(RELATIONSHIPJOBINFO.JOBSCHEDULECODEID) as JOBSCHEDULE,
                    RELATIONSHIPJOBINFO.JOBDEPARTMENT,
                    RELATIONSHIPJOBINFO.JOBDIVISION,
          RELATIONSHIPJOBINFO.JOBRESPONSIBILITY,
          RELATIONSHIPJOBINFO.ISPRIVATERECORD
                from 
                    dbo.RELATIONSHIPJOBINFO
                inner join 
                    dbo.RELATIONSHIP
                        on RELATIONSHIP.RELATIONSHIPSETID = RELATIONSHIPJOBINFO.RELATIONSHIPSETID
                inner join 
                    dbo.CONSTITUENT 
                        on RELATIONSHIP.RECIPROCALCONSTITUENTID = CONSTITUENT.ID
                outer apply
                    dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTITUENT.ID) NF
                where 
                    RELATIONSHIP.RELATIONSHIPCONSTITUENTID = @CONSTITUENTID
                    and
                    (@INCLUDEINACTIVE = 1 
                    or
                    @CURRENTDATE between 
                        coalesce(RELATIONSHIPJOBINFO.STARTDATE, @CURRENTDATE) and 
                        coalesce(RELATIONSHIPJOBINFO.ENDDATE, @CURRENTDATE)
                    )
                order by
                    RELATIONSHIPJOBINFO.STARTDATE,
                    RELATIONSHIPJOBINFO.ENDDATE,
                    CONSTITUENT.KEYNAME, 
                    CONSTITUENT.NAME;