USP_BBNC_EMPLOYMENTHISTORYDATA_FOR_GIVEN_CONSTITIDS

            CREATE PROCEDURE dbo.USP_BBNC_EMPLOYMENTHISTORYDATA_FOR_GIVEN_CONSTITIDS(@IDs nvarchar(max))
            as
            BEGIN
                set NOCOUNT on;

                declare @IDS_TABLE table (
                    SEQUENCEID int
                );

                insert into
                    @IDS_TABLE (SEQUENCEID)
                select
                    ID
                from
                    dbo.fnMakeIDsTableFromString(@IDs, ',');

                SELECT 

                    RJI.JOBTITLE AS POSITION,
                    JOBCODE.DESCRIPTION AS PROFESSION,
                    CAREERCODE.DESCRIPTION AS CAREERLEVEL,
                    RJI.STARTDATE,
                    RJI.ENDDATE,
                    SCHEDULECODE.DESCRIPTION AS SCHEDULE,
                    RJI.JOBDIVISION AS DIVISION,
                    RJI.JOBDEPARTMENT AS DEPARTMENT,
                    EMPLOYERSTATE.ABBREVIATION AS EMPLOYERSTATE,
                    EMPLOYERADDRESS.CITY AS EMPLOYERCITY,
                    EMPLOYER.KEYNAME AS EMPLOYER,
                    Case 
                    when (RJI.ENDDATE IS NULL)
                    then 'Yes'
                    else 'No'
                    end as [CURRENT],
                    INDUSTRYCODE.DESCRIPTION AS EMPLOYERINDUSTRY,
                    EMPLOYERCOUNTRY.DESCRIPTION AS EMPLOYERCOUNTRY,          
                    EMPLOYERSTATE.DESCRIPTION AS EMPLOYERNZCITY,
                    EMPLOYERADDRESS.CITY AS EMPLOYERNZSUBURB,
                    CONST.SEQUENCEID as CONSTITUENTSEQUENCEID,
                    RJI.ID,
                    EMPLOYERCOUNTRY.COUNTRYADDRESSFORMATID AS COUNTRYADDRESSFORMATID,
                    REL.ISCONTACT,
                    REL.ISPRIMARYBUSINESS,
                    REL.ISMATCHINGGIFTRELATIONSHIP,
                    REL.CONTACTTYPECODEID AS CONTACTTYPEID,
                    EMPLOYERREGIONCODE.DESCRIPTION AS EMPLOYERREGION,
          INDUSTRYCODE.ID AS INDUSTRYID,
          CAREERCODE.ID AS CAREERID,
          SCHEDULECODE.ID AS SCHEDULEID,
          JOBCODE.ID AS PROFESSIONID,
          REL.COMMENTS,
          REL.RELATIONSHIPSETID,
          RJI.JOBRESPONSIBILITY as RESPONSIBILITY,
          RJI.ISPRIVATERECORD
                FROM dbo.CONSTITUENT as CONST
                INNER JOIN @IDS_TABLE as IDs on IDs.SEQUENCEID = CONST.SEQUENCEID
                INNER JOIN dbo.RELATIONSHIP as REL ON REL.RELATIONSHIPCONSTITUENTID = CONST.ID
                INNER JOIN dbo.CONSTITUENT as EMPLOYER on REL.RECIPROCALCONSTITUENTID = EMPLOYER.ID and EMPLOYER.ISORGANIZATION = 1 
                INNER JOIN dbo.RELATIONSHIPJOBINFO as RJI ON RJI.RELATIONSHIPSETID = REL.RELATIONSHIPSETID
                LEFT JOIN dbo.ORGANIZATIONDATA as EMPLOYERDATA on EMPLOYER.ID = EMPLOYERDATA.ID
                LEFT JOIN dbo.ADDRESS as EMPLOYERADDRESS on EMPLOYER.ID = EMPLOYERADDRESS.CONSTITUENTID AND EMPLOYERADDRESS.ISPRIMARY = 1
                LEFT JOIN dbo.STATE as EMPLOYERSTATE on EMPLOYERADDRESS.STATEID = EMPLOYERSTATE.ID
                LEFT JOIN dbo.JOBCATEGORYCODE as JOBCODE ON JOBCODE.ID = RJI.JOBCATEGORYCODEID
                LEFT JOIN dbo.CAREERLEVELCODE as CAREERCODE ON CAREERCODE.ID = RJI.CAREERLEVELCODEID
                LEFT JOIN dbo.JOBSCHEDULECODE as SCHEDULECODE ON SCHEDULECODE.ID = RJI.JOBSCHEDULECODEID
                LEFT JOIN dbo.INDUSTRYCODE ON INDUSTRYCODE.ID = EMPLOYERDATA.INDUSTRYCODEID
                LEFT JOIN COUNTRY AS EMPLOYERCOUNTRY ON EMPLOYERADDRESS.COUNTRYID = EMPLOYERCOUNTRY.ID
                LEFT JOIN dbo.ADDRESSVALIDATIONUPDATE as EMPLOYERADDRESSVALIDATIONUPDATE on EMPLOYERADDRESSVALIDATIONUPDATE.ID = EMPLOYERADDRESS.ID
                LEFT JOIN dbo.REGIONCODE as EMPLOYERREGIONCODE on EMPLOYERREGIONCODE.ID = EMPLOYERADDRESSVALIDATIONUPDATE.REGIONCODEID
        ORDER BY RJI.STARTDATE DESC, RJI.ENDDATE DESC
            END