Table1
District Center s_No
Chittoor VenkatagiriKota 20004
Hyderabad CivilSupply 20005
Ananthapur NGOHome 20006
Ananthapur Ananthapur 20007
Chittoor Chittoor 20008
Ananthapur NGOHome 20010
Ananthapur Ananthapur 20020
Tble2
Id S_No
1080200062412120131215133536 20004
1080200062412220130509170801 20005
1080200062412320130509171628 20006
1080200062412420130509172121 20007
1080200062412520130509172512 20008
table3
id s_No Name
1080200062412120131215133536 20004 aaa
1080200062412420130509172121 20007 bbb
1080200062413220130509181948 20010 xxx
1080200062413320130509182836 20020 yyy
O/P;
District Center S_No Id Status
Chittoor VenkatagiriKota 20004 1080200062412120131215133536 Matched
Hyderabad CivilSupply 20005 1080200062412220130509170801 Unmatched
Ananthapur NGOHome 20006 1080200062412320130509171628 Unmatched
Ananthapur Ananthapur 20007 1080200062412420130509172121 Matched
Chittoor Chittoor 20008 1080200062412520130509172512 Unmatched
Ananthapur NGOHome 20010 1080200062413220130509181948 Newlyadded
Ananthapur Ananthapur 20020 1080200062413320130509182836 Newlyadded
Matched means:if Table2 and Table3 record is matched status in matched
Unmatched means:if Table2 and Table3 record is unmatched status in unmatched
Newly added:if new record is added in table3 status is newly added.
i am using bellow query. but it will shows one error
with cte as (select CenterDetails.District,CenterDetails.CenterName,NewDataParsing.EA_STATION_ID,case isnull(OldDataParsing.ENROLMENT_PACKET,0) when 0 then 'Newly added' else 'Matched' end as status
from OldDataParsing right outer join NewDataParsing on OldDataParsing.EA_STATION_ID = NewDataParsing.EA_STATION_ID
inner join CenterDetails on NewDataParsing.EA_STATION_ID = CenterDetails.EnrolmentStation)
select CenterDetails.District,CenterDetails.CenterName,CenterDetails.EnrolmentStation,isnull(cte.status,'Un_matched') as Status from CenterDetails left outer join cte on CenterDetails.EnrolmentStation = cte.EA_STATION_ID
error is
Msg 248, Level 16, State 1, Line 2
The conversion of the nvarchar value '1080200073724520130524170256 ' overflowed an int column.