Comparing data within the same table

[luvmy2Js]

I have a table that a set of data from 2 sources. The difference is the employeeid has letters in the front when coming from table b:

employee id [Pay Group ID]
WD506197 US01
506197 USA-1

Below is the code I have so far, but I am not sure how to distinguish the tables and if this is going to work:

Select
a.[employee id], a.[Pay Group ID],
'Analysis' = Case when a.[Pay Group ID] = b.[Pay Group ID] then ''
when a.[Pay Group ID] <> b.[Pay Group ID]
then 'MisMatch' end
from whse_AssignEmployeetoPayGroup a
inner join whse_AssignEmployeetoPayGroup b on b.[employee id] = a.[employee id]


I want something like this as the results:

employee id [Pay Group ID]
WD506197 'MisMatch'


Any help is greatly appreciated!

 

[markros]

Thanks for the table, I should have used it instead of writing from the top of my head. Here is a working code

Select 
a.[employee id], a.[Pay Group ID],
'Analysis' ='MisMatch'  
from @Temp  a
LEFT join @Temp  b
on a.[Employee ID] = 'WD' + b.[Employee ID] and a.[Employee ID] like 'WD%' and a.[Pay Group ID] <> b.[Pay Group ID]
UNION 
Select 
a.[employee id], a.[Pay Group ID],
'Analysis' ='MisMatch'  
from @Temp  a
LEFT join @Temp  b
on b.[Employee ID] = 'WD' + a.[Employee ID] and b.[Employee ID] like 'WD%' and a.[Pay Group ID] <> b.[Pay Group ID]
order by [Employee Id]

PluralSight Learning Library

 

[markros]

Sorry, disregard the previous reply. Working on an update.

PluralSight Learning Library

 

[markros]

Try

;with cte as (Select 
a.[employee id], a.[Pay Group ID],
'Analysis' ='MisMatch'  
from @Temp  a
inner join @Temp  b
on a.[Employee ID] = 'WD' + b.[Employee ID] and a.[Employee ID] like 'WD%' and a.[Pay Group ID] <> b.[Pay Group ID]
UNION ALL
Select 
a.[employee id], a.[Pay Group ID],
'Analysis' ='No Match'  
from @Temp  a 
where not exists (select 1 from @Temp b
where a.[Employee Id] = 'WD' + b.[Employee Id])
and a.[Employee Id] LIKE 'WD%' 
--LEFT join @Temp  b
--on b.[Employee ID] = 'WD' + a.[Employee ID] and b.[Employee ID] like 'WD%' and a.[Pay Group ID] <> b.[Pay Group ID]
UNION ALL
Select 
a.[employee id], a.[Pay Group ID],
'Analysis' ='No Match'  
from @Temp  a 
where not exists (select 1 from @Temp b
where b.[Employee Id] = 'WD' + a.[Employee Id])
and a.[Employee Id] not LIKE 'WD%') 

select * from cte order by REPLACE([Employee ID],'WD','')

PluralSight Learning Library

 

[luvmy2Js]

What does the ";with cte as" mean? Never seen that before..

Thanks,

 

[markros]

In SQL Server 2005 Common Table Expression (CTE for short) were introduced. This reduced greatly the complexity of statements and also CTE can be used recursively.

You may find this blog post (that has lots of good references)
CTE and hierarchical queries
helpful to understand the concept.

PluralSight Learning Library

 

[luvmy2Js]

Thanks a million guys for all of your help on this!!