SQl data comaprison

Question

Post reply

SQl data comaprison

keethukumar

Newbie

Points: 4
More actions
January 6, 2010 at 9:54 pm

#73976

I have 3 tables
•Ter_Ter_Alignment: Has 4 columns
Source SegmentSource TerritoryTarget SegmentTarget Territory
S1 T1 S3 T3
S2 T2 S4 T4
S3 T3 S2 S2
S4 T4 S1 T1
•Territory: Has 3 columns
Terr_ID (PK)Segment Territory
TID1 S1 T1
TID2 S2 T2
TID3 S3 T3
TID4 S4 T4
The data from Ter_Ter_Alignment table has to get loaded into a table called Territory_Assoc. However the target table doesn’t store 4 columns from the source, instead it maps the source segment/ter and target seg/ter with terr_id column generated in Territory table and stores the terr_id of source seg/terr into From Terr ID column and Target seg/ter into To Terr ID column.
•Territory_Assoc: Has 3 columns
Terr_Assoc_ID (PK)From Terr IDTo Terr ID
1 TID1 TID3
2 TID2 TID4
3 TID3 TID2
4 TID4 TID1
Now how do I compare the data between Ter_Ter_Alignment and Territory_Assoc??
pleeeeeeeeeaaaaseee help :w00t:

Viewing 7 posts - 1 through 6 (of 6 total)

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

arun.sas SSChampion Points: 11831 More actions · Answer 1

create table #TER_TER_ALIGNMENT

(

SourceSegment varchar(2),

SourceTerritory varchar(2),

TargetSegment varchar(2),

TargetTerritory varchar(2)

)

create table #Territory

(

Terr_ID varchar(5),

Segment varchar(2),

Territory varchar(2)

)

create table #Territory_Assoc

(

Terr_Assoc_ID int identity(1,1),

FromTerrID varchar(5),

ToTerrID varchar(5)

)

insert into #TER_TER_ALIGNMENT

select 'S1','T1','S3','T3'

union all

select 'S2','T2','S4','T4'

union all

select 'S3','T3','S2','T2'

union all

select 'S4','T4','S1','T1'

insert into #Territory

select 'TID1','S1','T1'

union all

select 'TID2','S2','T2'

union all

select 'TID3','S3','T3'

union all

select 'TID4','S4','T4'

insert into #Territory_Assoc (FromTerrID,ToTerrID)

select (case when (a.SourceSegment+a.SourceTerritory) = (b.Segment+b.Territory) then b.Terr_ID else '' end),

(case when (a.TargetSegment+a.TargetTerritory) = (c.Segment+c.Territory) then c.Terr_ID else '' end)

from #TER_TER_ALIGNMENT a , #Territory b, #Territory c

where (a.SourceSegment+a.SourceTerritory) = (b.Segment+b.Territory) and

(a.TargetSegment+a.TargetTerritory) = (c.Segment+c.Territory)

select * from #Territory_Assoc

keethukumar Newbie Points: 4 More actions · Answer 2

Thanks a lot Arun,

But i just got an info that the data enterted into Ter_Ter_Alignment need not have data in both source segment and source territory or target segment and target territory. i,e data could just be like

Source SegmentSource TerritoryTarget SegmentTarget Territory

S1NULLS3NULL

S2T2S4T4

NULLT3NULLS2

S4T4S1T1

That is data can be provided for either source seg/ter and target seg/ter. please help me on this

arun.sas SSChampion Points: 11831 More actions · Answer 3

select (case when ((a.SourceSegment= b.Segment)or(a.SourceTerritory= b.Territory)) then b.Terr_ID else '' end),

(case when ((a.TargetSegment = c.Segment) or (a.TargetTerritory = c.Territory)) then c.Terr_ID else '' end)

from #TER_TER_ALIGNMENT a , #Territory b, #Territory c

where ((a.SourceSegment= b.Segment)or(a.SourceTerritory= b.Territory)) and

((a.TargetSegment = c.Segment) or (a.TargetTerritory = c.Territory))

keethukumar Newbie Points: 4 More actions · Answer 4

wow i just came up with the same code as urs!!! 😀

Thankfully i cud do something rite finally :hehe: thanks a ton!!!

keethukumar Newbie Points: 4 More actions · Answer 5

Hi for the code given,

select (case when ((a.SourceSegment= b.Segment)or(a.SourceTerritory= b.Territory)) then b.Terr_ID else '' end),

(case when ((a.TargetSegment = c.Segment) or (a.TargetTerritory = c.Territory)) then c.Terr_ID else '' end)

from #TER_TER_ALIGNMENT a , #Territory b, #Territory c

where ((a.SourceSegment= b.Segment)or(a.SourceTerritory= b.Territory)) and

((a.TargetSegment = c.Segment) or (a.TargetTerritory = c.Territory))

suppose user enters all the 4 i.e a.SourceSegment,a.SourceTerritory, a.TargetSegment,a.TargetTerritory and it so happens that the combination a.SourceSegment,a.SourceTerritory does not exist in the master table Territory, still this record will be accepted since we have an "OR" condition. The above code works fine when user enters either source seg or source terr and same case for targets. How do i check if the user enters the correct combination too??

arun.sas SSChampion Points: 11831 More actions · Answer 6

Hi,

This check may provide in various method, use the trigger in the Master table (#Territory ) to validate the combination are exists during add the record, and in the procedure also we can provide the validate by the parameter checking.