I’m new to Sql Server and I’m stuck on the script below and help would be appreciated!!
I would like you to load these into the test server please and then do a comparison on 2 tables, as follows:-
Table1 --Contain Master data
MANDTZZPUNOZZPO_POINTVERNRZNAME
1002023131Florence wright
1002030160Maty E Wambui
1002065174Carole Daring
1002028347Dom Left
Table2
TCODEPSPIDZNAMEZTITLE
--UnknowInactive
POP20301Florence wrightProgramme
POP91603Maty E Wambui Advisor
POP91604Carole DaringAccountant
POP20704Dom LeftCoordinator
I need to match ZNAME to ZNAME so used
USE TestDA
SELECT ZNAME AS Name_not_Active
FROM table1 -- Find data that exists in Table1 but not in table2
WHERE ZZNAME IS NULL
EXCEPT
SELECT ZZNAME
FROM Active
and NOT found =ERROR to be reported (don’t know how to that any ideas?)
If found ZZPO_POINT – 2 numeric should = last 2 digits of PSPID –I need to explain this
ZZPUNO should = first 4 of PSPID
If found ZZPO_POINT the last 2 numeric should equal last 2 digit of PSPID and ZZPUNO should equal the first 4 digits of PSPID examples below:-
ZZPUNO =4448122
PSID = 444856
ZZPO_POINT= 56
Any ideas where I should start to create a comparison between both tables that does the searches above?
March 8, 2012 at 8:05 am
Try to post your question as per following: http://www.sqlservercentral.com/articles/Best+Practices/61537/
and it will attract much more attention...
Eugene is correct in asking you to provide more info, however I was a little bored so I threw this togehter. Since your requirements were a little vague, the query is pretty generic. I just used some cases to show where your matches and not matches occur.
This is probalby not what you are looking for, but maybe the logic will get you there.
--first make some fake tables that look sort of like your info
create table #master(
mandt int
,zzpuno int
,zzpo_point int
,vernr int
,zname varchar(50)
)
create table #OtherOne (
tcode varchar(5)
, pspid int
, zname varchar(50)
, ztitle varchar(50)
)
--add some data
insert into #master
values (100, 2023, 1, 31, 'Florence wright'),
(100, 2030, 1, 60, 'Maty E Wambui'),
(100, 2065, 1, 74, 'Carole Daring'),
(100, 2028, 3, 47, 'Dom Left')
insert into #OtherOne
values
('POP', 20301, 'Florence wright' ,'Programme'),
('POP', 91603, 'Maty E Wambui' ,'Advisor'),
('POP', 91604, 'Carole Daring' ,'Accountant'),
('POP', 20704, 'Dom Left', 'Coordinator')
--join the tables and look for matches and not matches
select
m.mandt
,m.zname
,m.zzpo_point
,m.zzpuno
,o.pspid
,o.tcode
,o.zname
,o.ztitle
,NotFoundError=case when o.zname is null then 'Not Found' else 'Found' end --if no match in 2nd table
,ZZPointMatch=case when m.zzpo_point = RIGHT(o.pspid,2) then 'Match' else 'Not Match' end --if names match but zzpo_point does not
,ZZPunoMatch=case when m.zzpuno = LEFT(o.pspid, 4) then 'Match' else 'Not Match' end --if names match but zzpo_puno does not
from#master m
--join the two tables on the zname columns, use left join so we can find non-matches also
left outer join #OtherOne o on o.zname = m.zname
--clean up
drop table #master
drop table #OtherOne
March 8, 2012 at 6:48 pm
Use FULL JOIN if you want to check both directions at the same time.
March 9, 2012 at 4:39 am
Thank you! Exactly what i need.
Will follow the best practises guide for posting questions from on now.:-D
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply