sql server - How to prevent duplicate or similar data from getting processed? - Database Administrators Stack Exchange


i have stored procedure calculates distance between 2 coordinate points:

create proc loc.calcdistancebypostalcode ( @from varchar(10), @to   varchar(10) ) begin     declare @data table (from_place varchar(200), from_region varchar(200), "to_place" varchar(200), to_region varchar(200), distance decimal(8,2))     declare @postal1 table (id int identity(1,1), place_name varchar(200), region_name varchar(200), latitude decimal(12,9), longitude decimal(12,9))     declare @postal2 table (id int identity(1,1), place_name varchar(200), region_name varchar(200), latitude decimal(12,9), longitude decimal(12,9))       insert @postal1 select distinct l.place_name, l.region_name, l.latitude, l.longitude                           loc.locations l                           cross join loc.locations_postal lp                           postal_code @from , lp.city_id = l.id     insert @postal2 select distinct l.place_name, l.region_name, l.latitude, l.longitude                           loc.locations l                           cross join loc.locations_postal lp                           postal_code @to , lp.city_id = l.id      insert @data      select         t1.place_name,  t1.region_name, t2.place_name, t2.region_name,         loc.fn_calcaerialdistance(t1.latitude, t1.longitude, t2.latitude, t2.longitude) -- function calculates distance     @postal1 t1     cross join @postal2 t2     t1.latitude <> t2.latitude , t1.longitude <> t2.longitude      declare @distance decimal(8,2) = 0     select * @data order distance desc     select @distance = distance @data     return @distance; end 

it calculates correct result if @from , @to different, gives redundant data if @from , @to same.

example: different @from , @to:

exec loc.calcdistancebypostalcode @from = '110%', @to = '400%'` 

result:

from_place  from_region to_place     to_region      distance delhi       delhi        mumbai      maharashtra    1160.65 new delhi   delhi        mumbai      maharashtra    1157.80 delhi       delhi        vasai-virar maharashtra    1115.68 new delhi   delhi        vasai-virar maharashtra    1112.87 

example: same @from , @to:

exec loc.calcdistancebypostalcode @from = '110%', @to = '110%'` 

result:

from_place  from_region to_place    to_region   distance new delhi   delhi       delhi       delhi       3.51 delhi       delhi       new delhi   delhi       3.51 

do need write code check if distance same compare from_place to_place rows having same distance? or optimised way prevent duplicate data?

try adding and t1.id > t2.id clause , make sure join happens once . following query

select t1.place_name, t1.region_name, t2.place_name, t2.region_name, loc.fn_calcaerialdistance(t1.latitude, t1.longitude, t2.latitude, t2.longitude) -- function calculates distance  @postal1 t1 cross join @postal2 t2  t1.latitude <> t2.latitude , t1.longitude <> t2.longitude 

Comments

Popular posts from this blog

analysis of two transistors ib,ic,ie? - Electrical Engineering Stack Exchange

Choosing and replace capacitors for audio amplifier - Electrical Engineering Stack Exchange

sql server 2008 r2 - LogWriter: Operating system error 1117 - Database Administrators Stack Exchange