Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
miteshpant
Starting Member
1 Post |
 Posted - 2012-04-20 : 08:57:46
|
| Below is the problem i am facing. There are two tables 1: employeeinfo (HR maintains this table and we cannot alter the records of this table)2: cmp_employee_info (i have created this table with limited fields we need to monitor for changes, the records in this table are limited).Procedure:1: The second table stores the same information as employee info but is not updated by HR, but the first table can at any point be updated by HR.2: A program checks every day for differences in table employeeinfo and cmp_employee_info. If any changes are found it needs to alert us via emiail regarding he changes and also it updated the cmp_employee_info table.I have this query in place.(SELECT cmp_employeeinfo_rms.logonid,cmp_employeeinfo_rms.firstname,cmp_employeeinfo_rms.lastname,cmp_employeeinfo_rms.emailaddr,cmp_employeeinfo_rms.Locationname,cmp_employeeinfo_rms.termdate,cmp_employeeinfo_rms.persontype,cmp_employeeinfo_rms.jobclass,cmp_employeeinfo_rms.assignmentstatus,cmp_employeeinfo_rms.mgrlogonid FROM cmp_employeeinfo_rms LEFT OUTER JOIN employeeinfo ON cmp_employeeinfo_rms.logonid = employeeinfo.logonid where (cmp_employeeinfo_rms.mgrlogonid != employeeinfo.mgrlogonid or cmp_employeeinfo_rms.lastname != employeeinfo.lastname or cmp_employeeinfo_rms.locationname != employeeinfo.locationname or cmp_employeeinfo_rms.termdate != employeeinfo.termdate or cmp_employeeinfo_rms.persontype != employeeinfo.persontype or cmp_employeeinfo_rms.jobclass != employeeinfo.jobclass or cmp_employeeinfo_rms.assignmentstatus != employeeinfo.assignmentstatus)) UNION (SELECT employeeinfo.logonid,employeeinfo.firstname,employeeinfo.lastname,employeeinfo.emailaddr,employeeinfo.Locationname,employeeinfo.termdate,employeeinfo.persontype,employeeinfo.jobclass,employeeinfo.assignmentstatus,employeeinfo.mgrlogonid FROM employeeinfo JOIN cmp_employeeinfo_rms on cmp_employeeinfo_rms.logonid=employeeinfo.logonid)The output to the above query is. LOGONID FIRSTNAME LASTNAME EMAIL LOCATION TERMDATE TYPE DESIGNATION ASSIGNMENTSTATUS MGRID TEST1 FIRST1 LAST1 EMAIL1 L1 NULL Associate D1 Active Assignment M1 TEST2 FIRST2 LAST2 EMAIL2 L2 NULL Associate D2 Active Assignment M2 TEST3 FIRST3 LAST3 EMAIL3 L3 NULL Associate D3 Active Assignment M3 TEST4 FIRST4 LAST4 EMAIL4 L4 NULL Associate D4 Active Assignment M4 TEST5 FIRST5 LAST5 EMAIL5 L5 NULL Associate D5 Active Assignment M5 TEST6 FIRST6 LAST6 EMAIL6 L6 NULL Associate D6 Active Assignment M6 TEST7 FIRST7 LAST7 EMAIL7 L7 NULL Associate D7 Active Assignment M7 TEST8 FIRST8 LAST8 EMAIL8 L8 NULL Associate D8 Active Assignment M8 TEST9 FIRST9 LAST9 EMAIL9 L9 NULL Associate D9 Active Assignment M9 TEST10 FIRST10 LAST10 EMAIL10 L10 NULL Associate D10 Active Assignment M10 TEST11 FIRST11 LAST11 EMAIL11 L11 NULL Associate D11 Active Assignment M11 TEST12 FIRST12 LAST12 EMAIL12 L12 NULL Associate D12 Active Assignment M12 TEST13 FIRST13 LAST13 EMAIL13 L13 NULL Associate D13 Active Assignment M13 TEST14 FIRST14 LAST14 EMAIL14 L14 NULL Associate D14 Active Assignment M14 TEST15 FIRST15 LAST15 EMAIL15 L15 NULL Associate D15 Active Assignment M15 TEST16 FIRST16 LAST16 EMAIL16 L16 NULL Associate D16 Active Assignment M16 TEST17 FIRST17 LAST17 EMAIL17 L17 NULL Associate D17 Active Assignment M17 TEST18 FIRST18 LAST18 EMAIL18 L18 NULL Associate D18 Active Assignment M18 TEST18 FIRST18 LAST18 EMAIL18 L18 NULL Outsorced D18 Active Assignment M18 TEST19 FIRST19 LAST19 EMAIL19 L19 NULL Associate D19 Active Assignment M19 TEST20 FIRST20 LAST20 EMAIL20 L20 NULL Associate D20 Active Assignment M20 TEST21 FIRST21 LAST21 EMAIL21 L21 NULL Associate D21 Active Assignment M21 TEST22 FIRST22 LAST22 EMAIL22 L22 NULL Associate D22 Active Assignment M22 TEST23 FIRST23 LAST23 EMAIL23 L23 NULL Associate D23 Active Assignment M23 As you would have noticed that the following record appears twice as there is a change in persontype.TEST18 FIRST18 LAST18 EMAIL18 L18 NULL Associate D18 Active Assignment M18 TEST18 FIRST18 LAST18 EMAIL18 L18 NULL Outsorced D18 Active Assignment M18 I wish to extract any such change with the field changed. After recording the change i would update the second table as well. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-04-20 : 15:27:09
|
| whats the purpose of UNION part in your query?Also your first query will reduce to an inner join as checking conditions in where will cause all unmatched rows from cmp_employeeinfo_rms table to be filtered out as they'll return null from second table------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
|
|
|
|
|
Finding Differences in attributes of two tables