Error converting data type varchar to numeric and Conversion failed when converting date and/or time from character string.

  • I'm trying to compare two date ranges of employee data (i.e. 6/1/2018 to 6/4/2018) **within the same table** . I did a self join to the table to compare each column and if the column did not meet criteria then I want it to show NULL.  I've noticed that not all columns are showing NULL when they should be and there are a few rows that are error out due to the following errors:

    1.Conversion failed when converting date and/or time from character string.
      ---WHEN emp1.RECENT_HIRE_DATE <> emp2.RECENT_HIRE_DATE THEN                   emp2.RECENT_HIRE_DATE
      ---WHEN emp1.ORIG_HIRE_DATE <> emp2.ORIG_HIRE_DATE THEN emp2.ORIG_HIRE_DATE

    2. Error converting data type varchar to numeric.
       ---WHEN emp1.HRLY_RATE <> emp2.HRLY_RATE THEN emp2.HRLY_RATE
       ---WHEN emp1.EXPECTED_HOURS <> emp2.EXPECTED_HOURS THEN emp2.EXPECTED_HOURS
    etc.
    The lines commented out below in the code are throwing the error messages.

    Can someone please help with this?  I have tried to CAST / CONVERT the rows, but I obviously have the wrong syntax. The entire SQL query is below.

    select emp1.RRD,emp1.EMP_ID,emp1.JOB_REQ,emp1.POS_NUM,emp1.CANDIDATE_ID,emp1.EMP_STATUS,emp1.WORKER_TYPE,emp1.CONT_SUP_NAME,emp1.REMOTE_FLAG,emp1.emp_NAME,
    emp1.FIRST_NAME,emp1.LAST_NAME,emp1.RECENT_HIRE_DATE,emp1.ORIG_HIRE_DATE,emp1.DEPT_NUM,emp1.DEPT_NAME,emp1.FUNCTIONAL_GROUP,emp1.PAY_TYPE,emp1.JOB_TITLE,
    emp1.JOB_CODE,emp1.JOB_FAMILY,emp1.JOB_GROUP,emp1.JOB_FUNCTION,emp1.WORK_PHONE,emp1.EMAIL,emp1.EMP_MGR_LVL,emp1.ANNUAL_RATE,emp1.HRLY_RATE,emp1.EMP_TYPE,
    emp1.TIME_TYPE,emp1.EXEMPT,emp1.EXPECTED_HOURS,emp1.COMP_GRADE,emp1.WORK_LOC_ID,emp1.WORK_LOC_NAME,emp1.WORK_LOC_ADD_1,emp1.WORK_LOC_ADD_2,emp1.WORK_LOC_CITY,
    emp1.WORK_LOC_STATE,emp1.WORK_LOC_ZIP,emp1.HOME_ADD_1,emp1.HOME_ADD_2,emp1.HOME_ADD_CITY,emp1.HOME_ADD_STATE,emp1.HOME_ADD_ZIP,emp1.HOME_ADD_ST,emp1.LOA,
    emp1.REPORTS_TO,emp1.REPORTS_TO_EMP_ID,emp1.REPORTS_TO_EMAIL,emp1.REPORTS_TO_MGR_LVL,emp1.emp_rank,emp1.MGR_LVL_NUM,emp1.HIERARCHY_TIER,emp1.TIER_1,
    emp1.TIER_2,emp1.TIER_3,emp1.TIER_4,emp1.TIER_5,emp1.TIER_6,emp1.Snapshot_FTE,emp1.Period_FTE,emp1.Info_Source,emp1.Calendar_period,emp2.RRD,emp2.EMP_ID,
    emp2.JOB_REQ,emp2.POS_NUM,emp2.CANDIDATE_ID,emp2.EMP_STATUS,emp2.WORKER_TYPE,emp2.CONT_SUP_NAME,emp2.REMOTE_FLAG,emp2.emp_NAME,emp2.FIRST_NAME,emp2.LAST_NAME,
    emp2.RECENT_HIRE_DATE,emp2.ORIG_HIRE_DATE,emp2.DEPT_NUM,emp2.DEPT_NAME,emp2.FUNCTIONAL_GROUP,emp2.PAY_TYPE,emp2.JOB_TITLE,emp2.JOB_CODE,emp2.JOB_FAMILY,
    emp2.JOB_GROUP,emp2.JOB_FUNCTION,emp2.WORK_PHONE,emp2.EMAIL,emp2.EMP_MGR_LVL,emp2.ANNUAL_RATE,emp2.HRLY_RATE,emp2.EMP_TYPE,emp2.TIME_TYPE,emp2.EXEMPT,
    emp2.EXPECTED_HOURS,emp2.COMP_GRADE,emp2.WORK_LOC_ID,emp2.WORK_LOC_NAME,emp2.WORK_LOC_ADD_1,emp2.WORK_LOC_ADD_2,emp2.WORK_LOC_CITY,emp2.WORK_LOC_STATE,
    emp2.WORK_LOC_ZIP,emp2.HOME_ADD_1,emp2.HOME_ADD_2,emp2.HOME_ADD_CITY,emp2.HOME_ADD_STATE,emp2.HOME_ADD_ZIP,emp2.HOME_ADD_ST,emp2.LOA,emp2.REPORTS_TO,
    emp2.REPORTS_TO_EMP_ID,emp2.REPORTS_TO_EMAIL,emp2.REPORTS_TO_MGR_LVL,emp2.emp_rank,emp2.MGR_LVL_NUM,emp2.HIERARCHY_TIER,emp2.TIER_1,emp2.TIER_2,emp2.TIER_3,
    emp2.TIER_4,emp2.TIER_5,emp2.TIER_6,emp2.Snapshot_FTE,emp2.Period_FTE,emp2.Info_Source,emp2.Calendar_period,

    CASEWHEN emp1.JOB_REQ <> emp2.JOB_REQ THEN emp2.JOB_REQWHEN emp1.POS_NUM <> emp2.POS_NUM THEN emp2.POS_NUMWHEN emp1.CANDIDATE_ID <> emp2.CANDIDATE_ID THEN emp2.CANDIDATE_IDWHEN emp1.EMP_STATUS <> emp2.EMP_STATUS THEN emp2.EMP_STATUSWHEN emp1.WORKER_TYPE <> emp2.WORKER_TYPE THEN emp2.WORKER_TYPEWHEN emp1.CONT_SUP_NAME <> emp2.CONT_SUP_NAME THEN emp2.CONT_SUP_NAMEWHEN emp1.REMOTE_FLAG <> emp2.REMOTE_FLAG THEN emp2.REMOTE_FLAGWHEN emp1.emp_NAME <> emp2.emp_NAME THEN emp2.emp_NAMEWHEN emp1.FIRST_NAME <> emp2.FIRST_NAME THEN emp2.FIRST_NAMEWHEN emp1.LAST_NAME <> emp2.LAST_NAME THEN emp2.LAST_NAME---WHEN emp1.RECENT_HIRE_DATE <> emp2.RECENT_HIRE_DATE THEN emp2.RECENT_HIRE_DATE---WHEN emp1.ORIG_HIRE_DATE <> emp2.ORIG_HIRE_DATE THEN emp2.ORIG_HIRE_DATEWHEN emp1.DEPT_NUM <> emp2.DEPT_NUM THEN emp2.DEPT_NUMWHEN emp1.DEPT_NAME <> emp2.DEPT_NAME THEN emp2.DEPT_NAMEWHEN emp1.FUNCTIONAL_GROUP <> emp2.FUNCTIONAL_GROUP THEN emp2.FUNCTIONAL_GROUPWHEN emp1.PAY_TYPE <> emp2.PAY_TYPE THEN emp2.PAY_TYPEWHEN emp1.JOB_TITLE <> emp2.JOB_TITLE THEN emp2.JOB_TITLEWHEN emp1.JOB_CODE <> emp2.JOB_CODE THEN emp2.JOB_CODEWHEN emp1.JOB_FAMILY <> emp2.JOB_FAMILY THEN emp2.JOB_FAMILYWHEN emp1.JOB_GROUP <> emp2.JOB_GROUP THEN emp2.JOB_GROUPWHEN emp1.JOB_FUNCTION <> emp2.JOB_FUNCTION THEN emp2.JOB_FUNCTIONWHEN emp1.WORK_PHONE <> emp2.WORK_PHONE THEN emp2.WORK_PHONEWHEN emp1.EMAIL <> emp2.EMAIL THEN emp2.EMAILWHEN emp1.EMP_MGR_LVL <> emp2.EMP_MGR_LVL THEN emp2.EMP_MGR_LVL---WHEN emp1.ANNUAL_RATE <> emp2.ANNUAL_RATE THEN emp2.ANNUAL_RATE---WHEN emp1.HRLY_RATE <> emp2.HRLY_RATE THEN emp2.HRLY_RATEWHEN emp1.EMP_TYPE <> emp2.EMP_TYPE THEN emp2.EMP_TYPEWHEN emp1.TIME_TYPE <> emp2.TIME_TYPE THEN emp2.TIME_TYPEWHEN emp1.EXEMPT <> emp2.EXEMPT THEN emp2.EXEMPT---WHEN emp1.EXPECTED_HOURS <> emp2.EXPECTED_HOURS THEN emp2.EXPECTED_HOURSWHEN emp1.COMP_GRADE <> emp2.COMP_GRADE THEN emp2.COMP_GRADEWHEN emp1.WORK_LOC_ID <> emp2.WORK_LOC_ID THEN emp2.WORK_LOC_IDWHEN emp1.WORK_LOC_NAME <> emp2.WORK_LOC_NAME THEN emp2.WORK_LOC_NAMEWHEN emp1.WORK_LOC_ADD_1 <> emp2.WORK_LOC_ADD_1 THEN emp2.WORK_LOC_ADD_1WHEN emp1.WORK_LOC_ADD_2 <> emp2.WORK_LOC_ADD_2 THEN emp2.WORK_LOC_ADD_2WHEN emp1.WORK_LOC_CITY <> emp2.WORK_LOC_CITY THEN emp2.WORK_LOC_CITYWHEN emp1.WORK_LOC_STATE <> emp2.WORK_LOC_STATE THEN emp2.WORK_LOC_STATEWHEN emp1.WORK_LOC_ZIP <> emp2.WORK_LOC_ZIP THEN emp2.WORK_LOC_ZIPWHEN emp1.HOME_ADD_1 <> emp2.HOME_ADD_1 THEN emp2.HOME_ADD_1WHEN emp1.HOME_ADD_2 <> emp2.HOME_ADD_2 THEN emp2.HOME_ADD_2WHEN emp1.HOME_ADD_CITY <> emp2.HOME_ADD_CITY THEN emp2.HOME_ADD_CITYWHEN emp1.HOME_ADD_STATE <> emp2.HOME_ADD_STATE THEN emp2.HOME_ADD_STATEWHEN emp1.HOME_ADD_ZIP <> emp2.HOME_ADD_ZIP THEN emp2.HOME_ADD_ZIPWHEN emp1.HOME_ADD_ST <> emp2.HOME_ADD_ST THEN emp2.HOME_ADD_STWHEN emp1.LOA <> emp2.LOA THEN emp2.LOAWHEN emp1.REPORTS_TO <> emp2.REPORTS_TO THEN emp2.REPORTS_TOWHEN emp1.REPORTS_TO_EMP_ID <> emp2.REPORTS_TO_EMP_ID THEN emp2.REPORTS_TO_EMP_IDWHEN emp1.REPORTS_TO_EMAIL <> emp2.REPORTS_TO_EMAIL THEN emp2.REPORTS_TO_EMAILWHEN emp1.REPORTS_TO_MGR_LVL <> emp2.REPORTS_TO_MGR_LVL THEN emp2.REPORTS_TO_MGR_LVL---WHEN emp1.emp_rank <> emp2.emp_rank THEN emp2.emp_rank---WHEN emp1.MGR_LVL_NUM <> emp2.MGR_LVL_NUM THEN emp2.MGR_LVL_NUM---WHEN emp1.HIERARCHY_TIER <> emp2.HIERARCHY_TIER THEN emp2.HIERARCHY_TIERWHEN emp1.TIER_1 <> emp2.TIER_1 THEN emp2.TIER_1WHEN emp1.TIER_2 <> emp2.TIER_2 THEN emp2.TIER_2WHEN emp1.TIER_3 <> emp2.TIER_3 THEN emp2.TIER_3WHEN emp1.TIER_4 <> emp2.TIER_4 THEN emp2.TIER_4WHEN emp1.TIER_5 <> emp2.TIER_5 THEN emp2.TIER_5WHEN emp1.TIER_6 <> emp2.TIER_6 THEN emp2.TIER_6---WHEN emp1.Snapshot_FTE <> emp2.Snapshot_FTE THEN emp2.Snapshot_FTE---WHEN emp1.Period_FTE <> emp2.Period_FTE THEN emp2.Period_FTEWHEN emp1.Info_Source <> emp2.Info_Source THEN emp2.Info_Source---WHEN emp1.Calendar_period <> emp2.Calendar_period THEN emp2.Calendar_period

    CASE
    WHEN emp1.JOB_REQ <> emp2.JOB_REQ THEN emp2.JOB_REQ
    WHEN emp1.POS_NUM <> emp2.POS_NUM THEN emp2.POS_NUM
    WHEN emp1.CANDIDATE_ID <> emp2.CANDIDATE_ID THEN emp2.CANDIDATE_ID
    WHEN emp1.EMP_STATUS <> emp2.EMP_STATUS THEN emp2.EMP_STATUS
    WHEN emp1.WORKER_TYPE <> emp2.WORKER_TYPE THEN emp2.WORKER_TYPE
    WHEN emp1.CONT_SUP_NAME <> emp2.CONT_SUP_NAME THEN emp2.CONT_SUP_NAME
    WHEN emp1.REMOTE_FLAG <> emp2.REMOTE_FLAG THEN emp2.REMOTE_FLAG
    WHEN emp1.emp_NAME <> emp2.emp_NAME THEN emp2.emp_NAME
    WHEN emp1.FIRST_NAME <> emp2.FIRST_NAME THEN emp2.FIRST_NAME
    WHEN emp1.LAST_NAME <> emp2.LAST_NAME THEN emp2.LAST_NAME
    ---WHEN emp1.RECENT_HIRE_DATE <> emp2.RECENT_HIRE_DATE THEN emp2.RECENT_HIRE_DATE
    ---WHEN emp1.ORIG_HIRE_DATE <> emp2.ORIG_HIRE_DATE THEN emp2.ORIG_HIRE_DATE
    WHEN emp1.DEPT_NUM <> emp2.DEPT_NUM THEN emp2.DEPT_NUM
    WHEN emp1.DEPT_NAME <> emp2.DEPT_NAME THEN emp2.DEPT_NAME
    WHEN emp1.FUNCTIONAL_GROUP <> emp2.FUNCTIONAL_GROUP THEN emp2.FUNCTIONAL_GROUP
    WHEN emp1.PAY_TYPE <> emp2.PAY_TYPE THEN emp2.PAY_TYPE
    WHEN emp1.JOB_TITLE <> emp2.JOB_TITLE THEN emp2.JOB_TITLE
    WHEN emp1.JOB_CODE <> emp2.JOB_CODE THEN emp2.JOB_CODE
    WHEN emp1.JOB_FAMILY <> emp2.JOB_FAMILY THEN emp2.JOB_FAMILY
    WHEN emp1.JOB_GROUP <> emp2.JOB_GROUP THEN emp2.JOB_GROUP
    WHEN emp1.JOB_FUNCTION <> emp2.JOB_FUNCTION THEN emp2.JOB_FUNCTION
    WHEN emp1.WORK_PHONE <> emp2.WORK_PHONE THEN emp2.WORK_PHONE
    WHEN emp1.EMAIL <> emp2.EMAIL THEN emp2.EMAIL
    WHEN emp1.EMP_MGR_LVL <> emp2.EMP_MGR_LVL THEN emp2.EMP_MGR_LVL
    ---WHEN emp1.ANNUAL_RATE <> emp2.ANNUAL_RATE THEN emp2.ANNUAL_RATE
    ---WHEN emp1.HRLY_RATE <> emp2.HRLY_RATE THEN emp2.HRLY_RATE
    WHEN emp1.EMP_TYPE <> emp2.EMP_TYPE THEN emp2.EMP_TYPE
    WHEN emp1.TIME_TYPE <> emp2.TIME_TYPE THEN emp2.TIME_TYPE
    WHEN emp1.EXEMPT <> emp2.EXEMPT THEN emp2.EXEMPT
    ---WHEN emp1.EXPECTED_HOURS <> emp2.EXPECTED_HOURS THEN emp2.EXPECTED_HOURS
    WHEN emp1.COMP_GRADE <> emp2.COMP_GRADE THEN emp2.COMP_GRADE
    WHEN emp1.WORK_LOC_ID <> emp2.WORK_LOC_ID THEN emp2.WORK_LOC_ID
    WHEN emp1.WORK_LOC_NAME <> emp2.WORK_LOC_NAME THEN emp2.WORK_LOC_NAME
    WHEN emp1.WORK_LOC_ADD_1 <> emp2.WORK_LOC_ADD_1 THEN emp2.WORK_LOC_ADD_1
    WHEN emp1.WORK_LOC_ADD_2 <> emp2.WORK_LOC_ADD_2 THEN emp2.WORK_LOC_ADD_2
    WHEN emp1.WORK_LOC_CITY <> emp2.WORK_LOC_CITY THEN emp2.WORK_LOC_CITY
    WHEN emp1.WORK_LOC_STATE <> emp2.WORK_LOC_STATE THEN emp2.WORK_LOC_STATE
    WHEN emp1.WORK_LOC_ZIP <> emp2.WORK_LOC_ZIP THEN emp2.WORK_LOC_ZIP
    WHEN emp1.HOME_ADD_1 <> emp2.HOME_ADD_1 THEN emp2.HOME_ADD_1
    WHEN emp1.HOME_ADD_2 <> emp2.HOME_ADD_2 THEN emp2.HOME_ADD_2
    WHEN emp1.HOME_ADD_CITY <> emp2.HOME_ADD_CITY THEN emp2.HOME_ADD_CITY
    WHEN emp1.HOME_ADD_STATE <> emp2.HOME_ADD_STATE THEN emp2.HOME_ADD_STATE
    WHEN emp1.HOME_ADD_ZIP <> emp2.HOME_ADD_ZIP THEN emp2.HOME_ADD_ZIP
    WHEN emp1.HOME_ADD_ST <> emp2.HOME_ADD_ST THEN emp2.HOME_ADD_ST
    WHEN emp1.LOA <> emp2.LOA THEN emp2.LOA
    WHEN emp1.REPORTS_TO <> emp2.REPORTS_TO THEN emp2.REPORTS_TO
    WHEN emp1.REPORTS_TO_EMP_ID <> emp2.REPORTS_TO_EMP_ID THEN emp2.REPORTS_TO_EMP_ID
    WHEN emp1.REPORTS_TO_EMAIL <> emp2.REPORTS_TO_EMAIL THEN emp2.REPORTS_TO_EMAIL
    WHEN emp1.REPORTS_TO_MGR_LVL <> emp2.REPORTS_TO_MGR_LVL THEN emp2.REPORTS_TO_MGR_LVL
    ---WHEN emp1.emp_rank <> emp2.emp_rank THEN emp2.emp_rank
    ---WHEN emp1.MGR_LVL_NUM <> emp2.MGR_LVL_NUM THEN emp2.MGR_LVL_NUM
    ---WHEN emp1.HIERARCHY_TIER <> emp2.HIERARCHY_TIER THEN emp2.HIERARCHY_TIER
    WHEN emp1.TIER_1 <> emp2.TIER_1 THEN emp2.TIER_1
    WHEN emp1.TIER_2 <> emp2.TIER_2 THEN emp2.TIER_2
    WHEN emp1.TIER_3 <> emp2.TIER_3 THEN emp2.TIER_3
    WHEN emp1.TIER_4 <> emp2.TIER_4 THEN emp2.TIER_4
    WHEN emp1.TIER_5 <> emp2.TIER_5 THEN emp2.TIER_5
    WHEN emp1.TIER_6 <> emp2.TIER_6 THEN emp2.TIER_6
    ---WHEN emp1.Snapshot_FTE <> emp2.Snapshot_FTE THEN emp2.Snapshot_FTE
    ---WHEN emp1.Period_FTE <> emp2.Period_FTE THEN emp2.Period_FTE
    WHEN emp1.Info_Source <> emp2.Info_Source THEN emp2.Info_Source
    ---WHEN emp1.Calendar_period <> emp2.Calendar_period THEN emp2.Calendar_period

    ELSE NULL END

    ELSE NULL
    END

    from emp_main as emp1, emp_main as emp2where emp1.rrd = '2018-06-04' and emp2.rrd = '2018-06-01'and emp1.emp_id = emp2.emp_id;

    from emp_main as emp1, emp_main as emp2
    where emp1.rrd = '2018-06-04' and emp2.rrd = '2018-06-01'
    and emp1.emp_id = emp2.emp_id;

  • small sample of data. period fte column difference is what should show.

    RRDEMP_IDJOB_REQPOS_NUMCANDIDATE_IDEMP_STATUSWORKER_TYPECONT_SUP_NAMEREMOTE_FLAGemp_NAMEFIRST_NAMELAST_NAMERECENT_HIRE_DATEORIG_HIRE_DATEDEPT_NUMDEPT_NAMEFUNCTIONAL_GROUPPAY_TYPESnapshot_FTEPeriod_FTEInfo_SourceCalendar_period
    6/4/2018111NULLP100990NULLActiveEmployeeNULLNULLNAME1FNAME1LNAME111/14/201111/14/2011100Provider RelationsNULLSalary10.0333WD_ACTIVE6/1/2018
    6/4/20181121070864P122752136092ActiveEmployeeNULLNULLNAME2FNAME2LNAME26/24/20136/24/2013101Case ManagementNULLSalary10.0333WD_ACTIVE6/1/2018
    6/4/2018113NULLP112006NULLActiveEmployeeNULLNULLNAME3FNAME3LNAME310/28/201310/28/2013102OperationsNULLSalary10.0333WD_ACTIVE6/1/2018
    6/4/20181141083898P133296703215ActiveEmployeeNULLNULLNAME4FNAME4LNAME41/6/20141/6/2014103Medical ManagementNULLSalary10.0333WD_ACTIVE6/1/2018
    6/1/2018111NULLP100990NULLActiveEmployeeNULLNULLNAME 1FNAME1LNAME111/14/201111/14/2011100Provider RelationsNULLSalary10.1WD_ACTIVE6/1/2018
    6/1/20181121070864P122752136092ActiveEmployeeNULLNULLNAME 2FNAME2LNAME26/24/20136/24/2013101Case ManagementNULLSalary10.1WD_ACTIVE6/1/2018
    6/1/2018113NULLP112006NULLActiveEmployeeNULLNULLNAME 3FNAME3LNAME310/28/201310/28/2013102OperationsNULLSalary10.1WD_ACTIVE6/1/2018
    6/1/20181141083898P133296703215ActiveEmployeeNULLNULLNAME 4FNAME4LNAME41/6/20141/6/2014103Medical ManagementNULLSalary10.1WD_ACTIVE6/1/2018
  • First problem...  You are expecting to use a single CASE statement that involves every column that you are looking to compare, and some of which are dates.
    Whenever you encounter a row where the date is the first column that's different between the two tables, you're going to find that the CASE statement now
    wants to convert ALL the values it has derived so far to date (or possibly datetime) values.   Designing your query to function this way in order to return a value
    from what may be several different data types is a rather bad idea.   One, you'll get this error, because the inclusion of a date value in any part of this comparison
    is going to force the output column to adopt a date (or datetime) data type, due to data type precedence rules.

    Second problem...  Your query is almost unreadable because of formatting.   It's much harder to ferret out a problem with a query of this nature without some
    decent white space hanging around.    Business coding standards often preach the idea that for a SELECT, all the columns being selected must be on their own
    line, or that the width of selected column lines always be limited to some number of characters, typically in the 80 to 100 range.   Some prefer JOINs be segregated
    to a separate line, with the ON clause and each additional condition with it's own line.   Further, those lines are often either indented hierarchically beneath FROM,
    or the JOIN line is lined up with the FROM line, but the ON and all other conditions are indented.   Frequently, all SQL keywords are in UPPER case, with data types
    being all lower case.   Following some portion of those standards for a query you post will usually make it a lot easier for folks here to help out.

    Third problem...  Your sample data is a picture, which would require that someone attempting to run your query transcribe that data into INSERT statements.   Most
    folks here aren't usually so unbusy that they have time for that kind of thing.   Please be sure to provide sample data in a consumable format, which means table
    create statements and INSERT statements for the sample data.   Also critical is to list the expected results, based on the sample data.

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • Thank you Steve. I'm rather new to this, prior using BI apps to report write.  I apologize about the formatting. I am using an existing table derived from many other tables from what I can see. I hope this is a little clearer below.

    1. What would be the preferred solution to compare the same table's columns to itself for two separate dates that have multiple rows for each date the data is ran?
    Would it be to have multiple CASE statements separating out the date, int, numeric, char, varchar?  
    Basically from the above I would be comparing the RRD (run date) data 6/1/18 to the multiple rows of data
    I hope to see something similar to the following if there are differences between the two dates with their columns.
    The difference between the columns of the two dates are the Period_FTE columns and the EMP_STATUS columns
    rrd                  emp_id  Period_FTE  EMP_STATUS     rrd                emp_id   Period_FTE  EMP_STATUS
    2018-06-04    202678  0.0333          On Leave             2018-06-01  202678   0.1000          Active
    2018-06-04    216586  0.0333          On Leave             2018-06-01  216586   0.1000          Active

    2.

    SELECT
    emp1.RRD,emp1.EMP_ID,emp1.JOB_REQ,emp1.POS_NUM,emp1.CANDIDATE_ID,emp1.EMP_STATUS,emp1.WORKER_TYPE,
    emp1.CONT_SUP_NAME,emp1.REMOTE_FLAG,emp1.emp_NAME,emp1.FIRST_NAME,emp1.LAST_NAME,emp1.RECENT_HIRE_DATE,
    emp1.ORIG_HIRE_DATE,emp1.DEPT_NUM,emp1.DEPT_NAME,emp1.FUNCTIONAL_GROUP,emp1.PAY_TYPE,emp1.JOB_TITLE,
    emp1.JOB_CODE,emp1.JOB_FAMILY,emp1.JOB_GROUP,emp1.JOB_FUNCTION,emp1.WORK_PHONE,emp1.EMAIL,emp1.EMP_MGR_LVL,
    emp1.ANNUAL_RATE,emp1.HRLY_RATE,emp1.EMP_TYPE,emp1.TIME_TYPE,emp1.EXEMPT,emp1.EXPECTED_HOURS,emp1.COMP_GRADE,
    emp1.WORK_LOC_ID,emp1.WORK_LOC_NAME,emp1.WORK_LOC_ADD_1,emp1.WORK_LOC_ADD_2,emp1.WORK_LOC_CITY,emp1.WORK_LOC_STATE,
    emp1.WORK_LOC_ZIP,emp1.HOME_ADD_1,emp1.HOME_ADD_2,emp1.HOME_ADD_CITY,emp1.HOME_ADD_STATE,emp1.HOME_ADD_ZIP,
    emp1.HOME_ADD_ST,emp1.LOA,emp1.REPORTS_TO,emp1.REPORTS_TO_EMP_ID,emp1.REPORTS_TO_EMAIL,emp1.REPORTS_TO_MGR_LVL,
    emp1.emp_rank,emp1.MGR_LVL_NUM,emp1.HIERARCHY_TIER,emp1.TIER_1,emp1.TIER_2,emp1.TIER_3,emp1.TIER_4,emp1.TIER_5,
    emp1.TIER_6,emp1.Snapshot_FTE,emp1.Period_FTE,emp1.Info_Source,emp1.Calendar_period,
    emp2.RRD,emp2.EMP_ID,emp2.JOB_REQ, emp2.POS_NUM,emp2.CANDIDATE_ID,emp2.EMP_STATUS,emp2.WORKER_TYPE,emp2.CONT_SUP_NAME,
    emp2.REMOTE_FLAG,emp2.emp_NAME,emp2.FIRST_NAME,emp2.LAST_NAME,emp2.RECENT_HIRE_DATE,emp2.ORIG_HIRE_DATE,emp2.DEPT_NUM,
    emp2.DEPT_NAME,emp2.FUNCTIONAL_GROUP,emp2.PAY_TYPE,emp2.JOB_TITLE,emp2.JOB_CODE,emp2.JOB_FAMILY,
    emp2.JOB_GROUP,emp2.JOB_FUNCTION,emp2.WORK_PHONE,emp2.EMAIL,emp2.EMP_MGR_LVL,emp2.ANNUAL_RATE,
    emp2.HRLY_RATE,emp2.EMP_TYPE,emp2.TIME_TYPE,emp2.EXEMPT,emp2.EXPECTED_HOURS,emp2.COMP_GRADE,
    emp2.WORK_LOC_ID,emp2.WORK_LOC_NAME,emp2.WORK_LOC_ADD_1,emp2.WORK_LOC_ADD_2,emp2.WORK_LOC_CITY,
    emp2.WORK_LOC_STATE,emp2.WORK_LOC_ZIP,emp2.HOME_ADD_1,emp2.HOME_ADD_2,emp2.HOME_ADD_CITY,
    emp2.HOME_ADD_STATE,emp2.HOME_ADD_ZIP,emp2.HOME_ADD_ST,emp2.LOA,emp2.REPORTS_TO,emp2.REPORTS_TO_EMP_ID,
    emp2.REPORTS_TO_EMAIL,emp2.REPORTS_TO_MGR_LVL,emp2.emp_rank,emp2.MGR_LVL_NUM,emp2.HIERARCHY_TIER,
    emp2.TIER_1,emp2.TIER_2,emp2.TIER_3,emp2.TIER_4,emp2.TIER_5,emp2.TIER_6,emp2.Snapshot_FTE,emp2.Period_FTE,emp2.Info_Source,emp2.Calendar_period,

    CASE

    WHEN emp1.JOB_REQ <> emp2.JOB_REQ THEN emp2.JOB_REQ
    WHEN emp1.POS_NUM <> emp2.POS_NUM THEN emp2.POS_NUM
    WHEN emp1.CANDIDATE_ID <> emp2.CANDIDATE_ID THEN emp2.CANDIDATE_ID
    WHEN emp1.EMP_STATUS <> emp2.EMP_STATUS THEN emp2.EMP_STATUS
    WHEN emp1.WORKER_TYPE <> emp2.WORKER_TYPE THEN emp2.WORKER_TYPE
    WHEN emp1.CONT_SUP_NAME <> emp2.CONT_SUP_NAME THEN emp2.CONT_SUP_NAME
    WHEN emp1.REMOTE_FLAG <> emp2.REMOTE_FLAG THEN emp2.REMOTE_FLAG
    WHEN emp1.emp_NAME <> emp2.emp_NAME THEN emp2.emp_NAME
    WHEN emp1.FIRST_NAME <> emp2.FIRST_NAME THEN emp2.FIRST_NAME
    WHEN emp1.LAST_NAME <> emp2.LAST_NAME THEN emp2.LAST_NAME
    ---WHEN emp1.RECENT_HIRE_DATE <> emp2.RECENT_HIRE_DATE THEN emp2.RECENT_HIRE_DATE
    ---WHEN emp1.ORIG_HIRE_DATE <> emp2.ORIG_HIRE_DATE THEN emp2.ORIG_HIRE_DATE
    WHEN emp1.DEPT_NUM <> emp2.DEPT_NUM THEN emp2.DEPT_NUM
    WHEN emp1.DEPT_NAME <> emp2.DEPT_NAME THEN emp2.DEPT_NAME
    WHEN emp1.FUNCTIONAL_GROUP <> emp2.FUNCTIONAL_GROUP THEN emp2.FUNCTIONAL_GROUP
    WHEN emp1.PAY_TYPE <> emp2.PAY_TYPE THEN emp2.PAY_TYPE
    WHEN emp1.JOB_TITLE <> emp2.JOB_TITLE THEN emp2.JOB_TITLE
    WHEN emp1.JOB_CODE <> emp2.JOB_CODE THEN emp2.JOB_CODE
    WHEN emp1.JOB_FAMILY <> emp2.JOB_FAMILY THEN emp2.JOB_FAMILY
    WHEN emp1.JOB_GROUP <> emp2.JOB_GROUP THEN emp2.JOB_GROUP
    WHEN emp1.JOB_FUNCTION <> emp2.JOB_FUNCTION THEN emp2.JOB_FUNCTION
    WHEN emp1.WORK_PHONE <> emp2.WORK_PHONE THEN emp2.WORK_PHONE
    WHEN emp1.EMAIL <> emp2.EMAIL THEN emp2.EMAIL
    WHEN emp1.EMP_MGR_LVL <> emp2.EMP_MGR_LVL THEN emp2.EMP_MGR_LVL
    ---WHEN emp1.ANNUAL_RATE <> emp2.ANNUAL_RATE THEN emp2.ANNUAL_RATE
    ---WHEN emp1.HRLY_RATE <> emp2.HRLY_RATE THEN emp2.HRLY_RATE
    WHEN emp1.EMP_TYPE <> emp2.EMP_TYPE THEN emp2.EMP_TYPE
    WHEN emp1.TIME_TYPE <> emp2.TIME_TYPE THEN emp2.TIME_TYPE
    WHEN emp1.EXEMPT <> emp2.EXEMPT THEN emp2.EXEMPT
    ---WHEN emp1.EXPECTED_HOURS <> emp2.EXPECTED_HOURS THEN emp2.EXPECTED_HOURS
    WHEN emp1.COMP_GRADE <> emp2.COMP_GRADE THEN emp2.COMP_GRADE
    WHEN emp1.WORK_LOC_ID <> emp2.WORK_LOC_ID THEN emp2.WORK_LOC_ID
    WHEN emp1.WORK_LOC_NAME <> emp2.WORK_LOC_NAME THEN emp2.WORK_LOC_NAME
    WHEN emp1.WORK_LOC_ADD_1 <> emp2.WORK_LOC_ADD_1 THEN emp2.WORK_LOC_ADD_1
    WHEN emp1.WORK_LOC_ADD_2 <> emp2.WORK_LOC_ADD_2 THEN emp2.WORK_LOC_ADD_2
    WHEN emp1.WORK_LOC_CITY <> emp2.WORK_LOC_CITY THEN emp2.WORK_LOC_CITY
    WHEN emp1.WORK_LOC_STATE <> emp2.WORK_LOC_STATE THEN emp2.WORK_LOC_STATE
    WHEN emp1.WORK_LOC_ZIP <> emp2.WORK_LOC_ZIP THEN emp2.WORK_LOC_ZIP
    WHEN emp1.HOME_ADD_1 <> emp2.HOME_ADD_1 THEN emp2.HOME_ADD_1
    WHEN emp1.HOME_ADD_2 <> emp2.HOME_ADD_2 THEN emp2.HOME_ADD_2
    WHEN emp1.HOME_ADD_CITY <> emp2.HOME_ADD_CITY THEN emp2.HOME_ADD_CITY
    WHEN emp1.HOME_ADD_STATE <> emp2.HOME_ADD_STATE THEN emp2.HOME_ADD_STATE
    WHEN emp1.HOME_ADD_ZIP <> emp2.HOME_ADD_ZIP THEN emp2.HOME_ADD_ZIP
    WHEN emp1.HOME_ADD_ST <> emp2.HOME_ADD_ST THEN emp2.HOME_ADD_ST
    WHEN emp1.LOA <> emp2.LOA THEN emp2.LOA
    WHEN emp1.REPORTS_TO <> emp2.REPORTS_TO THEN emp2.REPORTS_TO
    WHEN emp1.REPORTS_TO_EMP_ID <> emp2.REPORTS_TO_EMP_ID THEN emp2.REPORTS_TO_EMP_ID
    WHEN emp1.REPORTS_TO_EMAIL <> emp2.REPORTS_TO_EMAIL THEN emp2.REPORTS_TO_EMAIL
    WHEN emp1.REPORTS_TO_MGR_LVL <> emp2.REPORTS_TO_MGR_LVL THEN emp2.REPORTS_TO_MGR_LVL
    ---WHEN emp1.emp_rank <> emp2.emp_rank THEN emp2.emp_rank
    ---WHEN emp1.MGR_LVL_NUM <> emp2.MGR_LVL_NUM THEN emp2.MGR_LVL_NUM
    ---WHEN emp1.HIERARCHY_TIER <> emp2.HIERARCHY_TIER THEN emp2.HIERARCHY_TIER
    WHEN emp1.TIER_1 <> emp2.TIER_1 THEN emp2.TIER_1
    WHEN emp1.TIER_2 <> emp2.TIER_2 THEN emp2.TIER_2
    WHEN emp1.TIER_3 <> emp2.TIER_3 THEN emp2.TIER_3
    WHEN emp1.TIER_4 <> emp2.TIER_4 THEN emp2.TIER_4
    WHEN emp1.TIER_5 <> emp2.TIER_5 THEN emp2.TIER_5
    WHEN emp1.TIER_6 <> emp2.TIER_6 THEN emp2.TIER_6
    ---WHEN emp1.Snapshot_FTE <> emp2.Snapshot_FTE THEN emp2.Snapshot_FTE
    ---WHEN emp1.Period_FTE <> emp2.Period_FTE THEN emp2.Period_FTE
    WHEN emp1.Info_Source <> emp2.Info_Source THEN emp2.Info_Source
    ---WHEN emp1.Calendar_period <> emp2.Calendar_period THEN emp2.Calendar_period

    ELSE NULL
    END

    from emp_main as emp1, emp_main as emp2
    where emp1.rrd = '2018-06-04' and emp2.rrd = '2018-06-01'
    and emp1.emp_id = emp2.emp_id;

    3.

    create table emp_main

    (

    RRD date,

    EMP_ID varchar(200) COLLATE SQL_Latin1_General_CP1_CS_AS,

    JOB_REQ varchar(200) COLLATE SQL_Latin1_General_CP1_CS_AS,

    POS_NUM varchar(200) COLLATE SQL_Latin1_General_CP1_CS_AS,

    CANDIDATE_ID varchar(200) COLLATE SQL_Latin1_General_CP1_CS_AS,

    EMP_STATUS varchar(200) COLLATE SQL_Latin1_General_CP1_CS_AS,

    WORKER_TYPE varchar(200) COLLATE SQL_Latin1_General_CP1_CS_AS,

    CONT_SUP_NAME varchar(200) COLLATE SQL_Latin1_General_CP1_CS_AS,

    --POS_EMP_TYPE varchar(200) COLLATE SQL_Latin1_General_CP1_CS_AS,

    --FIELD_FLAG varchar(200) COLLATE SQL_Latin1_General_CP1_CS_AS,

    REMOTE_FLAG varchar(200) COLLATE SQL_Latin1_General_CP1_CS_AS,

    emp_NAME varchar(200) COLLATE SQL_Latin1_General_CP1_CS_AS,

    FIRST_NAME varchar(200) COLLATE SQL_Latin1_General_CP1_CS_AS,

    LAST_NAME varchar(200) COLLATE SQL_Latin1_General_CP1_CS_AS,

    RECENT_HIRE_DATE date,

    ORIG_HIRE_DATE date,

    --LOB varchar(200) COLLATE SQL_Latin1_General_CP1_CS_AS,

    DEPT_NUM varchar(200) COLLATE SQL_Latin1_General_CP1_CS_AS,

    DEPT_NAME varchar(200) COLLATE SQL_Latin1_General_CP1_CS_AS,

    FUNCTIONAL_GROUP varchar(200) COLLATE SQL_Latin1_General_CP1_CS_AS, -- NEW

    PAY_TYPE varchar(200) COLLATE SQL_Latin1_General_CP1_CS_AS,

    JOB_TITLE varchar(200) COLLATE SQL_Latin1_General_CP1_CS_AS,

    JOB_CODE varchar(200) COLLATE SQL_Latin1_General_CP1_CS_AS,

    JOB_FAMILY varchar(200) COLLATE SQL_Latin1_General_CP1_CS_AS,

    JOB_GROUP varchar(200) COLLATE SQL_Latin1_General_CP1_CS_AS,

    JOB_FUNCTION varchar(200) COLLATE SQL_Latin1_General_CP1_CS_AS, -- NEW

    WORK_PHONE varchar(200) COLLATE SQL_Latin1_General_CP1_CS_AS,

    EMAIL varchar(200) COLLATE SQL_Latin1_General_CP1_CS_AS,

    --OFFICER varchar(200) COLLATE SQL_Latin1_General_CP1_CS_AS,

    EMP_MGR_LVL varchar(200) COLLATE SQL_Latin1_General_CP1_CS_AS,

    ANNUAL_RATE NUMERIC(18,4),

    HRLY_RATE NUMERIC(18,4),

    EMP_TYPE varchar(200) COLLATE SQL_Latin1_General_CP1_CS_AS,

    TIME_TYPE varchar(200) COLLATE SQL_Latin1_General_CP1_CS_AS,

    EXEMPT varchar(200) COLLATE SQL_Latin1_General_CP1_CS_AS,

    EXPECTED_HOURS NUMERIC(18,4),

    COMP_GRADE varchar(200) COLLATE SQL_Latin1_General_CP1_CS_AS,

    WORK_LOC_ID varchar(200) COLLATE SQL_Latin1_General_CP1_CS_AS,

    WORK_LOC_NAME varchar(200) COLLATE SQL_Latin1_General_CP1_CS_AS,

    WORK_LOC_ADD_1 varchar(200) COLLATE SQL_Latin1_General_CP1_CS_AS,

    WORK_LOC_ADD_2 varchar(200) COLLATE SQL_Latin1_General_CP1_CS_AS,

    WORK_LOC_CITY varchar(200) COLLATE SQL_Latin1_General_CP1_CS_AS,

    WORK_LOC_STATE varchar(200) COLLATE SQL_Latin1_General_CP1_CS_AS,

    WORK_LOC_ZIP varchar(200) COLLATE SQL_Latin1_General_CP1_CS_AS,

    HOME_ADD_1 varchar(200) COLLATE SQL_Latin1_General_CP1_CS_AS,

    HOME_ADD_2 varchar(200) COLLATE SQL_Latin1_General_CP1_CS_AS,

    HOME_ADD_CITY varchar(200) COLLATE SQL_Latin1_General_CP1_CS_AS,

    HOME_ADD_STATE varchar(200) COLLATE SQL_Latin1_General_CP1_CS_AS,

    HOME_ADD_ZIP varchar(200) COLLATE SQL_Latin1_General_CP1_CS_AS,

    HOME_ADD_ST varchar(200) COLLATE SQL_Latin1_General_CP1_CS_AS,

    --COST_CENTER_NUM varchar(200) COLLATE SQL_Latin1_General_CP1_CS_AS,

    --COST_CENTER_NAME varchar(200) COLLATE SQL_Latin1_General_CP1_CS_AS,

    --COST_CENTER_REF varchar(200) COLLATE SQL_Latin1_General_CP1_CS_AS,

    --COMPANY varchar(200) COLLATE SQL_Latin1_General_CP1_CS_AS,

    LOA varchar(200) COLLATE SQL_Latin1_General_CP1_CS_AS,

    REPORTS_TO varchar(200) COLLATE SQL_Latin1_General_CP1_CS_AS,

    REPORTS_TO_EMP_ID varchar(200) COLLATE SQL_Latin1_General_CP1_CS_AS,

    REPORTS_TO_EMAIL varchar(200) COLLATE SQL_Latin1_General_CP1_CS_AS,

    REPORTS_TO_MGR_LVL varchar(200) COLLATE SQL_Latin1_General_CP1_CS_AS,

    emp_rank NUMERIC(18,4),

    MGR_LVL_NUM NUMERIC(18,4),

    HIERARCHY_TIER NUMERIC(18,4),

    TIER_1 varchar(200) COLLATE SQL_Latin1_General_CP1_CS_AS,

    TIER_2 varchar(200) COLLATE SQL_Latin1_General_CP1_CS_AS,

    TIER_3 varchar(200) COLLATE SQL_Latin1_General_CP1_CS_AS,

    TIER_4 varchar(200) COLLATE SQL_Latin1_General_CP1_CS_AS,

    TIER_5 varchar(200) COLLATE SQL_Latin1_General_CP1_CS_AS,

    TIER_6 varchar(200) COLLATE SQL_Latin1_General_CP1_CS_AS,

    --CEO varchar(200) COLLATE SQL_Latin1_General_CP1_CS_AS,

    --VP varchar(200) COLLATE SQL_Latin1_General_CP1_CS_AS,

    --Sr_Dir varchar(200) COLLATE SQL_Latin1_General_CP1_CS_AS,

    --Dir varchar(200) COLLATE SQL_Latin1_General_CP1_CS_AS,

    --Sr_Mgr varchar(200) COLLATE SQL_Latin1_General_CP1_CS_AS,

    --Mgr varchar(200) COLLATE SQL_Latin1_General_CP1_CS_AS,

    --Supv varchar(200) COLLATE SQL_Latin1_General_CP1_CS_AS,

    Snapshot_FTE NUMERIC(18,4),

    Period_FTE NUMERIC(18,4),

    Info_Source varchar(200) COLLATE SQL_Latin1_General_CP1_CS_AS,

    Calendar_period date

    )

    ;

    insert into emp_main

    (

    RRD,

    EMP_ID,

    POS_NUM,

    EMP_STATUS,

    WORKER_TYPE,

    CONT_SUP_NAME,

    --POS_EMP_TYPE,

    --FIELD_FLAG,

    REMOTE_FLAG,

    emp_NAME,

    FIRST_NAME,

    LAST_NAME,

    RECENT_HIRE_DATE,

    ORIG_HIRE_DATE,

    --LOB,

    DEPT_NUM,

    DEPT_NAME,

    PAY_TYPE,

    JOB_TITLE,

    JOB_CODE,

    JOB_FAMILY,

    WORK_PHONE,

    EMAIL,

    --OFFICER,

    EMP_MGR_LVL,

    ANNUAL_RATE,

    HRLY_RATE,

    EMP_TYPE,

    TIME_TYPE,

    EXEMPT,

    EXPECTED_HOURS,

    COMP_GRADE,

    WORK_LOC_ID,

    WORK_LOC_NAME,

    WORK_LOC_ADD_1,

    WORK_LOC_ADD_2,

    WORK_LOC_CITY,

    WORK_LOC_STATE,

    WORK_LOC_ZIP,

    HOME_ADD_1,

    HOME_ADD_2,

    HOME_ADD_CITY,

    HOME_ADD_STATE,

    HOME_ADD_ZIP,

    HOME_ADD_ST,

    --COST_CENTER_NUM,

    --COST_CENTER_NAME,

    --COST_CENTER_REF,

    --COMPANY,

    LOA,

    REPORTS_TO,

    REPORTS_TO_EMP_ID,

    REPORTS_TO_EMAIL,

    REPORTS_TO_MGR_LVL,

    Emp_rank,

    MGR_LVL_NUM,

    --CEO

    --VP

    --Sr_Dir

    --Dir

    --Sr_Mgr

    --Mgr

    --Supv

    Snapshot_FTE,

    Period_FTE,

    Info_Source,

    Calendar_period

    )

  • Just out of curiosity does the following work?


    DECLARE @Date1 DATE = '2018-06-04'
            , @Date2 DATE = '2018-06-01';

    WITH DateCriteria1 AS (
    SELECT
      [emp1].[RRD]
      , [emp1].[EMP_ID]
      , [emp1].[JOB_REQ]
      , [emp1].[POS_NUM]
      , [emp1].[CANDIDATE_ID]
      , [emp1].[EMP_STATUS]
      , [emp1].[WORKER_TYPE]
      , [emp1].[CONT_SUP_NAME]
      , [emp1].[REMOTE_FLAG]
      , [emp1].[emp_NAME]
      , [emp1].[FIRST_NAME]
      , [emp1].[LAST_NAME]
      , [emp1].[RECENT_HIRE_DATE]
      , [emp1].[ORIG_HIRE_DATE]
      , [emp1].[DEPT_NUM]
      , [emp1].[DEPT_NAME]
      , [emp1].[FUNCTIONAL_GROUP]
      , [emp1].[PAY_TYPE]
      , [emp1].[JOB_TITLE]
      , [emp1].[JOB_CODE]
      , [emp1].[JOB_FAMILY]
      , [emp1].[JOB_GROUP]
      , [emp1].[JOB_FUNCTION]
      , [emp1].[WORK_PHONE]
      , [emp1].
      , [emp1].[EMP_MGR_LVL]
      , [emp1].[ANNUAL_RATE]
      , [emp1].[HRLY_RATE]
      , [emp1].[EMP_TYPE]
      , [emp1].[TIME_TYPE]
      , [emp1].[EXEMPT]
      , [emp1].[EXPECTED_HOURS]
      , [emp1].[COMP_GRADE]
      , [emp1].[WORK_LOC_ID]
      , [emp1].[WORK_LOC_NAME]
      , [emp1].[WORK_LOC_ADD_1]
      , [emp1].[WORK_LOC_ADD_2]
      , [emp1].[WORK_LOC_CITY]
      , [emp1].[WORK_LOC_STATE]
      , [emp1].[WORK_LOC_ZIP]
      , [emp1].[HOME_ADD_1]
      , [emp1].[HOME_ADD_2]
      , [emp1].[HOME_ADD_CITY]
      , [emp1].[HOME_ADD_STATE]
      , [emp1].[HOME_ADD_ZIP]
      , [emp1].[HOME_ADD_ST]
      , [emp1].[LOA]
      , [emp1].[REPORTS_TO]
      , [emp1].[REPORTS_TO_EMP_ID]
      , [emp1].[REPORTS_TO_EMAIL]
      , [emp1].[REPORTS_TO_MGR_LVL]
      , [emp1].[emp_rank]
      , [emp1].[MGR_LVL_NUM]
      , [emp1].[HIERARCHY_TIER]
      , [emp1].[TIER_1]
      , [emp1].[TIER_2]
      , [emp1].[TIER_3]
      , [emp1].[TIER_4]
      , [emp1].[TIER_5]
      , [emp1].[TIER_6]
      , [emp1].[Snapshot_FTE]
      , [emp1].[Period_FTE]
      , [emp1].[Info_Source]
      , [emp1].[Calendar_period]
    FROM
      [emp_main]            AS [emp1]
    WHERE
      [emp1].[rrd]     = @Date1
    ),
    DateCriteria2 AS (
    SELECT
      [emp1].[RRD]
      , [emp1].[EMP_ID]
      , [emp1].[JOB_REQ]
      , [emp1].[POS_NUM]
      , [emp1].[CANDIDATE_ID]
      , [emp1].[EMP_STATUS]
      , [emp1].[WORKER_TYPE]
      , [emp1].[CONT_SUP_NAME]
      , [emp1].[REMOTE_FLAG]
      , [emp1].[emp_NAME]
      , [emp1].[FIRST_NAME]
      , [emp1].[LAST_NAME]
      , [emp1].[RECENT_HIRE_DATE]
      , [emp1].[ORIG_HIRE_DATE]
      , [emp1].[DEPT_NUM]
      , [emp1].[DEPT_NAME]
      , [emp1].[FUNCTIONAL_GROUP]
      , [emp1].[PAY_TYPE]
      , [emp1].[JOB_TITLE]
      , [emp1].[JOB_CODE]
      , [emp1].[JOB_FAMILY]
      , [emp1].[JOB_GROUP]
      , [emp1].[JOB_FUNCTION]
      , [emp1].[WORK_PHONE]
      , [emp1].
      , [emp1].[EMP_MGR_LVL]
      , [emp1].[ANNUAL_RATE]
      , [emp1].[HRLY_RATE]
      , [emp1].[EMP_TYPE]
      , [emp1].[TIME_TYPE]
      , [emp1].[EXEMPT]
      , [emp1].[EXPECTED_HOURS]
      , [emp1].[COMP_GRADE]
      , [emp1].[WORK_LOC_ID]
      , [emp1].[WORK_LOC_NAME]
      , [emp1].[WORK_LOC_ADD_1]
      , [emp1].[WORK_LOC_ADD_2]
      , [emp1].[WORK_LOC_CITY]
      , [emp1].[WORK_LOC_STATE]
      , [emp1].[WORK_LOC_ZIP]
      , [emp1].[HOME_ADD_1]
      , [emp1].[HOME_ADD_2]
      , [emp1].[HOME_ADD_CITY]
      , [emp1].[HOME_ADD_STATE]
      , [emp1].[HOME_ADD_ZIP]
      , [emp1].[HOME_ADD_ST]
      , [emp1].[LOA]
      , [emp1].[REPORTS_TO]
      , [emp1].[REPORTS_TO_EMP_ID]
      , [emp1].[REPORTS_TO_EMAIL]
      , [emp1].[REPORTS_TO_MGR_LVL]
      , [emp1].[emp_rank]
      , [emp1].[MGR_LVL_NUM]
      , [emp1].[HIERARCHY_TIER]
      , [emp1].[TIER_1]
      , [emp1].[TIER_2]
      , [emp1].[TIER_3]
      , [emp1].[TIER_4]
      , [emp1].[TIER_5]
      , [emp1].[TIER_6]
      , [emp1].[Snapshot_FTE]
      , [emp1].[Period_FTE]
      , [emp1].[Info_Source]
      , [emp1].[Calendar_period]
    FROM
      [emp_main]            AS [emp1]
    WHERE
      [emp1].[rrd]     = @Date2
    ),
    Diff1to2 AS (
    SELECT
      [emp1].[EMP_ID]
      , [emp1].[JOB_REQ]
      , [emp1].[POS_NUM]
      , [emp1].[CANDIDATE_ID]
      , [emp1].[EMP_STATUS]
      , [emp1].[WORKER_TYPE]
      , [emp1].[CONT_SUP_NAME]
      , [emp1].[REMOTE_FLAG]
      , [emp1].[emp_NAME]
      , [emp1].[FIRST_NAME]
      , [emp1].[LAST_NAME]
      , [emp1].[RECENT_HIRE_DATE]
      , [emp1].[ORIG_HIRE_DATE]
      , [emp1].[DEPT_NUM]
      , [emp1].[DEPT_NAME]
      , [emp1].[FUNCTIONAL_GROUP]
      , [emp1].[PAY_TYPE]
      , [emp1].[JOB_TITLE]
      , [emp1].[JOB_CODE]
      , [emp1].[JOB_FAMILY]
      , [emp1].[JOB_GROUP]
      , [emp1].[JOB_FUNCTION]
      , [emp1].[WORK_PHONE]
      , [emp1].
      , [emp1].[EMP_MGR_LVL]
      , [emp1].[ANNUAL_RATE]
      , [emp1].[HRLY_RATE]
      , [emp1].[EMP_TYPE]
      , [emp1].[TIME_TYPE]
      , [emp1].[EXEMPT]
      , [emp1].[EXPECTED_HOURS]
      , [emp1].[COMP_GRADE]
      , [emp1].[WORK_LOC_ID]
      , [emp1].[WORK_LOC_NAME]
      , [emp1].[WORK_LOC_ADD_1]
      , [emp1].[WORK_LOC_ADD_2]
      , [emp1].[WORK_LOC_CITY]
      , [emp1].[WORK_LOC_STATE]
      , [emp1].[WORK_LOC_ZIP]
      , [emp1].[HOME_ADD_1]
      , [emp1].[HOME_ADD_2]
      , [emp1].[HOME_ADD_CITY]
      , [emp1].[HOME_ADD_STATE]
      , [emp1].[HOME_ADD_ZIP]
      , [emp1].[HOME_ADD_ST]
      , [emp1].[LOA]
      , [emp1].[REPORTS_TO]
      , [emp1].[REPORTS_TO_EMP_ID]
      , [emp1].[REPORTS_TO_EMAIL]
      , [emp1].[REPORTS_TO_MGR_LVL]
      , [emp1].[emp_rank]
      , [emp1].[MGR_LVL_NUM]
      , [emp1].[HIERARCHY_TIER]
      , [emp1].[TIER_1]
      , [emp1].[TIER_2]
      , [emp1].[TIER_3]
      , [emp1].[TIER_4]
      , [emp1].[TIER_5]
      , [emp1].[TIER_6]
      , [emp1].[Snapshot_FTE]
      , [emp1].[Period_FTE]
      , [emp1].[Info_Source]
      , [emp1].[Calendar_period]
    FROM
      [DateCriteria1] AS [emp1]
    EXCEPT
    SELECT
      [emp2].[EMP_ID]
      , [emp2].[JOB_REQ]
      , [emp2].[POS_NUM]
      , [emp2].[CANDIDATE_ID]
      , [emp2].[EMP_STATUS]
      , [emp2].[WORKER_TYPE]
      , [emp2].[CONT_SUP_NAME]
      , [emp2].[REMOTE_FLAG]
      , [emp2].[emp_NAME]
      , [emp2].[FIRST_NAME]
      , [emp2].[LAST_NAME]
      , [emp2].[RECENT_HIRE_DATE]
      , [emp2].[ORIG_HIRE_DATE]
      , [emp2].[DEPT_NUM]
      , [emp2].[DEPT_NAME]
      , [emp2].[FUNCTIONAL_GROUP]
      , [emp2].[PAY_TYPE]
      , [emp2].[JOB_TITLE]
      , [emp2].[JOB_CODE]
      , [emp2].[JOB_FAMILY]
      , [emp2].[JOB_GROUP]
      , [emp2].[JOB_FUNCTION]
      , [emp2].[WORK_PHONE]
      , [emp2].
      , [emp2].[EMP_MGR_LVL]
      , [emp2].[ANNUAL_RATE]
      , [emp2].[HRLY_RATE]
      , [emp2].[EMP_TYPE]
      , [emp2].[TIME_TYPE]
      , [emp2].[EXEMPT]
      , [emp2].[EXPECTED_HOURS]
      , [emp2].[COMP_GRADE]
      , [emp2].[WORK_LOC_ID]
      , [emp2].[WORK_LOC_NAME]
      , [emp2].[WORK_LOC_ADD_1]
      , [emp2].[WORK_LOC_ADD_2]
      , [emp2].[WORK_LOC_CITY]
      , [emp2].[WORK_LOC_STATE]
      , [emp2].[WORK_LOC_ZIP]
      , [emp2].[HOME_ADD_1]
      , [emp2].[HOME_ADD_2]
      , [emp2].[HOME_ADD_CITY]
      , [emp2].[HOME_ADD_STATE]
      , [emp2].[HOME_ADD_ZIP]
      , [emp2].[HOME_ADD_ST]
      , [emp2].[LOA]
      , [emp2].[REPORTS_TO]
      , [emp2].[REPORTS_TO_EMP_ID]
      , [emp2].[REPORTS_TO_EMAIL]
      , [emp2].[REPORTS_TO_MGR_LVL]
      , [emp2].[emp_rank]
      , [emp2].[MGR_LVL_NUM]
      , [emp2].[HIERARCHY_TIER]
      , [emp2].[TIER_1]
      , [emp2].[TIER_2]
      , [emp2].[TIER_3]
      , [emp2].[TIER_4]
      , [emp2].[TIER_5]
      , [emp2].[TIER_6]
      , [emp2].[Snapshot_FTE]
      , [emp2].[Period_FTE]
      , [emp2].[Info_Source]
      , [emp2].[Calendar_period]
    FROM
      [DateCriteria2] AS [emp2]
    ),
    Diff2to1 AS (
    SELECT
      [emp2].[EMP_ID]
      , [emp2].[JOB_REQ]
      , [emp2].[POS_NUM]
      , [emp2].[CANDIDATE_ID]
      , [emp2].[EMP_STATUS]
      , [emp2].[WORKER_TYPE]
      , [emp2].[CONT_SUP_NAME]
      , [emp2].[REMOTE_FLAG]
      , [emp2].[emp_NAME]
      , [emp2].[FIRST_NAME]
      , [emp2].[LAST_NAME]
      , [emp2].[RECENT_HIRE_DATE]
      , [emp2].[ORIG_HIRE_DATE]
      , [emp2].[DEPT_NUM]
      , [emp2].[DEPT_NAME]
      , [emp2].[FUNCTIONAL_GROUP]
      , [emp2].[PAY_TYPE]
      , [emp2].[JOB_TITLE]
      , [emp2].[JOB_CODE]
      , [emp2].[JOB_FAMILY]
      , [emp2].[JOB_GROUP]
      , [emp2].[JOB_FUNCTION]
      , [emp2].[WORK_PHONE]
      , [emp2].
      , [emp2].[EMP_MGR_LVL]
      , [emp2].[ANNUAL_RATE]
      , [emp2].[HRLY_RATE]
      , [emp2].[EMP_TYPE]
      , [emp2].[TIME_TYPE]
      , [emp2].[EXEMPT]
      , [emp2].[EXPECTED_HOURS]
      , [emp2].[COMP_GRADE]
      , [emp2].[WORK_LOC_ID]
      , [emp2].[WORK_LOC_NAME]
      , [emp2].[WORK_LOC_ADD_1]
      , [emp2].[WORK_LOC_ADD_2]
      , [emp2].[WORK_LOC_CITY]
      , [emp2].[WORK_LOC_STATE]
      , [emp2].[WORK_LOC_ZIP]
      , [emp2].[HOME_ADD_1]
      , [emp2].[HOME_ADD_2]
      , [emp2].[HOME_ADD_CITY]
      , [emp2].[HOME_ADD_STATE]
      , [emp2].[HOME_ADD_ZIP]
      , [emp2].[HOME_ADD_ST]
      , [emp2].[LOA]
      , [emp2].[REPORTS_TO]
      , [emp2].[REPORTS_TO_EMP_ID]
      , [emp2].[REPORTS_TO_EMAIL]
      , [emp2].[REPORTS_TO_MGR_LVL]
      , [emp2].[emp_rank]
      , [emp2].[MGR_LVL_NUM]
      , [emp2].[HIERARCHY_TIER]
      , [emp2].[TIER_1]
      , [emp2].[TIER_2]
      , [emp2].[TIER_3]
      , [emp2].[TIER_4]
      , [emp2].[TIER_5]
      , [emp2].[TIER_6]
      , [emp2].[Snapshot_FTE]
      , [emp2].[Period_FTE]
      , [emp2].[Info_Source]
      , [emp2].[Calendar_period]
    FROM
      [DateCriteria2] AS [emp2]
    EXCEPT
    SELECT
      [emp1].[EMP_ID]
      , [emp1].[JOB_REQ]
      , [emp1].[POS_NUM]
      , [emp1].[CANDIDATE_ID]
      , [emp1].[EMP_STATUS]
      , [emp1].[WORKER_TYPE]
      , [emp1].[CONT_SUP_NAME]
      , [emp1].[REMOTE_FLAG]
      , [emp1].[emp_NAME]
      , [emp1].[FIRST_NAME]
      , [emp1].[LAST_NAME]
      , [emp1].[RECENT_HIRE_DATE]
      , [emp1].[ORIG_HIRE_DATE]
      , [emp1].[DEPT_NUM]
      , [emp1].[DEPT_NAME]
      , [emp1].[FUNCTIONAL_GROUP]
      , [emp1].[PAY_TYPE]
      , [emp1].[JOB_TITLE]
      , [emp1].[JOB_CODE]
      , [emp1].[JOB_FAMILY]
      , [emp1].[JOB_GROUP]
      , [emp1].[JOB_FUNCTION]
      , [emp1].[WORK_PHONE]
      , [emp1].
      , [emp1].[EMP_MGR_LVL]
      , [emp1].[ANNUAL_RATE]
      , [emp1].[HRLY_RATE]
      , [emp1].[EMP_TYPE]
      , [emp1].[TIME_TYPE]
      , [emp1].[EXEMPT]
      , [emp1].[EXPECTED_HOURS]
      , [emp1].[COMP_GRADE]
      , [emp1].[WORK_LOC_ID]
      , [emp1].[WORK_LOC_NAME]
      , [emp1].[WORK_LOC_ADD_1]
      , [emp1].[WORK_LOC_ADD_2]
      , [emp1].[WORK_LOC_CITY]
      , [emp1].[WORK_LOC_STATE]
      , [emp1].[WORK_LOC_ZIP]
      , [emp1].[HOME_ADD_1]
      , [emp1].[HOME_ADD_2]
      , [emp1].[HOME_ADD_CITY]
      , [emp1].[HOME_ADD_STATE]
      , [emp1].[HOME_ADD_ZIP]
      , [emp1].[HOME_ADD_ST]
      , [emp1].[LOA]
      , [emp1].[REPORTS_TO]
      , [emp1].[REPORTS_TO_EMP_ID]
      , [emp1].[REPORTS_TO_EMAIL]
      , [emp1].[REPORTS_TO_MGR_LVL]
      , [emp1].[emp_rank]
      , [emp1].[MGR_LVL_NUM]
      , [emp1].[HIERARCHY_TIER]
      , [emp1].[TIER_1]
      , [emp1].[TIER_2]
      , [emp1].[TIER_3]
      , [emp1].[TIER_4]
      , [emp1].[TIER_5]
      , [emp1].[TIER_6]
      , [emp1].[Snapshot_FTE]
      , [emp1].[Period_FTE]
      , [emp1].[Info_Source]
      , [emp1].[Calendar_period]
    FROM
      [DateCriteria1] AS [emp1]
    )
    SELECT
      @Date1 AS RRD
      , [emp1].[EMP_ID]
      , [emp1].[JOB_REQ]
      , [emp1].[POS_NUM]
      , [emp1].[CANDIDATE_ID]
      , [emp1].[EMP_STATUS]
      , [emp1].[WORKER_TYPE]
      , [emp1].[CONT_SUP_NAME]
      , [emp1].[REMOTE_FLAG]
      , [emp1].[emp_NAME]
      , [emp1].[FIRST_NAME]
      , [emp1].[LAST_NAME]
      , [emp1].[RECENT_HIRE_DATE]
      , [emp1].[ORIG_HIRE_DATE]
      , [emp1].[DEPT_NUM]
      , [emp1].[DEPT_NAME]
      , [emp1].[FUNCTIONAL_GROUP]
      , [emp1].[PAY_TYPE]
      , [emp1].[JOB_TITLE]
      , [emp1].[JOB_CODE]
      , [emp1].[JOB_FAMILY]
      , [emp1].[JOB_GROUP]
      , [emp1].[JOB_FUNCTION]
      , [emp1].[WORK_PHONE]
      , [emp1].
      , [emp1].[EMP_MGR_LVL]
      , [emp1].[ANNUAL_RATE]
      , [emp1].[HRLY_RATE]
      , [emp1].[EMP_TYPE]
      , [emp1].[TIME_TYPE]
      , [emp1].[EXEMPT]
      , [emp1].[EXPECTED_HOURS]
      , [emp1].[COMP_GRADE]
      , [emp1].[WORK_LOC_ID]
      , [emp1].[WORK_LOC_NAME]
      , [emp1].[WORK_LOC_ADD_1]
      , [emp1].[WORK_LOC_ADD_2]
      , [emp1].[WORK_LOC_CITY]
      , [emp1].[WORK_LOC_STATE]
      , [emp1].[WORK_LOC_ZIP]
      , [emp1].[HOME_ADD_1]
      , [emp1].[HOME_ADD_2]
      , [emp1].[HOME_ADD_CITY]
      , [emp1].[HOME_ADD_STATE]
      , [emp1].[HOME_ADD_ZIP]
      , [emp1].[HOME_ADD_ST]
      , [emp1].[LOA]
      , [emp1].[REPORTS_TO]
      , [emp1].[REPORTS_TO_EMP_ID]
      , [emp1].[REPORTS_TO_EMAIL]
      , [emp1].[REPORTS_TO_MGR_LVL]
      , [emp1].[emp_rank]
      , [emp1].[MGR_LVL_NUM]
      , [emp1].[HIERARCHY_TIER]
      , [emp1].[TIER_1]
      , [emp1].[TIER_2]
      , [emp1].[TIER_3]
      , [emp1].[TIER_4]
      , [emp1].[TIER_5]
      , [emp1].[TIER_6]
      , [emp1].[Snapshot_FTE]
      , [emp1].[Period_FTE]
      , [emp1].[Info_Source]
      , [emp1].[Calendar_period]
    FROM
      [Diff1to2] AS [emp1]
    UNION
    SELECT
      @Date2 AS RRD
      , [emp2].[EMP_ID]
      , [emp2].[JOB_REQ]
      , [emp2].[POS_NUM]
      , [emp2].[CANDIDATE_ID]
      , [emp2].[EMP_STATUS]
      , [emp2].[WORKER_TYPE]
      , [emp2].[CONT_SUP_NAME]
      , [emp2].[REMOTE_FLAG]
      , [emp2].[emp_NAME]
      , [emp2].[FIRST_NAME]
      , [emp2].[LAST_NAME]
      , [emp2].[RECENT_HIRE_DATE]
      , [emp2].[ORIG_HIRE_DATE]
      , [emp2].[DEPT_NUM]
      , [emp2].[DEPT_NAME]
      , [emp2].[FUNCTIONAL_GROUP]
      , [emp2].[PAY_TYPE]
      , [emp2].[JOB_TITLE]
      , [emp2].[JOB_CODE]
      , [emp2].[JOB_FAMILY]
      , [emp2].[JOB_GROUP]
      , [emp2].[JOB_FUNCTION]
      , [emp2].[WORK_PHONE]
      , [emp2].
      , [emp2].[EMP_MGR_LVL]
      , [emp2].[ANNUAL_RATE]
      , [emp2].[HRLY_RATE]
      , [emp2].[EMP_TYPE]
      , [emp2].[TIME_TYPE]
      , [emp2].[EXEMPT]
      , [emp2].[EXPECTED_HOURS]
      , [emp2].[COMP_GRADE]
      , [emp2].[WORK_LOC_ID]
      , [emp2].[WORK_LOC_NAME]
      , [emp2].[WORK_LOC_ADD_1]
      , [emp2].[WORK_LOC_ADD_2]
      , [emp2].[WORK_LOC_CITY]
      , [emp2].[WORK_LOC_STATE]
      , [emp2].[WORK_LOC_ZIP]
      , [emp2].[HOME_ADD_1]
      , [emp2].[HOME_ADD_2]
      , [emp2].[HOME_ADD_CITY]
      , [emp2].[HOME_ADD_STATE]
      , [emp2].[HOME_ADD_ZIP]
      , [emp2].[HOME_ADD_ST]
      , [emp2].[LOA]
      , [emp2].[REPORTS_TO]
      , [emp2].[REPORTS_TO_EMP_ID]
      , [emp2].[REPORTS_TO_EMAIL]
      , [emp2].[REPORTS_TO_MGR_LVL]
      , [emp2].[emp_rank]
      , [emp2].[MGR_LVL_NUM]
      , [emp2].[HIERARCHY_TIER]
      , [emp2].[TIER_1]
      , [emp2].[TIER_2]
      , [emp2].[TIER_3]
      , [emp2].[TIER_4]
      , [emp2].[TIER_5]
      , [emp2].[TIER_6]
      , [emp2].[Snapshot_FTE]
      , [emp2].[Period_FTE]
      , [emp2].[Info_Source]
      , [emp2].[Calendar_period]
    FROM
      [Diff2to1] AS [emp2];
    GO
     

  • Well... re-formatting your code to improve it does mean that you still need to have it inside of the code="SQL" and /code tags, so the last post didn't help any on that front.
    Also, you didn't provide any actual data for your INSERT statement.   Finally, let's talk about your objective.   You show some sample output, but it shows identical column
    names for both halves of the comparison, and you would be better served by having those actually be different.   Given that sample data, is there perhaps some over-arching
    need in terms of identifying what has changed?  Do you really need to know exactly what has changed?   That question naturally leads to wanting to know WHEN the change
    took place, which then leads to a host of other possible questions.   As your original query would only have identified the first of the columns in the CASE statement that had
    changed, I'm not at all sure that had it worked, that it would have been useful.   Thus I'm wondering what the ultimate objective is, as maybe there's another way to go about
    it that doesn't involve comparing every single column.   Mind you, that still might be okay, but you would probably be best served in that case by creating an output column
    for each comparison, and thus, rather than one giant CASE statement, you would have one for each source column that shows the result of the comparison.

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • Lynn Pettis - Tuesday, June 19, 2018 10:24 AM

    Just out of curiosity does the following work?


    DECLARE @Date1 DATE = '2018-06-04'
            , @Date2 DATE = '2018-06-01';

    WITH DateCriteria1 AS (
    SELECT
      [emp1].[RRD]
      , [emp1].[EMP_ID]
      , [emp1].[JOB_REQ]
      , [emp1].[POS_NUM]
      , [emp1].[CANDIDATE_ID]
      , [emp1].[EMP_STATUS]
      , [emp1].[WORKER_TYPE]
      , [emp1].[CONT_SUP_NAME]
      , [emp1].[REMOTE_FLAG]
      , [emp1].[emp_NAME]
      , [emp1].[FIRST_NAME]
      , [emp1].[LAST_NAME]
      , [emp1].[RECENT_HIRE_DATE]
      , [emp1].[ORIG_HIRE_DATE]
      , [emp1].[DEPT_NUM]
      , [emp1].[DEPT_NAME]
      , [emp1].[FUNCTIONAL_GROUP]
      , [emp1].[PAY_TYPE]
      , [emp1].[JOB_TITLE]
      , [emp1].[JOB_CODE]
      , [emp1].[JOB_FAMILY]
      , [emp1].[JOB_GROUP]
      , [emp1].[JOB_FUNCTION]
      , [emp1].[WORK_PHONE]
      , [emp1].
      , [emp1].[EMP_MGR_LVL]
      , [emp1].[ANNUAL_RATE]
      , [emp1].[HRLY_RATE]
      , [emp1].[EMP_TYPE]
      , [emp1].[TIME_TYPE]
      , [emp1].[EXEMPT]
      , [emp1].[EXPECTED_HOURS]
      , [emp1].[COMP_GRADE]
      , [emp1].[WORK_LOC_ID]
      , [emp1].[WORK_LOC_NAME]
      , [emp1].[WORK_LOC_ADD_1]
      , [emp1].[WORK_LOC_ADD_2]
      , [emp1].[WORK_LOC_CITY]
      , [emp1].[WORK_LOC_STATE]
      , [emp1].[WORK_LOC_ZIP]
      , [emp1].[HOME_ADD_1]
      , [emp1].[HOME_ADD_2]
      , [emp1].[HOME_ADD_CITY]
      , [emp1].[HOME_ADD_STATE]
      , [emp1].[HOME_ADD_ZIP]
      , [emp1].[HOME_ADD_ST]
      , [emp1].[LOA]
      , [emp1].[REPORTS_TO]
      , [emp1].[REPORTS_TO_EMP_ID]
      , [emp1].[REPORTS_TO_EMAIL]
      , [emp1].[REPORTS_TO_MGR_LVL]
      , [emp1].[emp_rank]
      , [emp1].[MGR_LVL_NUM]
      , [emp1].[HIERARCHY_TIER]
      , [emp1].[TIER_1]
      , [emp1].[TIER_2]
      , [emp1].[TIER_3]
      , [emp1].[TIER_4]
      , [emp1].[TIER_5]
      , [emp1].[TIER_6]
      , [emp1].[Snapshot_FTE]
      , [emp1].[Period_FTE]
      , [emp1].[Info_Source]
      , [emp1].[Calendar_period]
    FROM
      [emp_main]            AS [emp1]
    WHERE
      [emp1].[rrd]     = @Date1
    ),
    DateCriteria2 AS (
    SELECT
      [emp1].[RRD]
      , [emp1].[EMP_ID]
      , [emp1].[JOB_REQ]
      , [emp1].[POS_NUM]
      , [emp1].[CANDIDATE_ID]
      , [emp1].[EMP_STATUS]
      , [emp1].[WORKER_TYPE]
      , [emp1].[CONT_SUP_NAME]
      , [emp1].[REMOTE_FLAG]
      , [emp1].[emp_NAME]
      , [emp1].[FIRST_NAME]
      , [emp1].[LAST_NAME]
      , [emp1].[RECENT_HIRE_DATE]
      , [emp1].[ORIG_HIRE_DATE]
      , [emp1].[DEPT_NUM]
      , [emp1].[DEPT_NAME]
      , [emp1].[FUNCTIONAL_GROUP]
      , [emp1].[PAY_TYPE]
      , [emp1].[JOB_TITLE]
      , [emp1].[JOB_CODE]
      , [emp1].[JOB_FAMILY]
      , [emp1].[JOB_GROUP]
      , [emp1].[JOB_FUNCTION]
      , [emp1].[WORK_PHONE]
      , [emp1].
      , [emp1].[EMP_MGR_LVL]
      , [emp1].[ANNUAL_RATE]
      , [emp1].[HRLY_RATE]
      , [emp1].[EMP_TYPE]
      , [emp1].[TIME_TYPE]
      , [emp1].[EXEMPT]
      , [emp1].[EXPECTED_HOURS]
      , [emp1].[COMP_GRADE]
      , [emp1].[WORK_LOC_ID]
      , [emp1].[WORK_LOC_NAME]
      , [emp1].[WORK_LOC_ADD_1]
      , [emp1].[WORK_LOC_ADD_2]
      , [emp1].[WORK_LOC_CITY]
      , [emp1].[WORK_LOC_STATE]
      , [emp1].[WORK_LOC_ZIP]
      , [emp1].[HOME_ADD_1]
      , [emp1].[HOME_ADD_2]
      , [emp1].[HOME_ADD_CITY]
      , [emp1].[HOME_ADD_STATE]
      , [emp1].[HOME_ADD_ZIP]
      , [emp1].[HOME_ADD_ST]
      , [emp1].[LOA]
      , [emp1].[REPORTS_TO]
      , [emp1].[REPORTS_TO_EMP_ID]
      , [emp1].[REPORTS_TO_EMAIL]
      , [emp1].[REPORTS_TO_MGR_LVL]
      , [emp1].[emp_rank]
      , [emp1].[MGR_LVL_NUM]
      , [emp1].[HIERARCHY_TIER]
      , [emp1].[TIER_1]
      , [emp1].[TIER_2]
      , [emp1].[TIER_3]
      , [emp1].[TIER_4]
      , [emp1].[TIER_5]
      , [emp1].[TIER_6]
      , [emp1].[Snapshot_FTE]
      , [emp1].[Period_FTE]
      , [emp1].[Info_Source]
      , [emp1].[Calendar_period]
    FROM
      [emp_main]            AS [emp1]
    WHERE
      [emp1].[rrd]     = @Date2
    ),
    Diff1to2 AS (
    SELECT
      [emp1].[EMP_ID]
      , [emp1].[JOB_REQ]
      , [emp1].[POS_NUM]
      , [emp1].[CANDIDATE_ID]
      , [emp1].[EMP_STATUS]
      , [emp1].[WORKER_TYPE]
      , [emp1].[CONT_SUP_NAME]
      , [emp1].[REMOTE_FLAG]
      , [emp1].[emp_NAME]
      , [emp1].[FIRST_NAME]
      , [emp1].[LAST_NAME]
      , [emp1].[RECENT_HIRE_DATE]
      , [emp1].[ORIG_HIRE_DATE]
      , [emp1].[DEPT_NUM]
      , [emp1].[DEPT_NAME]
      , [emp1].[FUNCTIONAL_GROUP]
      , [emp1].[PAY_TYPE]
      , [emp1].[JOB_TITLE]
      , [emp1].[JOB_CODE]
      , [emp1].[JOB_FAMILY]
      , [emp1].[JOB_GROUP]
      , [emp1].[JOB_FUNCTION]
      , [emp1].[WORK_PHONE]
      , [emp1].
      , [emp1].[EMP_MGR_LVL]
      , [emp1].[ANNUAL_RATE]
      , [emp1].[HRLY_RATE]
      , [emp1].[EMP_TYPE]
      , [emp1].[TIME_TYPE]
      , [emp1].[EXEMPT]
      , [emp1].[EXPECTED_HOURS]
      , [emp1].[COMP_GRADE]
      , [emp1].[WORK_LOC_ID]
      , [emp1].[WORK_LOC_NAME]
      , [emp1].[WORK_LOC_ADD_1]
      , [emp1].[WORK_LOC_ADD_2]
      , [emp1].[WORK_LOC_CITY]
      , [emp1].[WORK_LOC_STATE]
      , [emp1].[WORK_LOC_ZIP]
      , [emp1].[HOME_ADD_1]
      , [emp1].[HOME_ADD_2]
      , [emp1].[HOME_ADD_CITY]
      , [emp1].[HOME_ADD_STATE]
      , [emp1].[HOME_ADD_ZIP]
      , [emp1].[HOME_ADD_ST]
      , [emp1].[LOA]
      , [emp1].[REPORTS_TO]
      , [emp1].[REPORTS_TO_EMP_ID]
      , [emp1].[REPORTS_TO_EMAIL]
      , [emp1].[REPORTS_TO_MGR_LVL]
      , [emp1].[emp_rank]
      , [emp1].[MGR_LVL_NUM]
      , [emp1].[HIERARCHY_TIER]
      , [emp1].[TIER_1]
      , [emp1].[TIER_2]
      , [emp1].[TIER_3]
      , [emp1].[TIER_4]
      , [emp1].[TIER_5]
      , [emp1].[TIER_6]
      , [emp1].[Snapshot_FTE]
      , [emp1].[Period_FTE]
      , [emp1].[Info_Source]
      , [emp1].[Calendar_period]
    FROM
      [DateCriteria1] AS [emp1]
    EXCEPT
    SELECT
      [emp2].[EMP_ID]
      , [emp2].[JOB_REQ]
      , [emp2].[POS_NUM]
      , [emp2].[CANDIDATE_ID]
      , [emp2].[EMP_STATUS]
      , [emp2].[WORKER_TYPE]
      , [emp2].[CONT_SUP_NAME]
      , [emp2].[REMOTE_FLAG]
      , [emp2].[emp_NAME]
      , [emp2].[FIRST_NAME]
      , [emp2].[LAST_NAME]
      , [emp2].[RECENT_HIRE_DATE]
      , [emp2].[ORIG_HIRE_DATE]
      , [emp2].[DEPT_NUM]
      , [emp2].[DEPT_NAME]
      , [emp2].[FUNCTIONAL_GROUP]
      , [emp2].[PAY_TYPE]
      , [emp2].[JOB_TITLE]
      , [emp2].[JOB_CODE]
      , [emp2].[JOB_FAMILY]
      , [emp2].[JOB_GROUP]
      , [emp2].[JOB_FUNCTION]
      , [emp2].[WORK_PHONE]
      , [emp2].
      , [emp2].[EMP_MGR_LVL]
      , [emp2].[ANNUAL_RATE]
      , [emp2].[HRLY_RATE]
      , [emp2].[EMP_TYPE]
      , [emp2].[TIME_TYPE]
      , [emp2].[EXEMPT]
      , [emp2].[EXPECTED_HOURS]
      , [emp2].[COMP_GRADE]
      , [emp2].[WORK_LOC_ID]
      , [emp2].[WORK_LOC_NAME]
      , [emp2].[WORK_LOC_ADD_1]
      , [emp2].[WORK_LOC_ADD_2]
      , [emp2].[WORK_LOC_CITY]
      , [emp2].[WORK_LOC_STATE]
      , [emp2].[WORK_LOC_ZIP]
      , [emp2].[HOME_ADD_1]
      , [emp2].[HOME_ADD_2]
      , [emp2].[HOME_ADD_CITY]
      , [emp2].[HOME_ADD_STATE]
      , [emp2].[HOME_ADD_ZIP]
      , [emp2].[HOME_ADD_ST]
      , [emp2].[LOA]
      , [emp2].[REPORTS_TO]
      , [emp2].[REPORTS_TO_EMP_ID]
      , [emp2].[REPORTS_TO_EMAIL]
      , [emp2].[REPORTS_TO_MGR_LVL]
      , [emp2].[emp_rank]
      , [emp2].[MGR_LVL_NUM]
      , [emp2].[HIERARCHY_TIER]
      , [emp2].[TIER_1]
      , [emp2].[TIER_2]
      , [emp2].[TIER_3]
      , [emp2].[TIER_4]
      , [emp2].[TIER_5]
      , [emp2].[TIER_6]
      , [emp2].[Snapshot_FTE]
      , [emp2].[Period_FTE]
      , [emp2].[Info_Source]
      , [emp2].[Calendar_period]
    FROM
      [DateCriteria2] AS [emp2]
    ),
    Diff2to1 AS (
    SELECT
      [emp2].[EMP_ID]
      , [emp2].[JOB_REQ]
      , [emp2].[POS_NUM]
      , [emp2].[CANDIDATE_ID]
      , [emp2].[EMP_STATUS]
      , [emp2].[WORKER_TYPE]
      , [emp2].[CONT_SUP_NAME]
      , [emp2].[REMOTE_FLAG]
      , [emp2].[emp_NAME]
      , [emp2].[FIRST_NAME]
      , [emp2].[LAST_NAME]
      , [emp2].[RECENT_HIRE_DATE]
      , [emp2].[ORIG_HIRE_DATE]
      , [emp2].[DEPT_NUM]
      , [emp2].[DEPT_NAME]
      , [emp2].[FUNCTIONAL_GROUP]
      , [emp2].[PAY_TYPE]
      , [emp2].[JOB_TITLE]
      , [emp2].[JOB_CODE]
      , [emp2].[JOB_FAMILY]
      , [emp2].[JOB_GROUP]
      , [emp2].[JOB_FUNCTION]
      , [emp2].[WORK_PHONE]
      , [emp2].
      , [emp2].[EMP_MGR_LVL]
      , [emp2].[ANNUAL_RATE]
      , [emp2].[HRLY_RATE]
      , [emp2].[EMP_TYPE]
      , [emp2].[TIME_TYPE]
      , [emp2].[EXEMPT]
      , [emp2].[EXPECTED_HOURS]
      , [emp2].[COMP_GRADE]
      , [emp2].[WORK_LOC_ID]
      , [emp2].[WORK_LOC_NAME]
      , [emp2].[WORK_LOC_ADD_1]
      , [emp2].[WORK_LOC_ADD_2]
      , [emp2].[WORK_LOC_CITY]
      , [emp2].[WORK_LOC_STATE]
      , [emp2].[WORK_LOC_ZIP]
      , [emp2].[HOME_ADD_1]
      , [emp2].[HOME_ADD_2]
      , [emp2].[HOME_ADD_CITY]
      , [emp2].[HOME_ADD_STATE]
      , [emp2].[HOME_ADD_ZIP]
      , [emp2].[HOME_ADD_ST]
      , [emp2].[LOA]
      , [emp2].[REPORTS_TO]
      , [emp2].[REPORTS_TO_EMP_ID]
      , [emp2].[REPORTS_TO_EMAIL]
      , [emp2].[REPORTS_TO_MGR_LVL]
      , [emp2].[emp_rank]
      , [emp2].[MGR_LVL_NUM]
      , [emp2].[HIERARCHY_TIER]
      , [emp2].[TIER_1]
      , [emp2].[TIER_2]
      , [emp2].[TIER_3]
      , [emp2].[TIER_4]
      , [emp2].[TIER_5]
      , [emp2].[TIER_6]
      , [emp2].[Snapshot_FTE]
      , [emp2].[Period_FTE]
      , [emp2].[Info_Source]
      , [emp2].[Calendar_period]
    FROM
      [DateCriteria2] AS [emp2]
    EXCEPT
    SELECT
      [emp1].[EMP_ID]
      , [emp1].[JOB_REQ]
      , [emp1].[POS_NUM]
      , [emp1].[CANDIDATE_ID]
      , [emp1].[EMP_STATUS]
      , [emp1].[WORKER_TYPE]
      , [emp1].[CONT_SUP_NAME]
      , [emp1].[REMOTE_FLAG]
      , [emp1].[emp_NAME]
      , [emp1].[FIRST_NAME]
      , [emp1].[LAST_NAME]
      , [emp1].[RECENT_HIRE_DATE]
      , [emp1].[ORIG_HIRE_DATE]
      , [emp1].[DEPT_NUM]
      , [emp1].[DEPT_NAME]
      , [emp1].[FUNCTIONAL_GROUP]
      , [emp1].[PAY_TYPE]
      , [emp1].[JOB_TITLE]
      , [emp1].[JOB_CODE]
      , [emp1].[JOB_FAMILY]
      , [emp1].[JOB_GROUP]
      , [emp1].[JOB_FUNCTION]
      , [emp1].[WORK_PHONE]
      , [emp1].
      , [emp1].[EMP_MGR_LVL]
      , [emp1].[ANNUAL_RATE]
      , [emp1].[HRLY_RATE]
      , [emp1].[EMP_TYPE]
      , [emp1].[TIME_TYPE]
      , [emp1].[EXEMPT]
      , [emp1].[EXPECTED_HOURS]
      , [emp1].[COMP_GRADE]
      , [emp1].[WORK_LOC_ID]
      , [emp1].[WORK_LOC_NAME]
      , [emp1].[WORK_LOC_ADD_1]
      , [emp1].[WORK_LOC_ADD_2]
      , [emp1].[WORK_LOC_CITY]
      , [emp1].[WORK_LOC_STATE]
      , [emp1].[WORK_LOC_ZIP]
      , [emp1].[HOME_ADD_1]
      , [emp1].[HOME_ADD_2]
      , [emp1].[HOME_ADD_CITY]
      , [emp1].[HOME_ADD_STATE]
      , [emp1].[HOME_ADD_ZIP]
      , [emp1].[HOME_ADD_ST]
      , [emp1].[LOA]
      , [emp1].[REPORTS_TO]
      , [emp1].[REPORTS_TO_EMP_ID]
      , [emp1].[REPORTS_TO_EMAIL]
      , [emp1].[REPORTS_TO_MGR_LVL]
      , [emp1].[emp_rank]
      , [emp1].[MGR_LVL_NUM]
      , [emp1].[HIERARCHY_TIER]
      , [emp1].[TIER_1]
      , [emp1].[TIER_2]
      , [emp1].[TIER_3]
      , [emp1].[TIER_4]
      , [emp1].[TIER_5]
      , [emp1].[TIER_6]
      , [emp1].[Snapshot_FTE]
      , [emp1].[Period_FTE]
      , [emp1].[Info_Source]
      , [emp1].[Calendar_period]
    FROM
      [DateCriteria1] AS [emp1]
    )
    SELECT
      @Date1 AS RRD
      , [emp1].[EMP_ID]
      , [emp1].[JOB_REQ]
      , [emp1].[POS_NUM]
      , [emp1].[CANDIDATE_ID]
      , [emp1].[EMP_STATUS]
      , [emp1].[WORKER_TYPE]
      , [emp1].[CONT_SUP_NAME]
      , [emp1].[REMOTE_FLAG]
      , [emp1].[emp_NAME]
      , [emp1].[FIRST_NAME]
      , [emp1].[LAST_NAME]
      , [emp1].[RECENT_HIRE_DATE]
      , [emp1].[ORIG_HIRE_DATE]
      , [emp1].[DEPT_NUM]
      , [emp1].[DEPT_NAME]
      , [emp1].[FUNCTIONAL_GROUP]
      , [emp1].[PAY_TYPE]
      , [emp1].[JOB_TITLE]
      , [emp1].[JOB_CODE]
      , [emp1].[JOB_FAMILY]
      , [emp1].[JOB_GROUP]
      , [emp1].[JOB_FUNCTION]
      , [emp1].[WORK_PHONE]
      , [emp1].
      , [emp1].[EMP_MGR_LVL]
      , [emp1].[ANNUAL_RATE]
      , [emp1].[HRLY_RATE]
      , [emp1].[EMP_TYPE]
      , [emp1].[TIME_TYPE]
      , [emp1].[EXEMPT]
      , [emp1].[EXPECTED_HOURS]
      , [emp1].[COMP_GRADE]
      , [emp1].[WORK_LOC_ID]
      , [emp1].[WORK_LOC_NAME]
      , [emp1].[WORK_LOC_ADD_1]
      , [emp1].[WORK_LOC_ADD_2]
      , [emp1].[WORK_LOC_CITY]
      , [emp1].[WORK_LOC_STATE]
      , [emp1].[WORK_LOC_ZIP]
      , [emp1].[HOME_ADD_1]
      , [emp1].[HOME_ADD_2]
      , [emp1].[HOME_ADD_CITY]
      , [emp1].[HOME_ADD_STATE]
      , [emp1].[HOME_ADD_ZIP]
      , [emp1].[HOME_ADD_ST]
      , [emp1].[LOA]
      , [emp1].[REPORTS_TO]
      , [emp1].[REPORTS_TO_EMP_ID]
      , [emp1].[REPORTS_TO_EMAIL]
      , [emp1].[REPORTS_TO_MGR_LVL]
      , [emp1].[emp_rank]
      , [emp1].[MGR_LVL_NUM]
      , [emp1].[HIERARCHY_TIER]
      , [emp1].[TIER_1]
      , [emp1].[TIER_2]
      , [emp1].[TIER_3]
      , [emp1].[TIER_4]
      , [emp1].[TIER_5]
      , [emp1].[TIER_6]
      , [emp1].[Snapshot_FTE]
      , [emp1].[Period_FTE]
      , [emp1].[Info_Source]
      , [emp1].[Calendar_period]
    FROM
      [Diff1to2] AS [emp1]
    UNION
    SELECT
      @Date2 AS RRD
      , [emp2].[EMP_ID]
      , [emp2].[JOB_REQ]
      , [emp2].[POS_NUM]
      , [emp2].[CANDIDATE_ID]
      , [emp2].[EMP_STATUS]
      , [emp2].[WORKER_TYPE]
      , [emp2].[CONT_SUP_NAME]
      , [emp2].[REMOTE_FLAG]
      , [emp2].[emp_NAME]
      , [emp2].[FIRST_NAME]
      , [emp2].[LAST_NAME]
      , [emp2].[RECENT_HIRE_DATE]
      , [emp2].[ORIG_HIRE_DATE]
      , [emp2].[DEPT_NUM]
      , [emp2].[DEPT_NAME]
      , [emp2].[FUNCTIONAL_GROUP]
      , [emp2].[PAY_TYPE]
      , [emp2].[JOB_TITLE]
      , [emp2].[JOB_CODE]
      , [emp2].[JOB_FAMILY]
      , [emp2].[JOB_GROUP]
      , [emp2].[JOB_FUNCTION]
      , [emp2].[WORK_PHONE]
      , [emp2].
      , [emp2].[EMP_MGR_LVL]
      , [emp2].[ANNUAL_RATE]
      , [emp2].[HRLY_RATE]
      , [emp2].[EMP_TYPE]
      , [emp2].[TIME_TYPE]
      , [emp2].[EXEMPT]
      , [emp2].[EXPECTED_HOURS]
      , [emp2].[COMP_GRADE]
      , [emp2].[WORK_LOC_ID]
      , [emp2].[WORK_LOC_NAME]
      , [emp2].[WORK_LOC_ADD_1]
      , [emp2].[WORK_LOC_ADD_2]
      , [emp2].[WORK_LOC_CITY]
      , [emp2].[WORK_LOC_STATE]
      , [emp2].[WORK_LOC_ZIP]
      , [emp2].[HOME_ADD_1]
      , [emp2].[HOME_ADD_2]
      , [emp2].[HOME_ADD_CITY]
      , [emp2].[HOME_ADD_STATE]
      , [emp2].[HOME_ADD_ZIP]
      , [emp2].[HOME_ADD_ST]
      , [emp2].[LOA]
      , [emp2].[REPORTS_TO]
      , [emp2].[REPORTS_TO_EMP_ID]
      , [emp2].[REPORTS_TO_EMAIL]
      , [emp2].[REPORTS_TO_MGR_LVL]
      , [emp2].[emp_rank]
      , [emp2].[MGR_LVL_NUM]
      , [emp2].[HIERARCHY_TIER]
      , [emp2].[TIER_1]
      , [emp2].[TIER_2]
      , [emp2].[TIER_3]
      , [emp2].[TIER_4]
      , [emp2].[TIER_5]
      , [emp2].[TIER_6]
      , [emp2].[Snapshot_FTE]
      , [emp2].[Period_FTE]
      , [emp2].[Info_Source]
      , [emp2].[Calendar_period]
    FROM
      [Diff2to1] AS [emp2];
    GO
     

    Sorry Lynn, but that combined both dates of the data as a whole. I am searching for a way to do an exception. When the data doesn't match then show only the data that doesn't match.
    Thank you for trying.

  • Well, with nothing to test it against myself, I guess I will have to believe what you say.  Logically, however, I would disagree.  The first two CTEs should pull the necessary data for each date of the comparison.  The third CTE should pull all the data from the first set of data that doesn't exist in the second (the dates have been pulled as that would then return ALL the rows).  The fourth CTE does the reverse of the third pulling all the data from the second set that isn't in the first (again, not including the dates).  This means that the data in the third CTE is from the first data set and the data in the fourth CTE is from the second data set.  The dates for these sets are added back to the respective data sets and the data then UNIONed together.

  • If all you want are the data columns where data is different between two rows of data, which could mean different columns for different sets within the entire set, you may need to look at writing some pretty gnarly dynamic SQL as columns aren't going to align nicely.
    What I wrote should be returning only the data that differs between two sets of dates.  You would need to do additional work to identify the data columns where data differs.

  • I started to go about the self join differently, here is a snippet that works until you add the WORKER_TYPE which is the same in both sets so it then unfortunately doesn't display the EMP_STATUS or PERIOD_FTE anymore which didn't match and what I wanted it to display.
    I can added data to this once I leave for the day. It's a little tough right now to do at work.

    Select

    emp1.RRD RRD1,

    emp1.EMP_ID EMP_ID1,

    emp1.EMP_STATUS EMP_STATUS1,

    emp1.Period_FTE Period_FTE1,

    emp1.WORKER_TYPE WORKER_TYPE1,

    emp2.RRD RRD2,

    emp2.EMP_ID EMP_ID2,

    emp2.EMP_STATUS EMP_STATUS2,

    emp2.Period_FTE Period_FTE2,

    emp2.WORKER_TYPE WORKER_TYPE2

    INTO #EMPTEMP7

    FROM emp_main emp1, emp_main emp2

    WHERE emp1.rrd = '2018-06-04'

    and emp2.rrd = '2018-06-01'

    and emp1.emp_id = emp2.emp_id

    and emp1.EMP_STATUS <> emp2.EMP_STATUS

    and emp1.Period_FTE <> emp2.Period_FTE

    and emp1.WORKER_TYPE <> emp2.WORKER_TYPE

    ORDER

    BY RRD1, RRD2;

    data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAqgAAABtCAYAAABtApNyAAAAAXNSR0IArs4c6QAAAARnQU1BAACxjwv8YQUAAAAJcEhZcwAADsMAAA7DAcdvqGQAACOmSURBVHhe7Z1/jCPlmee/PSsl+0e0w2nvxC5aTsmM3WR6jLLMDwRuhejCDIu7J6seLnECSXCSXeyE5NIG3XAXmJy0Si+6ZXbBTfY43PxxadBmF4dlWmG6TZhJpKzUJgozJBKehmmbYXSK2CDEaoYl0sKJ+N6n6i273K6yq/yzbH8/8E5XPVXv6/d96n2feup537In9hy8owLFF+Kfxcem/kg2x4bdO/+93iKEEEIIIUGhzkH9ymf2GUJCCCGEEEIGxTb9lxBCCCGEkEBAB5UQQgghhAQKOqiEEEIIISRQ1K1Bnb3hSkNI3Ln88sv1FiGEEEII6QUNDurk5KRxgDSyublJB5UQQgghpMfQQfWBOKikPejYE0IIIcQrdFB9wAhqe7zxxhvUGyGEEEI849tBTS2d0VvOnHE5fDq7V28NL24O6i9/+Uvj79tvv238tfMv//I25uYO6b3xhA4qIYQQQvzQ1lv8e/furSX1n5JUU90xnUadc+c+hE984hP41Kc+VZeuu+46FN79jT6LEEIIIYR4oS0H9cyZM7Wk/lOSetmWZJzSjHIG0YkJTNSlKFJ5fbxTpPxoBmW920skinrmLdVklU5d+EDrthNCCCGEkDp8O6gSD80m91bTHXsqxl+Zwpf0N7e9U91efzgCdUixJP805/qHUKpUUNGptHY1XvpOl53KHjuqr7zyCkqlEiqvncbm+Uv411/0wyUmhBBCCBktlIMqS1CNZaht8Zvf1E9hW+sw3333XSMJH/jAS8ZfP4Ric7j6+ZdR0vtBZ+nMGcM5lYDx2bPn8N7GCXzwg6/oo21w/hEcvOwyXJY+qQXCeTxyUMkOPqK2CCGEEEJGE98RVHG6JFJ4+vRp/PSnPzVk8tdKwtLSGTz33HM4fvwZY78dyvkVvHT9LoSNnQxSUT31H00howOT5Uy0thxAhFsjpHX7L+PY7Xfh+efvQljLGvJ3QHLvXmPdaTK5F4nE5430galDuOOOPVjbBJ4sl4301ltv6Rwe2L8f+8+Was7o+Wdx/AW9TQghhBAyorS1BvWjH/0o9u3bZ7wYJMhfK/3zP1+Bz30ubGxHDn3eexRRHMfq+tMJhL8DfPvxNELKlczcnsOux/X0/+O7kLtdHMw8jt11NdZEVorjpbuOKUkzduHI4w/hellKUJBy/eZvzq9+9Su8+eabRpIo6t+/cMmQi55u3b/dcGBlPWo8/lc+nNTDOLz7OJ61PNRXlaf7pS9hv94lhBBCCBlFfDuo7777Ub3ViEzp/+Efvm5sl/7f7xl/PWNfg1paw0N4CSsyv18+gdzzz+OusHZewxIFzeFEOYxd1y9hJhpF6sRVeLyURcwsySOd5q/nvffew8bGhpHeeeen+NCv/wn/uk1HZZeWVEoZm48+eocvJ/Xm2d04rj3Uk6tncXjW9jVg5x9BWqb8ZSnAwTQe0Y7s+UcOmrLLDiKthU4ynEybywgsubWaoK5clc9aUuDj8wghhBBC2sW3g/r++z833sy3pvTPfehDeOaZZ6rT+hJB/Yd/KKF44u+M9G47X7MUiiH97auxtGLFNJNmpLOaCkiHQkgX1Pbj38aul7+DcDjlMwLaaf56duzYURdJlun+KsmkSlkcOPDv8NXHHjNE4qR6YqdySI8/qxzEk1g9exg379RyWY+aOo7J7EVcvKhSdhLHU+JInsTD9+7GD0T24mGcvfdhJXGSKQ5mcFJklvwByS/l3gvc86Iu97CO2Pr5PEIIIYSQ9mlril++29RyxGTa+qabbqpO68uU/t7/vBeRiLkG8/3338d7712tc/ogNofk0gryoUOIX7+EldrCU72uNI/URBQZKGc2+zgeuv4lnMNVxotVJ6xTSy+bG4445O9sGWr1K6aMaX57gFSip6mU4cTu3XtG+avKYfX69bA7bsZhbOLVk6s4uzuMHVpsrkd9Affu0RHNPffihRdkOcBOTO7/Hj5z8CDSz4aRfTGDg3CSSRkSEdXRT8lvlYv78c2D1U8y8fV5hBBCCCHts63Dl/iNKX3rbf3qtP4FYPO35hrM9pEpeHEaQ0g/vgbkwuYU/+0vI26sTY3hyENXK7FM/YeRiz+OdEhkL+nlAFEcW9FFWYSuQhyy1lWipU759Xkd8B9/+yb+77b/YO5Y34GazALZrOHA/rcDOdM5tY61ZAduPnwWDzxwFrtnt7p+XzIjl9V0EncqJ/jOkxLhvAeTmw9gz540TqoyGmUnkd5zHJP3ZM28L97vYW2r188jhBBCCGmftiKoMrVvTetbU/ryxr5M6QuyBODi6e8byROhNArGi0t2ZApepvJlM4asTMfL9H4hW3UkQ+ksCnrav6CFoXShugwgq5xCo9xq+TFzWr9irjd1yt8J4qhXnVPlpFdJSQR1n+Gg/tWpuBZ6Z8fNh4EXdqPOP5XI6v7vYbW2EBQHjbWiyvG87CDUHu7MZHH//rMonXeSSabdwE4zUnr+2U0zgmpEbO/Fwyd1ufJiluDr88xTCCGEEELaoS0H9Vvf+hb+4i9O4Hvf+y/KSV0y/n73u/+kHML/ZSSRPfHEE9XUzvegDjXKOV3Cvto3GChH+c2FPN555x3cdtXf+IieanbciZMXt06d78Cd2R8Ax/eYU+6pTRzO3qmkB/HN+3crsUzF78Hxw1ncucNNJtnNKfvU8bO2cu/H2c/och84azquvj7PyEAIIYQQ0hYTew78uTHB/4XPfg6zN1yJyUnbW+Kkjs3NTVx++eV6r8aTT5axZ08FL744Ube29IPFV4zvQ5XoqXD6jkfx1ce+iuTerOHE//jHDxjyIHP+ZBp7VmdxMdP+ytI33njDUW+EEEIIIU5s63AJKtHI2/mFwncNx9NKFn//5z/GUjJrOLDinHb0C1N94GTa+tqoy7BH+dD3f5OvPRFCCCGkf0xcoyOoX2QEtSVuEVQ3VlZOGEsf3BiGCGo3YASVEEIIIX6gg+oDvw4qMaGDSgghhBA/0EH1gTiohBBCCCGkt9BBJYQQQgghgaKpg/rU0yf0FiGEEEIIIf1BOah/ph3UWx0d1E/fckjv9Z/XX38dV1xxhd4jdsZVN+wTg4F6bw/qzRnaL9JPqPf2GaTu2vqifkIIIYQQQnoFHVRCCCGEEBIo6KASQgghhJBAMZwOankR0xMTmKhL00jl2z0ux8r6oA11XmoiBSvbUODY9gmz7daxqiKEMhanlWx6UW3JrpNujBPdkTxN8/dBtw2fa6aetluw55uexqLOk0/Zy7Ile6H5lJKpPDb1NM+Xd9DZFplLfaqo473Vu0e9tYN8lnW93GiozwSmlYKb6tUhj6S6dvRcb5JsuvN9vHGc5VPT1eOig6Gg2bWwjtVfmL7br57odRDtFuz5Am6/+qN3j3prB/msAdivobQDmuGNoEYzKFUqqOhUWouguGC7+H6Ol45iaiFsu3h68IfTqP1g6RCxpe2SsjHrWBTR4mZND+VV5Ap626JON/F6vXlhULrte7uVcQ3nEC/pPMtx5GZMYxvLalllDUn135pVbrVCKvdKEclkBLnV2qd4yeeOe336pnc//cWLwW6HLf1gfT7UWq+ufad//bWrNkw5DzOI6+MlxHPh3t14u43rtZBjA7ZfvdRr39s9RParX3ofNfs1zHZAMTJT/KHYHCKFDZT0/laaHg/FML+cgRppurOFML8uF1Q6w6gRRzySQ9WmlDagrAyiereB0GRTvbYkMLrtQbvLmyhG45gN6f3QPNYrWXgxxeL4bBYjmMuqflnVTYc0rU+f9O6nv0j91udVzYJM//prV21YLKtuXpZuQ5iNR1Hc7EovGzADtl8D0+uY269+6X3U7NeQ24GRcVDL+RXVuacQ1vtbaXW8Y0M2RMzO1Z565Sk4PjdlbDvRUm9eCIhuu97u0Ky6baRxbDGPst8xLxGQyJwyvmFMwXbj6YRO6tMl6vRWXkRKoo8yvTSdMqcCjahDCilr2sk2RWk/t/qQb5NPJ3LYGjQaJXpnw8pYzRUQmQz0rdQzwbFf/dUr7ZdF7/Q+2vZr+OzA8DqohTTC0hl0Ci8AR5dtTzOtjjegBlq0iJEIMmxp+8TWdT9hZdiMp948VorqiXWrFbPlF73Fm+rNC33Sbd/bHcL88hqmNhYQDku++vVYzSiv5hCZk9iAPNVKtbqhnPbr0xGOeitjURnkqWWJPqq0rG5jCW3MC0VMzS2jUsroCJCcm1YDtGScW4oXMWPMQ9XLl49GjLNbUtcPtvQBN1r1nV6w5TN7ZcPKiwl128/giLfQ2OBpdS0CYr+6rte+t3s47Vcv9T7K9mvo7IBiNNagltaU2otYsT/itjreQAkbhQhGIsjQsB5ly7SN8aS6gZI8LUYmG42YPf96FvMd66RPuh1Eu2UKMLtu5JE1gmnLiDXFfJJdmjGNSThdQKFb02Rt1adDnPRmrJErIG3caFQKp1Eo5LAqY1Cm8WI25cq5huE0ZSF1xzPW222Vqxu065Smnbp+4HHKslXf6QX2z+yRDSsvTiORi6MU+KUUNlpdiwDYr57odRDtHjL71XO9j6j9Gko7oBiNKX7p1OrpZGnF5Vmj1XHBWP/S4VTQ0CBPvUUsLBT1U3CPCYxue9tuWSOY9LKUQQxXZM1mTErKjHmZJnOI5DTRref69AzbYn4jrWN+PAaYf3pgw+SmFFY3peUhuym1ZrD2a3B6HW/71X+9j4b9GmY7sA3yQ6fGj50OOdKZl1bcw+HNjpfzRig+cnTUDLk78pSHQgQ9t+8B02032y0DfyJVe8KX9UtLHhxxmR7DlP0sr9NkIUxGCtgo1c4rlzZUc8xoSrv16QkS7YkuYcWaozPWbrlEZ4zIUBrH9Nf5iH6MNjnJja0RpZs2TOk7kY5gbeScU5OB2a8B63Vs7Ve/9T4q9mvI7cDIvCTltk6oxpbj9jUb4QVsHC3VvtJj2GlYj+Lw/We+3tj0yaB02+d2h+aXsYZc9TNl/dJay3VfMj0GxKuvqpoYNx4P02SxbAlTKwllLKeNlFiZQkkrt7369ApzPRlyYfNaJDaarImTczPAgnmuPO2bbTLlxRlTrorwNkXWDl76Ts/png3LH0urm+ESZgbanjYJsP3qqV5pv1zr0//+PBr2a6jtgGLimhv/zIiffvFzt2L2hisxOTlpHBCeevoEPn3LIb3Xf15//XVcccUVeo/YGVfdsE8MBuq9Pag3Z2i/SD+h3ttnkLoboQgq6S3yix/1T2hm6sPbzgNlXNsdZHhNiF9ov8ar3UGG18QrykEdlUWopLfEkK1bMG6lHk2zBYZxbXeQ4TUhfqH9Gq92BxleE68wgkoIIYQQQgIFHVRCCCGEEBIoJq658Sv6JanbHF+SOnjjx/XeYLjssstw8eJF/nX5SwghhBDSC7Zv3663+o8nB3VQFbx06RL+7X0GeZ343d/57UA7zqCQPjGO7R401Ht7UG/OjKte2B8GA/XeHoPWW+C9vz/4/d/TW8SORFAJIYQQQkaRbUF/h//Xb72tt4gdTu8TQgghZFQZ7Qjqa6/iwmt6u1t0UmYX69NxBLVclv8J6S5e+hX7nnd6oatOyuxFfdohKPUgo0Un/cotr185qdK5g1peRKqHXzDbKoL6k7sPGE6spEOPvqqlWr4vhW/sU/K7n9NSzWuP4Mjvz+Mnele48Og8DhnlHMARWzl2XMs8ZeV1+CxN0/oofnJ3ff1b4SWCmk9NV78E2P7zZoY8nEAirOSp2pVzO18dwLQl1+cbv5usZbVk9oPyonX+NFID+Fm1sq2+E9Mp+K5Cs99dJq649Ss7rfue6jO2rH7lw0itLfXjzk1XBg5218u4cy3TYYxvxW99OsWvXtzOd2pbkO2XgaqzfP6gPn4cadq/7Tj09aZ90oec1NOBg1rG4rQaxOE0lrSkFzSNoCrH8Dbcgp8pJ/bXb/0Cf7pyDY6cUnLlgD64IfJTOCHyjQewZEQuX8XSzcqR3Pff8YTsWqjzv3EfcPdpVc7pe4D7Uvp8G65lPocjn/0/2PWkrgM+bdbBjmtejRxf1tseaRlBVQZuBnGUjC8ALiGeC5s3cjW4FooiX8e6yIsLphF0O18Nw9TMEiJrWq7OEnlofl1/ubBOpQyiyTnEVPmJNHC0JLKjQDrRXyOr2hGe0Z8v9VqeQi5MQ99z3PqVnWZ9z5DrPjOjjb9f+TDiNu5c9elid72MO9cyncd4HX7r0yl+9eJ2/rDZL01+pYhkMoLcaosPV/WtPkzbt4k/XPu3Hfex55jXr5w00IGDGsL8ugzuNSS1pBc0jaAeWMSvH7wTHzZ2duKmuWvxcvlV5Yeew+mpUFUemvo5SkZwcieSz4oj+RS+aByzsf8q7PiI+vuRmxBLWOfbcCvz1Cqe2P8/8fUDpjz54Ns4ZmzbcK2PoJzmrz2NXYlr9b43WkZQY1lUsvPqKgkhzMajKG6qUVDaQCEyWZVPRgrYKKlNt/PzK1iKZnDE+IkLdc2zFWQdfu4ifyyHuHkSEJ1CWAoKxTCX1OX3BWVAForIlLKImQ1RdZjH+loE6WNb77ikq7j1KzvN+t667nuhMKaiRUjX8y0fRvyOU7XtandbjTu3Mr2M8Xbq0wl+9eJ2/lDZL4syNosRzGXnEMmtenc4xdZZ44L4w7V/23Hp6255/cpJAyO0BvVVPLfyc+wK7cSF8lnsm9yp5cCOSe24uvGRP8Gf4mk8J1HN155DfuPLiG1xMt3KFDmqzqczTetz6mH8cOoefL327V6e8LcGtYzVXAGRyRDKm0Vlf8NaDoSntCGvo/58taEHkwv6iXBWTgrNIo4cjAf/ch4rxSTmHG4IPaG8ilwhAlXtesJTiC6tIK/qOS1T/tWpQJ/Twyp/Sp6gJa996UDetqSgWqb83rItciufbU0LuZUzxHjpV576nts19CsfSvyOUxsexp1bmV7GuO/6dJUxsV8W0qcjc4hBPXxZdTHkyqZYdsOwM3ksJtIoFNIIS+TUsG8SQR0v29MNOunfbnn9ykkjgXdQvb7Ff+HRFP4HrEimX3Yi+b9vwQ+/dgCHvvYA8F8X8Ul9xAv7sIojsnSgyRpUZ57Dkb8G7n7wJr3vHT9v8ZcXE0jDiiK0Zuv5UaxUjZrTepnyag6Ro7XoxfyyMvGJaUwnFoCjff59YYl+6M0qoUlE9CYKRWzMLZvTemsRFBe8TomV1c0gh6lleYJWSZYOJHTeWBbr1anCuC7TjL5YU3Sio4JMITYrZ8wx1gWG00DmSF2f8SsfVvyO03o6G3etxvggGSv7pTDqY3jFEgmGtiFiN2aAeEnbGVnaIvXLIBrNoFQXOaXtIaPBSERQLzx6AN9YuQU/e9aa7vfJa48oxxT422dP4cSzWYT/+oCxRlReXJLP/4MWTudpXIWvG0sHZH2puQbVS94Ljz6Al+e+6csZtvAaQZWbeCIX32LA3HE6v6Ce448YUxuyXmbr+rQ8juUitSiDPK0ngOX1dayvL2Nqoc/rPwsbaJgtKW+iqDcRjasbl25ZeMr86wUjUldAOqyjD8oxKhSsSItEJXRUVuRmDsSOZOTuom4AEtERP0opqVk5Y465LlD1ser6QRO/8mHE7zhtwGXc5VO6n7VQkNMY95q3l4yd/TJsRQFLM6buw+kCCmJDxG6I0z2vWxWKIVtZx3zD07gJbQ8ZBYY+girO6XXKOf1bm3P64dBunN6sTemf3zSn/t248KOngbk/qa4RvWkO+OGPXsUnHxSnU6UHb3ItU+T7Ju15zen71nlhLEk4fd81hiN73X3mttc3+b1EUMVYh5WxXrYZ69BkRPlwNReutGFOhQlu50enZvW+bW2Xpry4gGK8FsGSp3XE7edbEYA+INNzTusRZc2PEUHolCTWrEipkdQNIpRHKpzD1FEdlZWXLfTZtelCubnoKUQDp3L0oSGlWb+y8HKO2Wecprz8yocHv+PUCbdxF8vqPpaNuZbpNsa95O0lY2e/BLEVkTWbbSgpt1TZkFqTvTFGtqcbdNK/3fL6lZNGhjuCarx9vxvf3xo53XkV9m2UccHYeRXljWsRdvdPTQdy5UfV8621rHW4lXlgFrvuexg/Md7K95PXemHLTD/7y2ux7y9/gRNfbVJRGy0jqGV5GzWCNZuxNpD1mMVN9VwtyGL8KIzlMG7nx+YQSR9D3shQW9tlYj6dx2vWzxx81YX9W8/vNSHMH40gHU7p+qoa5BcxPVM0IwidYDi/S1ixwikSaam+MRtRejXbWF5VzrCxJegbXMJ202tazhDj1q/suJwjjoVE6Sx5df2gT/lQ4necuuBp3LmV2XSMa3zWp2PG0n6pT5QK1SlW25DNSeVmpnGsajf0OlNXx3WMbE836KR/u+X1KyeN/PGNX6lIevCxU5Vz586ph6oaP/jHZyoXL17Ue26sVZJIqn+7j3y2ct5c0/cTxo9g1SXl5Olj16r9ayv7RJZ4akvepypfxJcr37fJzPN1GQ3n289xKPPJLxuytvLqpBzUat29pFbXZS1ZrxdJ0UxJH4uq/WglKrKkeeWana8OGucaMn2+QWmtksk0XnmzfIfzu0Dr/ijVqtUX0WTFakallKlEo5mKtduwbyFyK7+VpB2qvcmo3reVW8rY9BNVbd/6GUrX1ToILuUEGS96d+pXSlpnH5zPKdn6TLSSrF0wn/Lg0e1xWqPR7tZ04nS+iWuZbmPcht/6NIP2y4lSJaPsR0N3tuyU3W5Iv1+TE1UbDJnSvZN9U+fVlTeEtqcbdMt+mbiNvcax4VceNLzorZdM/PEnvyydFbff+nnM3nAlJidrr5M/9fQJHLzx49i+fbuW9JdLly4ZkULlkGmJT+SXm7ATH5avj+oWnZTZxfpIZFldP73XBvIrFuq5OtS/4EBXkD4xqP44znjWu5d+NaR9rx067q+90FUnZXapPoHUSx+g/RoMXbVfbrjl9SsPEIPur4F3UP/t/cCvQhgIv/s7vx1LQ0cDPxio9/ag3pwZV72wPwwG6r09Bq23kXiLfxzx9z2ohBBCCCHDw9C/xT+uXPTxPaiEEEIIIcMEI6hDCiOohBBCCBlVPK1BHTTijEnEkH/r/xJCCCGE9IpBrkEN/EtSXNjsDF8yIP2Eem8P6s0Z2i/ST6j39hi03viKPCGEEEIICRR0UAkhhBBCSKAYbQdVvgi327/j1kmZvagPIUHCSx/nOPBOL3TVSZm9qA8hQaEXY8OvnFTpyEHNp6YxMTFhpGnr9337jFsdDHk4gURYyVN5LdWUF5GaSMEuLS+mMG2UM42US1tcy8xbeR0+S9O0Pop8qr7+3aDp9XHQgXM75DefTZmZpmEV5aozD/roJWXb509Mp6r19Qx/o7otWvVxwe2cWl9VfcmW1a98GKm1pX6cuunKIGg2zMmedIibXgxG2H4ZqDrY60p6T9P+bceh77nl9Ssn9bTvoKoBNIM4SpUKKpUS4rlw/28UbnVQHWihKPJ1rIu8uKAHehmL08rwhNNYkl0LdX4iDRwtqXJKR4F0otEwuJapDODMEiJrug6qRg16cM2rkeN1FeoCrtfHRQdN25HEmlGOpHXMy0+zuerMgz56iWp3eEbXS+q7PIVcmIa+57Tq44LbOdJXDbnuSzPa+PuVDyNu49RVn0GzYW72pEPc9DLq9kuTXykimYwgt7r1Im5BtaP6MG3fJv5w7d923MeeY16/ctJA+w5qLItKdh7mz8iGMBuPorjZZy271aG0gUJksiqfjBSwUTK359fF8Kwpk7WF6BTCkiEUw1zSOt+GW5n5FSxFMzgSM+Xz2QqyxrYN1/oIqtMncogko3q/S7heHxcduLWjvImi6EbEW3HSmRd99Ayly4UiMqUsYmbDVRXmsb4WQfrYAO4y40TTPq5xO0f66rruq6EwpqJFGF3Vr3wYGXob1qQ+nTCW9suijM1iBHPZOURyq94dTrF11rgg/vBiv6Q/OPU9t7x+5aSBLq1BLWM1V0BkcpBDo1aH8mZR2Z6aSQpPtXCeQ7PqSTkH42G1nMdKMYm5LUbJrUyRqw/Vnc2ZpvXJH0MuchRHpszd3tD6+jRtRyGHhDw5ypSX9ajnojMv+ugZ5VXkChE0NDM8hejSCvJGhCGFxerUoc/pYZU/pfVQt3QgX5sSrJWZR8o+RSefbU0LuZUzxDTt4xov57heQ7/yoWRIbVjPGRP7ZSF9OjKHmHKrp6w6GnJlUyy7YdiZPBYTaRQKaYQlcmrYN4mgjpft6Qad9G+3vH7lpJGuOKjlxQTSsJ46B0NndVBPRsvKXCWmMZ1YAI5mlXHwThQr1UHvbz2JMiTGx/VWcV5149qOaBxHl9WTY2lNPdFbU23uOmtfH13AKVoSmkREb6JQxMbcsnoKVu1Zi6C44HVKzIx0T4keJK8sHUjovLEs1q0pxFJcl2lGZawpuvJqDoWk3HSalDPmlBdlXVYayBypG39+5cPKcNqw3jNW9kshtiJiPF1I5BjahojdmAHiJW1nZGmL1DuDaDSDUl3klLaHjAbKQZXv6Te+q78t5CaRyMW3DJD+0nEd5AkzASyvr2N9fRlTC+bTp7y4JEZqooWRKqjn3CNG6F/Wk5hrlrzkLS8uoBjv7c3Vj26c2mFNGxlT5qEYjlhTbS46ExzL6ReFDTTMlsg0n96Um9URa/4/7CNsbUTqCkiH9XVVjlGhYEVgJCqho7IiN3MgdiQjdxd1A5AIkPhR6ko3K2fMCc2vm32m6kSY+JUPI8Nqw3rN2Nkvw1YUsDRj6j6cLqAgNkTshjjpxgJahWpLVtbTNjyNm9D2kFGgowiqGI+wMh7L7RrVLuBUh9BkRPkpNTeltNFiekhGcHxW5689tcayYqRUysZcyxR5dMqe1zSArfPCMESFdLhmiNR2w9uqHeDn+ri1ww03nfktp6vItJ3TekRZ82NEEDrF/rKFJHnhIo9UOIepozoqW8qgupq4Oo0oN5c4ZqsXwakcfWhIcRsfdryc495n/MqHh+G1Ye716QZjZ78EsRWRNZttKCm3VNmQmuq9MUa2pxt00r/d8vqVk0a2tR0/VU+giXQEawN0Tl3rIGsOi5vq2VGQBedR2JZ8NGB0mOpidJf1Tm5lxuYQSR9D3jjgJ6+14NpMpUwU0UwJ692yFn6vj0s75CYhERRTnMextNh1PcicdOZFHz1D6fRoBOlwSn++VHkR0zNFM4LQCYbzu4QV6wFCIjDVN2Yj6hqbbSyvKmfY2BL0jS9huxk2LWeI8TLmXM6p62PqX8e+50E+lAy1DTN2esNY2i/1ieI41ylW25DNSeVmpnGsajf0OlNXx3WMbE836KR/u+X1KyeNfOyTX6pIevCxU5Vz584pX6nGD/7xmcrFixf1Xj1ryeragGpSDpY+2h3cPtuiWR3WklG1H61ERZZUz4x1rFWSSKp/a5jn6zIazjdxLXMtacjayqtRDqov/XWiG5NGHTi3o6TrLvJoJWkrw1VnHvTRLq3aLZRsn49oslKtcilTiUYzqkWarfsWIrfyW0naUVI6i+p9W7mljK29UaWTrZ+h9FanepdygowXvTv38fp+5nyOWx/zKw8e42PDHOxJE2i/nChVMsp+NHRny07Z7Ya0ZU1OXFN5ZF/pwsm+qfPqyhtC29MNumW/TBplbmPDrzxoeNFbL5kQ51R1ViRu/QJmb7gSk5OTsmvw1NMncPDGj2P79u1a0l8uXbrU2WfLLzWoZ8dQNx+AOymzi/XpWDdDyri2e9B41ruXPt6LcRlQaMOcof0i/aSr9ssNt7x+5QFi0P21S18zFVDUle/6xe+kzF7Uh5Ag4aWPcxx4pxe66qTMXtSHkKDQi7HhV06qbLMmDAghhBBCCAkCox1BJYQQQgghQwcdVEIIIYQQEigmPvaf9EtSt7m/JEUIIYQQQsaHQb/Q19JB/fQth/QeIYQQQgghvYdT/IQQQgghJFDQQSWEEEIIIYGCDiohhBBCCAkUdFAJIYQQQkigoINKCCGEEEIChXJQ+VNShBBCCCEkODCCSgghhBBCAgUdVEIIIYQQEijooBJCCCGEkEBBB5UQQgghhAQKOqiEEEIIISRQ0EElhBBCCCGBgg4qIYQQQggJFHRQCSGEEEJIoKCDSgghhBBCAsU2/pAUIYQQQggJEoygEkIIIYSQQEEHlRBCCCGEBAo6qIQQQgghJFBs4xJUQgghhBASJLaZ7ildVEIIIYQQEgw4xU8IIYQQQgIE8P8BhkzeN0kxcTsAAAAASUVORK5CYII=

  • dc5jdmr - Tuesday, June 19, 2018 12:35 PM

    I started to go about the self join differently, here is a snippet that works until you add the WORKER_TYPE which is the same in both sets so it then unfortunately doesn't display the EMP_STATUS or PERIOD_FTE anymore which didn't match and what I wanted it to display.
    I can added data to this once I leave for the day. It's a little tough right now to do at work.

    Select

    emp1.RRD RRD1,

    emp1.EMP_ID EMP_ID1,

    emp1.EMP_STATUS EMP_STATUS1,

    emp1.Period_FTE Period_FTE1,

    emp1.WORKER_TYPE WORKER_TYPE1,

    emp2.RRD RRD2,

    emp2.EMP_ID EMP_ID2,

    emp2.EMP_STATUS EMP_STATUS2,

    emp2.Period_FTE Period_FTE2,

    emp2.WORKER_TYPE WORKER_TYPE2

    INTO #EMPTEMP7

    FROM emp_main emp1, emp_main emp2

    WHERE emp1.rrd = '2018-06-04'

    and emp2.rrd = '2018-06-01'

    and emp1.emp_id = emp2.emp_id

    and emp1.EMP_STATUS <> emp2.EMP_STATUS

    and emp1.Period_FTE <> emp2.Period_FTE

    and emp1.WORKER_TYPE <> emp2.WORKER_TYPE

    ORDER

    BY RRD1, RRD2;

    data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAqgAAABtCAYAAABtApNyAAAAAXNSR0IArs4c6QAAAARnQU1BAACxjwv8YQUAAAAJcEhZcwAADsMAAA7DAcdvqGQAACOmSURBVHhe7Z1/jCPlmee/PSsl+0e0w2nvxC5aTsmM3WR6jLLMDwRuhejCDIu7J6seLnECSXCSXeyE5NIG3XAXmJy0Si+6ZXbBTfY43PxxadBmF4dlWmG6TZhJpKzUJgozJBKehmmbYXSK2CDEaoYl0sKJ+N6n6i273K6yq/yzbH8/8E5XPVXv6/d96n2feup537In9hy8owLFF+Kfxcem/kg2x4bdO/+93iKEEEIIIUGhzkH9ymf2GUJCCCGEEEIGxTb9lxBCCCGEkEBAB5UQQgghhAQKOqiEEEIIISRQ1K1Bnb3hSkNI3Ln88sv1FiGEEEII6QUNDurk5KRxgDSyublJB5UQQgghpMfQQfWBOKikPejYE0IIIcQrdFB9wAhqe7zxxhvUGyGEEEI849tBTS2d0VvOnHE5fDq7V28NL24O6i9/+Uvj79tvv238tfMv//I25uYO6b3xhA4qIYQQQvzQ1lv8e/furSX1n5JUU90xnUadc+c+hE984hP41Kc+VZeuu+46FN79jT6LEEIIIYR4oS0H9cyZM7Wk/lOSetmWZJzSjHIG0YkJTNSlKFJ5fbxTpPxoBmW920skinrmLdVklU5d+EDrthNCCCGEkDp8O6gSD80m91bTHXsqxl+Zwpf0N7e9U91efzgCdUixJP805/qHUKpUUNGptHY1XvpOl53KHjuqr7zyCkqlEiqvncbm+Uv411/0wyUmhBBCCBktlIMqS1CNZaht8Zvf1E9hW+sw3333XSMJH/jAS8ZfP4Ric7j6+ZdR0vtBZ+nMGcM5lYDx2bPn8N7GCXzwg6/oo21w/hEcvOwyXJY+qQXCeTxyUMkOPqK2CCGEEEJGE98RVHG6JFJ4+vRp/PSnPzVk8tdKwtLSGTz33HM4fvwZY78dyvkVvHT9LoSNnQxSUT31H00howOT5Uy0thxAhFsjpHX7L+PY7Xfh+efvQljLGvJ3QHLvXmPdaTK5F4nE5430galDuOOOPVjbBJ4sl4301ltv6Rwe2L8f+8+Was7o+Wdx/AW9TQghhBAyorS1BvWjH/0o9u3bZ7wYJMhfK/3zP1+Bz30ubGxHDn3eexRRHMfq+tMJhL8DfPvxNELKlczcnsOux/X0/+O7kLtdHMw8jt11NdZEVorjpbuOKUkzduHI4w/hellKUJBy/eZvzq9+9Su8+eabRpIo6t+/cMmQi55u3b/dcGBlPWo8/lc+nNTDOLz7OJ61PNRXlaf7pS9hv94lhBBCCBlFfDuo7777Ub3ViEzp/+Efvm5sl/7f7xl/PWNfg1paw0N4CSsyv18+gdzzz+OusHZewxIFzeFEOYxd1y9hJhpF6sRVeLyURcwsySOd5q/nvffew8bGhpHeeeen+NCv/wn/uk1HZZeWVEoZm48+eocvJ/Xm2d04rj3Uk6tncXjW9jVg5x9BWqb8ZSnAwTQe0Y7s+UcOmrLLDiKthU4ynEybywgsubWaoK5clc9aUuDj8wghhBBC2sW3g/r++z833sy3pvTPfehDeOaZZ6rT+hJB/Yd/KKF44u+M9G47X7MUiiH97auxtGLFNJNmpLOaCkiHQkgX1Pbj38aul7+DcDjlMwLaaf56duzYURdJlun+KsmkSlkcOPDv8NXHHjNE4qR6YqdySI8/qxzEk1g9exg379RyWY+aOo7J7EVcvKhSdhLHU+JInsTD9+7GD0T24mGcvfdhJXGSKQ5mcFJklvwByS/l3gvc86Iu97CO2Pr5PEIIIYSQ9mlril++29RyxGTa+qabbqpO68uU/t7/vBeRiLkG8/3338d7712tc/ogNofk0gryoUOIX7+EldrCU72uNI/URBQZKGc2+zgeuv4lnMNVxotVJ6xTSy+bG4445O9sGWr1K6aMaX57gFSip6mU4cTu3XtG+avKYfX69bA7bsZhbOLVk6s4uzuMHVpsrkd9Affu0RHNPffihRdkOcBOTO7/Hj5z8CDSz4aRfTGDg3CSSRkSEdXRT8lvlYv78c2D1U8y8fV5hBBCCCHts63Dl/iNKX3rbf3qtP4FYPO35hrM9pEpeHEaQ0g/vgbkwuYU/+0vI26sTY3hyENXK7FM/YeRiz+OdEhkL+nlAFEcW9FFWYSuQhyy1lWipU759Xkd8B9/+yb+77b/YO5Y34GazALZrOHA/rcDOdM5tY61ZAduPnwWDzxwFrtnt7p+XzIjl9V0EncqJ/jOkxLhvAeTmw9gz540TqoyGmUnkd5zHJP3ZM28L97vYW2r188jhBBCCGmftiKoMrVvTetbU/ryxr5M6QuyBODi6e8byROhNArGi0t2ZApepvJlM4asTMfL9H4hW3UkQ+ksCnrav6CFoXShugwgq5xCo9xq+TFzWr9irjd1yt8J4qhXnVPlpFdJSQR1n+Gg/tWpuBZ6Z8fNh4EXdqPOP5XI6v7vYbW2EBQHjbWiyvG87CDUHu7MZHH//rMonXeSSabdwE4zUnr+2U0zgmpEbO/Fwyd1ufJiluDr88xTCCGEEELaoS0H9Vvf+hb+4i9O4Hvf+y/KSV0y/n73u/+kHML/ZSSRPfHEE9XUzvegDjXKOV3Cvto3GChH+c2FPN555x3cdtXf+IieanbciZMXt06d78Cd2R8Ax/eYU+6pTRzO3qmkB/HN+3crsUzF78Hxw1ncucNNJtnNKfvU8bO2cu/H2c/och84azquvj7PyEAIIYQQ0hYTew78uTHB/4XPfg6zN1yJyUnbW+Kkjs3NTVx++eV6r8aTT5axZ08FL744Ube29IPFV4zvQ5XoqXD6jkfx1ce+iuTerOHE//jHDxjyIHP+ZBp7VmdxMdP+ytI33njDUW+EEEIIIU5s63AJKtHI2/mFwncNx9NKFn//5z/GUjJrOLDinHb0C1N94GTa+tqoy7BH+dD3f5OvPRFCCCGkf0xcoyOoX2QEtSVuEVQ3VlZOGEsf3BiGCGo3YASVEEIIIX6gg+oDvw4qMaGDSgghhBA/0EH1gTiohBBCCCGkt9BBJYQQQgghgaKpg/rU0yf0FiGEEEIIIf1BOah/ph3UWx0d1E/fckjv9Z/XX38dV1xxhd4jdsZVN+wTg4F6bw/qzRnaL9JPqPf2GaTu2vqifkIIIYQQQnoFHVRCCCGEEBIo6KASQgghhJBAMZwOankR0xMTmKhL00jl2z0ux8r6oA11XmoiBSvbUODY9gmz7daxqiKEMhanlWx6UW3JrpNujBPdkTxN8/dBtw2fa6aetluw55uexqLOk0/Zy7Ile6H5lJKpPDb1NM+Xd9DZFplLfaqo473Vu0e9tYN8lnW93GiozwSmlYKb6tUhj6S6dvRcb5JsuvN9vHGc5VPT1eOig6Gg2bWwjtVfmL7br57odRDtFuz5Am6/+qN3j3prB/msAdivobQDmuGNoEYzKFUqqOhUWouguGC7+H6Ol45iaiFsu3h68IfTqP1g6RCxpe2SsjHrWBTR4mZND+VV5Ap626JON/F6vXlhULrte7uVcQ3nEC/pPMtx5GZMYxvLalllDUn135pVbrVCKvdKEclkBLnV2qd4yeeOe336pnc//cWLwW6HLf1gfT7UWq+ufad//bWrNkw5DzOI6+MlxHPh3t14u43rtZBjA7ZfvdRr39s9RParX3ofNfs1zHZAMTJT/KHYHCKFDZT0/laaHg/FML+cgRppurOFML8uF1Q6w6gRRzySQ9WmlDagrAyiereB0GRTvbYkMLrtQbvLmyhG45gN6f3QPNYrWXgxxeL4bBYjmMuqflnVTYc0rU+f9O6nv0j91udVzYJM//prV21YLKtuXpZuQ5iNR1Hc7EovGzADtl8D0+uY269+6X3U7NeQ24GRcVDL+RXVuacQ1vtbaXW8Y0M2RMzO1Z565Sk4PjdlbDvRUm9eCIhuu97u0Ky6baRxbDGPst8xLxGQyJwyvmFMwXbj6YRO6tMl6vRWXkRKoo8yvTSdMqcCjahDCilr2sk2RWk/t/qQb5NPJ3LYGjQaJXpnw8pYzRUQmQz0rdQzwbFf/dUr7ZdF7/Q+2vZr+OzA8DqohTTC0hl0Ci8AR5dtTzOtjjegBlq0iJEIMmxp+8TWdT9hZdiMp948VorqiXWrFbPlF73Fm+rNC33Sbd/bHcL88hqmNhYQDku++vVYzSiv5hCZk9iAPNVKtbqhnPbr0xGOeitjURnkqWWJPqq0rG5jCW3MC0VMzS2jUsroCJCcm1YDtGScW4oXMWPMQ9XLl49GjLNbUtcPtvQBN1r1nV6w5TN7ZcPKiwl128/giLfQ2OBpdS0CYr+6rte+t3s47Vcv9T7K9mvo7IBiNNagltaU2otYsT/itjreQAkbhQhGIsjQsB5ly7SN8aS6gZI8LUYmG42YPf96FvMd66RPuh1Eu2UKMLtu5JE1gmnLiDXFfJJdmjGNSThdQKFb02Rt1adDnPRmrJErIG3caFQKp1Eo5LAqY1Cm8WI25cq5huE0ZSF1xzPW222Vqxu065Smnbp+4HHKslXf6QX2z+yRDSsvTiORi6MU+KUUNlpdiwDYr57odRDtHjL71XO9j6j9Gko7oBiNKX7p1OrpZGnF5Vmj1XHBWP/S4VTQ0CBPvUUsLBT1U3CPCYxue9tuWSOY9LKUQQxXZM1mTErKjHmZJnOI5DTRref69AzbYn4jrWN+PAaYf3pgw+SmFFY3peUhuym1ZrD2a3B6HW/71X+9j4b9GmY7sA3yQ6fGj50OOdKZl1bcw+HNjpfzRig+cnTUDLk78pSHQgQ9t+8B02032y0DfyJVe8KX9UtLHhxxmR7DlP0sr9NkIUxGCtgo1c4rlzZUc8xoSrv16QkS7YkuYcWaozPWbrlEZ4zIUBrH9Nf5iH6MNjnJja0RpZs2TOk7kY5gbeScU5OB2a8B63Vs7Ve/9T4q9mvI7cDIvCTltk6oxpbj9jUb4QVsHC3VvtJj2GlYj+Lw/We+3tj0yaB02+d2h+aXsYZc9TNl/dJay3VfMj0GxKuvqpoYNx4P02SxbAlTKwllLKeNlFiZQkkrt7369ApzPRlyYfNaJDaarImTczPAgnmuPO2bbTLlxRlTrorwNkXWDl76Ts/png3LH0urm+ESZgbanjYJsP3qqV5pv1zr0//+PBr2a6jtgGLimhv/zIiffvFzt2L2hisxOTlpHBCeevoEPn3LIb3Xf15//XVcccUVeo/YGVfdsE8MBuq9Pag3Z2i/SD+h3ttnkLoboQgq6S3yix/1T2hm6sPbzgNlXNsdZHhNiF9ov8ar3UGG18QrykEdlUWopLfEkK1bMG6lHk2zBYZxbXeQ4TUhfqH9Gq92BxleE68wgkoIIYQQQgIFHVRCCCGEEBIoJq658Sv6JanbHF+SOnjjx/XeYLjssstw8eJF/nX5SwghhBDSC7Zv3663+o8nB3VQFbx06RL+7X0GeZ343d/57UA7zqCQPjGO7R401Ht7UG/OjKte2B8GA/XeHoPWW+C9vz/4/d/TW8SORFAJIYQQQkaRbUF/h//Xb72tt4gdTu8TQgghZFQZ7Qjqa6/iwmt6u1t0UmYX69NxBLVclv8J6S5e+hX7nnd6oatOyuxFfdohKPUgo0Un/cotr185qdK5g1peRKqHXzDbKoL6k7sPGE6spEOPvqqlWr4vhW/sU/K7n9NSzWuP4Mjvz+Mnele48Og8DhnlHMARWzl2XMs8ZeV1+CxN0/oofnJ3ff1b4SWCmk9NV78E2P7zZoY8nEAirOSp2pVzO18dwLQl1+cbv5usZbVk9oPyonX+NFID+Fm1sq2+E9Mp+K5Cs99dJq649Ss7rfue6jO2rH7lw0itLfXjzk1XBg5218u4cy3TYYxvxW99OsWvXtzOd2pbkO2XgaqzfP6gPn4cadq/7Tj09aZ90oec1NOBg1rG4rQaxOE0lrSkFzSNoCrH8Dbcgp8pJ/bXb/0Cf7pyDY6cUnLlgD64IfJTOCHyjQewZEQuX8XSzcqR3Pff8YTsWqjzv3EfcPdpVc7pe4D7Uvp8G65lPocjn/0/2PWkrgM+bdbBjmtejRxf1tseaRlBVQZuBnGUjC8ALiGeC5s3cjW4FooiX8e6yIsLphF0O18Nw9TMEiJrWq7OEnlofl1/ubBOpQyiyTnEVPmJNHC0JLKjQDrRXyOr2hGe0Z8v9VqeQi5MQ99z3PqVnWZ9z5DrPjOjjb9f+TDiNu5c9elid72MO9cyncd4HX7r0yl+9eJ2/rDZL01+pYhkMoLcaosPV/WtPkzbt4k/XPu3Hfex55jXr5w00IGDGsL8ugzuNSS1pBc0jaAeWMSvH7wTHzZ2duKmuWvxcvlV5Yeew+mpUFUemvo5SkZwcieSz4oj+RS+aByzsf8q7PiI+vuRmxBLWOfbcCvz1Cqe2P8/8fUDpjz54Ns4ZmzbcK2PoJzmrz2NXYlr9b43WkZQY1lUsvPqKgkhzMajKG6qUVDaQCEyWZVPRgrYKKlNt/PzK1iKZnDE+IkLdc2zFWQdfu4ifyyHuHkSEJ1CWAoKxTCX1OX3BWVAForIlLKImQ1RdZjH+loE6WNb77ikq7j1KzvN+t667nuhMKaiRUjX8y0fRvyOU7XtandbjTu3Mr2M8Xbq0wl+9eJ2/lDZL4syNosRzGXnEMmtenc4xdZZ44L4w7V/23Hp6255/cpJAyO0BvVVPLfyc+wK7cSF8lnsm9yp5cCOSe24uvGRP8Gf4mk8J1HN155DfuPLiG1xMt3KFDmqzqczTetz6mH8cOoefL327V6e8LcGtYzVXAGRyRDKm0Vlf8NaDoSntCGvo/58taEHkwv6iXBWTgrNIo4cjAf/ch4rxSTmHG4IPaG8ilwhAlXtesJTiC6tIK/qOS1T/tWpQJ/Twyp/Sp6gJa996UDetqSgWqb83rItciufbU0LuZUzxHjpV576nts19CsfSvyOUxsexp1bmV7GuO/6dJUxsV8W0qcjc4hBPXxZdTHkyqZYdsOwM3ksJtIoFNIIS+TUsG8SQR0v29MNOunfbnn9ykkjgXdQvb7Ff+HRFP4HrEimX3Yi+b9vwQ+/dgCHvvYA8F8X8Ul9xAv7sIojsnSgyRpUZ57Dkb8G7n7wJr3vHT9v8ZcXE0jDiiK0Zuv5UaxUjZrTepnyag6Ro7XoxfyyMvGJaUwnFoCjff59YYl+6M0qoUlE9CYKRWzMLZvTemsRFBe8TomV1c0gh6lleYJWSZYOJHTeWBbr1anCuC7TjL5YU3Sio4JMITYrZ8wx1gWG00DmSF2f8SsfVvyO03o6G3etxvggGSv7pTDqY3jFEgmGtiFiN2aAeEnbGVnaIvXLIBrNoFQXOaXtIaPBSERQLzx6AN9YuQU/e9aa7vfJa48oxxT422dP4cSzWYT/+oCxRlReXJLP/4MWTudpXIWvG0sHZH2puQbVS94Ljz6Al+e+6csZtvAaQZWbeCIX32LA3HE6v6Ce448YUxuyXmbr+rQ8juUitSiDPK0ngOX1dayvL2Nqoc/rPwsbaJgtKW+iqDcRjasbl25ZeMr86wUjUldAOqyjD8oxKhSsSItEJXRUVuRmDsSOZOTuom4AEtERP0opqVk5Y465LlD1ser6QRO/8mHE7zhtwGXc5VO6n7VQkNMY95q3l4yd/TJsRQFLM6buw+kCCmJDxG6I0z2vWxWKIVtZx3zD07gJbQ8ZBYY+girO6XXKOf1bm3P64dBunN6sTemf3zSn/t248KOngbk/qa4RvWkO+OGPXsUnHxSnU6UHb3ItU+T7Ju15zen71nlhLEk4fd81hiN73X3mttc3+b1EUMVYh5WxXrYZ69BkRPlwNReutGFOhQlu50enZvW+bW2Xpry4gGK8FsGSp3XE7edbEYA+INNzTusRZc2PEUHolCTWrEipkdQNIpRHKpzD1FEdlZWXLfTZtelCubnoKUQDp3L0oSGlWb+y8HKO2Wecprz8yocHv+PUCbdxF8vqPpaNuZbpNsa95O0lY2e/BLEVkTWbbSgpt1TZkFqTvTFGtqcbdNK/3fL6lZNGhjuCarx9vxvf3xo53XkV9m2UccHYeRXljWsRdvdPTQdy5UfV8621rHW4lXlgFrvuexg/Md7K95PXemHLTD/7y2ux7y9/gRNfbVJRGy0jqGV5GzWCNZuxNpD1mMVN9VwtyGL8KIzlMG7nx+YQSR9D3shQW9tlYj6dx2vWzxx81YX9W8/vNSHMH40gHU7p+qoa5BcxPVM0IwidYDi/S1ixwikSaam+MRtRejXbWF5VzrCxJegbXMJ202tazhDj1q/suJwjjoVE6Sx5df2gT/lQ4necuuBp3LmV2XSMa3zWp2PG0n6pT5QK1SlW25DNSeVmpnGsajf0OlNXx3WMbE836KR/u+X1KyeN/PGNX6lIevCxU5Vz586ph6oaP/jHZyoXL17Ue26sVZJIqn+7j3y2ct5c0/cTxo9g1SXl5Olj16r9ayv7RJZ4akvepypfxJcr37fJzPN1GQ3n289xKPPJLxuytvLqpBzUat29pFbXZS1ZrxdJ0UxJH4uq/WglKrKkeeWana8OGucaMn2+QWmtksk0XnmzfIfzu0Dr/ijVqtUX0WTFakallKlEo5mKtduwbyFyK7+VpB2qvcmo3reVW8rY9BNVbd/6GUrX1ToILuUEGS96d+pXSlpnH5zPKdn6TLSSrF0wn/Lg0e1xWqPR7tZ04nS+iWuZbmPcht/6NIP2y4lSJaPsR0N3tuyU3W5Iv1+TE1UbDJnSvZN9U+fVlTeEtqcbdMt+mbiNvcax4VceNLzorZdM/PEnvyydFbff+nnM3nAlJidrr5M/9fQJHLzx49i+fbuW9JdLly4ZkULlkGmJT+SXm7ATH5avj+oWnZTZxfpIZFldP73XBvIrFuq5OtS/4EBXkD4xqP44znjWu5d+NaR9rx067q+90FUnZXapPoHUSx+g/RoMXbVfbrjl9SsPEIPur4F3UP/t/cCvQhgIv/s7vx1LQ0cDPxio9/ag3pwZV72wPwwG6r09Bq23kXiLfxzx9z2ohBBCCCHDw9C/xT+uXPTxPaiEEEIIIcMEI6hDCiOohBBCCBlVPK1BHTTijEnEkH/r/xJCCCGE9IpBrkEN/EtSXNjsDF8yIP2Eem8P6s0Z2i/ST6j39hi03viKPCGEEEIICRR0UAkhhBBCSKAYbQdVvgi327/j1kmZvagPIUHCSx/nOPBOL3TVSZm9qA8hQaEXY8OvnFTpyEHNp6YxMTFhpGnr9337jFsdDHk4gURYyVN5LdWUF5GaSMEuLS+mMG2UM42US1tcy8xbeR0+S9O0Pop8qr7+3aDp9XHQgXM75DefTZmZpmEV5aozD/roJWXb509Mp6r19Qx/o7otWvVxwe2cWl9VfcmW1a98GKm1pX6cuunKIGg2zMmedIibXgxG2H4ZqDrY60p6T9P+bceh77nl9Ssn9bTvoKoBNIM4SpUKKpUS4rlw/28UbnVQHWihKPJ1rIu8uKAHehmL08rwhNNYkl0LdX4iDRwtqXJKR4F0otEwuJapDODMEiJrug6qRg16cM2rkeN1FeoCrtfHRQdN25HEmlGOpHXMy0+zuerMgz56iWp3eEbXS+q7PIVcmIa+57Tq44LbOdJXDbnuSzPa+PuVDyNu49RVn0GzYW72pEPc9DLq9kuTXykimYwgt7r1Im5BtaP6MG3fJv5w7d923MeeY16/ctJA+w5qLItKdh7mz8iGMBuPorjZZy271aG0gUJksiqfjBSwUTK359fF8Kwpk7WF6BTCkiEUw1zSOt+GW5n5FSxFMzgSM+Xz2QqyxrYN1/oIqtMncogko3q/S7heHxcduLWjvImi6EbEW3HSmRd99Ayly4UiMqUsYmbDVRXmsb4WQfrYAO4y40TTPq5xO0f66rruq6EwpqJFGF3Vr3wYGXob1qQ+nTCW9suijM1iBHPZOURyq94dTrF11rgg/vBiv6Q/OPU9t7x+5aSBLq1BLWM1V0BkcpBDo1aH8mZR2Z6aSQpPtXCeQ7PqSTkH42G1nMdKMYm5LUbJrUyRqw/Vnc2ZpvXJH0MuchRHpszd3tD6+jRtRyGHhDw5ypSX9ajnojMv+ugZ5VXkChE0NDM8hejSCvJGhCGFxerUoc/pYZU/pfVQt3QgX5sSrJWZR8o+RSefbU0LuZUzxDTt4xov57heQ7/yoWRIbVjPGRP7ZSF9OjKHmHKrp6w6GnJlUyy7YdiZPBYTaRQKaYQlcmrYN4mgjpft6Qad9G+3vH7lpJGuOKjlxQTSsJ46B0NndVBPRsvKXCWmMZ1YAI5mlXHwThQr1UHvbz2JMiTGx/VWcV5149qOaBxHl9WTY2lNPdFbU23uOmtfH13AKVoSmkREb6JQxMbcsnoKVu1Zi6C44HVKzIx0T4keJK8sHUjovLEs1q0pxFJcl2lGZawpuvJqDoWk3HSalDPmlBdlXVYayBypG39+5cPKcNqw3jNW9kshtiJiPF1I5BjahojdmAHiJW1nZGmL1DuDaDSDUl3klLaHjAbKQZXv6Te+q78t5CaRyMW3DJD+0nEd5AkzASyvr2N9fRlTC+bTp7y4JEZqooWRKqjn3CNG6F/Wk5hrlrzkLS8uoBjv7c3Vj26c2mFNGxlT5qEYjlhTbS46ExzL6ReFDTTMlsg0n96Um9URa/4/7CNsbUTqCkiH9XVVjlGhYEVgJCqho7IiN3MgdiQjdxd1A5AIkPhR6ko3K2fMCc2vm32m6kSY+JUPI8Nqw3rN2Nkvw1YUsDRj6j6cLqAgNkTshjjpxgJahWpLVtbTNjyNm9D2kFGgowiqGI+wMh7L7RrVLuBUh9BkRPkpNTeltNFiekhGcHxW5689tcayYqRUysZcyxR5dMqe1zSArfPCMESFdLhmiNR2w9uqHeDn+ri1ww03nfktp6vItJ3TekRZ82NEEDrF/rKFJHnhIo9UOIepozoqW8qgupq4Oo0oN5c4ZqsXwakcfWhIcRsfdryc495n/MqHh+G1Ye716QZjZ78EsRWRNZttKCm3VNmQmuq9MUa2pxt00r/d8vqVk0a2tR0/VU+giXQEawN0Tl3rIGsOi5vq2VGQBedR2JZ8NGB0mOpidJf1Tm5lxuYQSR9D3jjgJ6+14NpMpUwU0UwJ692yFn6vj0s75CYhERRTnMextNh1PcicdOZFHz1D6fRoBOlwSn++VHkR0zNFM4LQCYbzu4QV6wFCIjDVN2Yj6hqbbSyvKmfY2BL0jS9huxk2LWeI8TLmXM6p62PqX8e+50E+lAy1DTN2esNY2i/1ieI41ylW25DNSeVmpnGsajf0OlNXx3WMbE836KR/u+X1KyeNfOyTX6pIevCxU5Vz584pX6nGD/7xmcrFixf1Xj1ryeragGpSDpY+2h3cPtuiWR3WklG1H61ERZZUz4x1rFWSSKp/a5jn6zIazjdxLXMtacjayqtRDqov/XWiG5NGHTi3o6TrLvJoJWkrw1VnHvTRLq3aLZRsn49oslKtcilTiUYzqkWarfsWIrfyW0naUVI6i+p9W7mljK29UaWTrZ+h9FanepdygowXvTv38fp+5nyOWx/zKw8e42PDHOxJE2i/nChVMsp+NHRny07Z7Ya0ZU1OXFN5ZF/pwsm+qfPqyhtC29MNumW/TBplbmPDrzxoeNFbL5kQ51R1ViRu/QJmb7gSk5OTsmvw1NMncPDGj2P79u1a0l8uXbrU2WfLLzWoZ8dQNx+AOymzi/XpWDdDyri2e9B41ruXPt6LcRlQaMOcof0i/aSr9ssNt7x+5QFi0P21S18zFVDUle/6xe+kzF7Uh5Ag4aWPcxx4pxe66qTMXtSHkKDQi7HhV06qbLMmDAghhBBCCAkCox1BJYQQQgghQwcdVEIIIYQQEigmPvaf9EtSt7m/JEUIIYQQQsaHQb/Q19JB/fQth/QeIYQQQgghvYdT/IQQQgghJFDQQSWEEEIIIYGCDiohhBBCCAkUdFAJIYQQQkigoINKCCGEEEIChXJQ+VNShBBCCCEkODCCSgghhBBCAgUdVEIIIYQQEijooBJCCCGEkEBBB5UQQgghhAQKOqiEEEIIISRQ0EElhBBCCCGBgg4qIYQQQggJFHRQCSGEEEJIoKCDSgghhBBCAsU2/pAUIYQQQggJEoygEkIIIYSQQEEHlRBCCCGEBAo6qIQQQgghJFBs4xJUQgghhBASJLaZ7ildVEIIIYQQEgw4xU8IIYQQQgIE8P8BhkzeN0kxcTsAAAAASUVORK5CYII=

    First of all, please use ANSI-02 style joins instead of the older ANSI-89 style joins.  This will separate your join criteria from your filter criteria.

    Try this:

    SELECT
      [emp1].[RRD]           [RRD1]
      , [emp1].[EMP_ID]      [EMP_ID1]
      , [emp1].[EMP_STATUS]  [EMP_STATUS1]
      , [emp1].[Period_FTE]  [Period_FTE1]
      , [emp1].[WORKER_TYPE] [WORKER_TYPE1]
      , [emp2].[RRD]         [RRD2]
      , [emp2].[EMP_ID]      [EMP_ID2]
      , [emp2].[EMP_STATUS]  [EMP_STATUS2]
      , [emp2].[Period_FTE]  [Period_FTE2]
      , [emp2].[WORKER_TYPE] [WORKER_TYPE2]
    INTO
      [#EMPTEMP7]
    FROM
      [emp_main] [emp1]
      INNER JOIN [emp_main] [emp2]
        ON [emp1].[emp_id]      = [emp2].[emp_id]
    WHERE
      ([emp1].[rrd]             = '2018-06-04'
      AND [emp2].[rrd]          = '2018-06-01')
      AND ([emp1].[EMP_STATUS]     <> [emp2].[EMP_STATUS]
           OR [emp1].[Period_FTE]  <> [emp2].[Period_FTE]
           OR [emp1].[WORKER_TYPE] <> [emp2].[WORKER_TYPE])
    ORDER BY
      [RRD1]
      , [RRD2];
    GO

  • This is good Lynn!

    Is there a way to have WORKER_TYPE not show if [emp1].[WORKER_TYPE] <> [emp2].[WORKER_TYPE] and EMP_STATUS  if [emp1].[EMP_STATUS] <> [emp2].[EMP_STATUS]not show? The Period_FTE columns are all different so those are good.

    the data displays as:

  • You would have to write some interesting dynamic SQL to return the entire data set, then determine if two columns between the two sets of data completely match and not include that data in the final result set.  The more columns of data that you need to report differences would make that dynamic SQL even harder to write and maintain in my opinion, and I have had to write some interesting dynamic SQL at times but nothing like what you are asking.

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply