parkland (and generic) location linker

parklandlocationlinker.sql

--function to trim street info
IF object_id(N'fn_doStuff', N'FN') IS NOT NULL
DROP FUNCTION fn_doStuff
GO


CREATE FUNCTION fn_doStuff
                 ( @value nvarchar(500) )
RETURNS varchar
AS
BEGIN
		set @value = REPLACE(@value, '-', '') 
		set @value = REPLACE(@value, '.', '')
		set @value = REPLACE(@value, ',', '')
		set @value = REPLACE(@value, ' ', '') 
		set @value = REPLACE(@value COLLATE Latin1_General_CS_AS, 'PO', '') 

		--turn long forms (like "street", "avenue", etc) into short form ("st", "av") - case INsensitive
		set @value = REPLACE(@value COLLATE Latin1_General_CS_AS, 'street', 'st') 
		set @value = REPLACE(@value COLLATE Latin1_General_CS_AS, 'avenue', 'av')
		set @value = REPLACE(@value COLLATE Latin1_General_CS_AS, 'ave', 'st') 
		set @value = REPLACE(@value COLLATE Latin1_General_CS_AS, 'pointe', 'pt') 
		set @value = REPLACE(@value COLLATE Latin1_General_CS_AS, 'point', 'pt') 
		set @value = REPLACE(@value COLLATE Latin1_General_CS_AS, 'road', 'rd') 
		set @value = REPLACE(@value COLLATE Latin1_General_CS_AS, 'close', 'cl') 
		set @value = REPLACE(@value COLLATE Latin1_General_CS_AS, 'place', 'pl')
		set @value = REPLACE(@value COLLATE Latin1_General_CS_AS, 'crescent', 'cr') 
		set @value = REPLACE(@value COLLATE Latin1_General_CS_AS, 'range road', 'rr') 
		set @value = REPLACE(@value COLLATE Latin1_General_CS_AS, 'rangeroad', 'rr') 
		return @value
END
GO


SET NOCOUNT ON
GO


--Step 1

--go through contacts for each student and compare addresses. if the same, mark as Lives With.
--------------------------------------------------------
--------------------------------------------------------
--------------------------------------------------------

--declare all variables
declare @cr_iStudentID int
declare @cr_iContactID int
declare @cr_iContactRelationID int

declare @cr_iStudentID_l int
declare @cr_iContactID_l int

declare @cr_iStudentID_l_value varchar(max)
declare @cr_iContactID_l_value varchar(max)

declare @cr_iStudentID_1_houseno varchar(1000)
declare @cr_iContactID_1_houseno varchar(1000)


--create a temp table for putting status/debug stuff into
IF OBJECT_ID('tempdb..#cContactRelation_iContactID_iStudentID_info') IS NOT NULL
    DROP TABLE #cContactRelation_iContactID_iStudentID_info
create table #cContactRelation_iContactID_iStudentID_info (
	c_iContactID int,
	c_cLastname varchar(100),
	c_cFirstname varchar(100),
	c_Address varchar(100),
	s_Address varchar(100),
	s_cLastname varchar(100),
	s_cFirstname varchar(100),
	s_iStudentID int,
	cr_iContactRelationID int
)

-- go through each ContactRelation record and compare addresses for the related student and contact.
-------------------------------------------------------------------------
declare cContactRelation_iContactID_iStudentID cursor for
	select istudentid, iContactID, iContactRelationID 
	from ContactRelation cr
	where cr.lLivesWithStudent = 0
	
open cContactRelation_iContactID_iStudentID
fetch cContactRelation_iContactID_iStudentID into @cr_iStudentID, @cr_iContactID, @cr_iContactRelationID
while @@fetch_status=0
begin


		--set istudentid, streetnumber, housenumber variables for the current student 
		set @cr_iStudentID_l = (select ilocationid from Student where iStudentID = @cr_iStudentID)
		set @cr_iStudentID_l_value = (select cStreet from Location where iLocationID = @cr_iStudentID_l)
		set @cr_iStudentID_1_houseno = (select cHouseNo from Location where iLocationID = @cr_iStudentID_l)
		
		--function to remove spacing and special chars and do stuff
		set @cr_iStudentID_l_value = (select dbo.fn_doStuff(@cr_iStudentID_l_value))
		

	
		--------------------------------------------------------------------------------------------
	
	
		set @cr_iContactID_l = (select ilocationid from Contact where iContactID = @cr_iContactID)
		set @cr_iContactID_l_value = (select cStreet from Location where iLocationID = @cr_iContactID_l)
		set @cr_iContactID_1_houseno = (select cHouseNo from Location where iLocationID = @cr_iContactID_l)
		
		
		--function to remove spacing and special chars and do stuff
		set @cr_iContactID_l_value = ( dbo.fn_doStuff(@cr_iContactID_l_value))
		
	
		--if they are essentially the same address then we will make lives with = 1
		if( (@cr_iContactID_l_value = @cr_iStudentID_l_value) AND ( @cr_iStudentID_1_houseno = @cr_iContactID_1_houseno))
		BEGIN

			
			update ContactRelation set lLivesWithStudent = 1 where iContactRelationID = @cr_iContactRelationID


			--log our change
			insert into #cContactRelation_iContactID_iStudentID_info (
				c_iContactID,
				c_cLastname, 
				c_cFirstname, 
				c_Address, 
				s_Address,
				s_cLastname, 
				s_cFirstname,
				s_iStudentID,
				cr_iContactRelationID)
			SELECT
				@cr_iContactID,
				c.clastname,
				c.cfirstname, 
				(@cr_iContactID_1_houseno + @cr_iContactID_l_value), 
				(@cr_iStudentID_1_houseno + @cr_iStudentID_l_value), 
				s.cLastName, 
				s.cfirstname ,
				@cr_iStudentID,
				@cr_iContactRelationID
			from Contact c
			join Student s
				on s.iStudentID = @cr_iStudentID
			join ContactRelation cr
				on cr.iStudentID = s.iStudentID and c.iContactID = cr.iContactID
			where c.iContactID = @cr_iContactID
			
			
		END
	
	
	
fetch next from cContactRelation_iContactID_iStudentID into @cr_iStudentID, @cr_iContactID, @cr_iContactRelationID
end
close cContactRelation_iContactID_iStudentID
deallocate cContactRelation_iContactID_iStudentID



--print the status results
select 
	'contact ' + cast(c_cLastname as varchar) + ', ' + cast(c_cFirstname as varchar) + '(' + cast(c_iContactID as varchar) + ')' +
	' was set to LivesWith with ' +
	'student ' + cast(s_cLastname as varchar) + ', ' + cast(s_cFirstname as varchar) + '(' + cast(s_iStudentID as varchar)+ ')' +
	'- contactrelationid ' + cast(cr_iContactRelationID as varchar)
from #cContactRelation_iContactID_iStudentID_info




--step 2

--------------------------------------------------------
--------------------------------------------------------
--------------------------------------------------------


declare @ifamilyid int

declare @master_studentid int --the master student
declare @istudentid int --"child" student


declare @master_ilocationid int
declare @child_ilocationid int

declare @cStreetM varchar(100)
declare @cStreetC varchar(100)



--create a temp table for putting status/debug stuff on Student Familyid location linking into
IF OBJECT_ID('tempdb..#Student_FamilyUpdate_info') IS NOT NULL
	DROP TABLE #Student_FamilyUpdate_info
	
create table #Student_FamilyUpdate_info (
	s_master_cLastname varchar(100),
	s_master_cFirstname varchar(100),
	s_istudentid_master int,
	s_istudentid_child int,
	s_child_cLastname varchar(100),
	s_child_cfirstname varchar(100),
	s_familyid int	
)

--create a temp table for putting status/debug stuff on Contact location liveswith updates into
IF OBJECT_ID('tempdb..#Student_FamilyUpdate_info') IS NOT NULL
	DROP TABLE #Contact_location_update_LivesWith
	
create table #Contact_location_update_LivesWith (
	c_iContactID int,
	c_cLastname varchar(100),
	c_cFirstname varchar(100),
	c_Address varchar(100),
	s_Address varchar(100),
	s_cLastname varchar(100),
	s_cFirstname varchar(100),
	s_iStudentID int,
	cr_iContactRelationID int
)




--------------------------------------------------------

declare cOuter cursor for
	select ifamilyid from Student 
	where iFamilyID != 0 
	group by iFamilyID 
	having COUNT(ifamilyid) > 1
	
open cOuter
fetch cOuter into @ifamilyid
while @@fetch_status=0
begin

	--set the "master" student that other students will have their location matched to
	set @master_studentid =	(
		select max(istudentid) from Student 
				where lCurrent = (
						select max(cast(lcurrent as int)) from student 
						where iFamilyID = @ifamilyid) 
				and iFamilyID = @ifamilyid)
	
	
	--set the "master" locationid
	set @master_ilocationid = (select ilocationid from student where iStudentID = @master_studentid)
	set @cStreetM = (select cstreet from location where iLocationID = @master_ilocationid)
	
	
	--function to remove spacing and special chars and do stuff
	set @cStreetM = (select dbo.fn_doStuff(@cStreetM))
		
	
	
	---------------------------------------------------------------
	declare cInner cursor for
		select istudentid from Student 
		where iFamilyID like @ifamilyid 
		and istudentid not like @master_studentid --we dont want to process the "master" student
		and iLocationID not like @master_ilocationid --we dont want to process students that have location records already linked
		
	
	
	open cInner
	fetch cInner into @istudentid
	while @@fetch_status=0
	begin

			set @child_ilocationid = (select iLocationID from Student where iStudentID = @istudentid)
			set @cStreetC = (select cstreet from Location where iLocationID = @child_ilocationid)

			--function to remove spacing and special chars and do stuff
			set @cStreetC = (select dbo.fn_doStuff(@cStreetC))
	

			if( (@cStreetC like @cStreetM) )
				select 
					@ifamilyid as 'ifamilyid', 
					(select cstudentnumber from student where istudentid = @master_studentid) as 'master_studentid', 
					(select cstudentnumber from student where istudentid = @istudentid) as 'child_studentid', 
					@master_ilocationid as 'master_ilocationid', 
					@child_ilocationid as 'child_ilocationid', 
					@cStreetM as 'master_cStreet',
					@cStreetC as 'child_cStreet'
					
					
			--set
			update student set iLocationID = @master_ilocationid where iStudentID = @istudentid
			update StudentAddress set iLocationID = @master_ilocationid where iStudentID = @istudentid
		
		
			--log our change
			insert into #Student_FamilyUpdate_info (
				s_master_cLastname,
				s_master_cFirstname,
				s_istudentid_master,
				s_istudentid_child,
				s_child_cLastname,
				s_child_cfirstname,
				s_familyid)
			SELECT
				(select cLastname from student where iStudentid = @master_studentid),
				(select cFirstname from student where iStudentid = @master_studentid),
				@master_studentid,
				@istudentid,
				(select cLastname from student where iStudentid = @istudentid),
				(select cFirstname from student where iStudentid = @istudentid),
				@ifamilyid
				
			
			
	fetch next from cInner into @istudentid
	end
	close cInner
	deallocate cInner
	---------------------------------------------------------------
	
	
	
	
	--step 3		
	
	--now make contacts marked "lives with" to be the same address as the student.
	--the student's ilocationid will be used.
	--------------------------------------------------------
	--------------------------------------------------------
	--------------------------------------------------------
	
	if(		(select top 1 c.icontactid from contactrelation cr
			 join contact c
				on c.iContactID = cr.iContactID
			 join Location l
				on l.iLocationID = c.iLocationID
			 where cr.iStudentID = @master_studentid
			 and lLivesWithStudent = 1
			 and c.iLocationID != @master_ilocationid) 
			is not null)
	BEGIN
			
			
			--set contact location to = student location
			update contact
			set ilocationid = @master_ilocationid
			from contactrelation cr
			join contact c
				on c.iContactID = cr.iContactID
			join Location l
				on l.iLocationID = c.iLocationID
			where cr.iStudentID = @master_studentid
			and lLivesWithStudent = 1
			and c.iLocationID != @master_ilocationid
			
			
			
			--log our change
			insert into #cContactRelation_iContactID_iStudentID_info (
				c_iContactID,
				c_cLastname, 
				c_cFirstname, 
				c_Address, 
				s_Address,
				s_cLastname, 
				s_cFirstname,
				s_iStudentID,
				cr_iContactRelationID)
			SELECT
				@cr_iContactID,
				c.clastname,
				c.cfirstname, 
				(@cr_iContactID_1_houseno + @cr_iContactID_l_value), 
				(@cr_iStudentID_1_houseno + @cr_iStudentID_l_value), 
				s.cLastName, 
				s.cfirstname ,
				@cr_iStudentID,
				@cr_iContactRelationID
			from contactrelation cr
				join contact c
					on c.iContactID = cr.iContactID
				join student s
					on s.iStudentID = cr.iStudentID
				where s.istudentid = @master_studentid


	END	
	
	
fetch next from cOuter into @ifamilyid
end
close cOuter
deallocate cOuter


---------------------------------------------


--step 4

---------------------------------------------
---------------------------------------------
---------------------------------------------
---------------------------------------------


--now we need to determine who is a "duplicate" contact
---------------


--find duplicate contacts (based on firstname, lastname, phone?, location)? 
--should this be weighting-based?
	
	--make one of the duplicate contacts "primary"
	
	--find related duplicate contactrelation records
	
	--make all duplicate contactrelation records point to the "primary" contact

	--delete any duplicate contactrelation records
	
	
	
	--delete any contacts no longer linked to anything
	
	--delete any location records no longer linked to anything
	
	

------------------------------------------------------------------------------------


declare @contactLastname varchar(100)
declare @contactFirstname varchar(100)

declare @contactMaster int	-- this is the contact we are going to use as the master
declare @contactChild int	-- this is the contact we are goin to check against @contactMaster


--this will go through all the contacts and find ones with duplicate first/last name which may require merging
declare cContactOuterLoop cursor for
	select cLastname, cFirstname from contact
	where clastname != '' 
	and cFirstName != ''
	group by cLastName, cFirstName 
	having COUNT(*) > 1

open cContactOuterLoop
fetch cContactOuterLoop into @contactLastname, @contactFirstname
while @@fetch_status=0
begin
	
	
		--determine master contact
		set @contactMaster = (	select top 1 icontactid from Contact 
								where cLastName = @contactLastname 
								and cFirstName = @contactFirstname
							 )
		--debug
		select @contactMaster
	
	
		--grab child contacts with same last and first name as @contactMaster
		declare cContactInnerLoop cursor for
			select iContactID from Contact 
			where cLastName = @contactLastname
			and cFirstName = @contactFirstname
			and iContactID != @contactMaster
	
		open cContactInnerLoop
		fetch cContactInnerLoop into @contactChild
		while @@fetch_status=0
		begin
			
			--debug
			--select @contactChild, @contactMaster, @contactLastname, @contactFirstname
			
			--determine if this contact is a 100% match or not. ill make this weighting-based later.
			if	(
						--make a big string of all the fields we are comparing for the MASTER contact
						(select 
							c.clastname + 
							c.cfirstname + 
							l.cApartment + 
							l.cHouseNo + 
							l.cStreet + 
							cast(l.iLV_CityID as varchar) + 
							cast(l.cPostalCode as varchar) + 
							l.cPhone 
						from Contact c
						join location l
							on l.iLocationID = c.iLocationID
						where c.iContactID = @contactMaster
						)
						
						=
						
						--make a big string of all the fields we are comparing for the CHILD contact
						(select 
							c.clastname + 
							c.cfirstname + 
							l.cApartment + 
							l.cHouseNo + 
							l.cStreet + 
							cast(l.iLV_CityID as varchar) + 
							cast(l.cPostalCode as varchar) + 
							l.cPhone 
						from Contact c
						join location l
							on l.iLocationID = c.iLocationID
						where c.iContactID = @contactChild
						)
			
				) 
				BEGIN
						-- if we are at this point it means that there is a 100% match
				
						--debug
						select cast(@contactChild as varchar) + ' contactrelation records will be relinked to ' + cast(@contactMaster as varchar)
						
						--relink contactrelation records to the master contact
						update contactrelation set icontactid = @contactMaster where iContactID = @contactChild
				
				
				END
			

			
		fetch next from cContactInnerLoop into @contactChild
		end
		close cContactInnerLoop
		deallocate cContactInnerLoop
		
	
	
	
	
fetch next from cContactOuterLoop into @contactLastname, @contactFirstname
end
close cContactOuterLoop
deallocate cContactOuterLoop