USE [HumanFind]
GO
/****** Object:  StoredProcedure [dbo].[spSSNSearch]    Script Date: 01/22/2009 10:40:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Doron
-- Create date: Jan 14,2009
-- Description:	Main SSN Search
-- =============================================
ALTER PROCEDURE [dbo].[spSSNSearch] 
	-- Add the parameters for the stored procedure here
	@First VarChar(15), 
	@Last VarChar(20)
--	@State Char(2) 
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	Declare @TotalRec Int

    -- Insert statements for procedure here
	SELECT TOP 100 
		dbo.UserDetails.UserDetails_PK ,
		dbo.UserDetails.First,
		dbo.UserDetails.Last,
		dbo.UserDetails.DOB ,
		dbo.UserDetails.Phone,
		dbo.UserDetails.eStatus,
		dbo.Address.Address,
		dbo.Address.City,
		dbo.Address.State,
		dbo.Address.Zip,
		dbo.Address.Reported1,
		dbo.Address.Reported2,
		dbo.Address.AddressSortOrder,
		dbo.AliasesSSN.First AS FirstA ,
		dbo.AliasesSSN.Mid  AS MidA ,
		dbo.AliasesSSN.Last AS LastA,
		dbo.MoversSSN.MatchFlag ,
		dbo.MoversSSN.MoveType ,
		dbo.MoversSSN.MoveDate  
	FROM 
		dbo.UserDetails 
		LEFT JOIN dbo.Address
		ON dbo.UserDetails.UserDetails_PK = dbo.Address.UserDetails_FK
		LEFT JOIN dbo.AliasesSSN
		ON dbo.UserDetails.UserDetails_PK = dbo.AliasesSSN.UserDetails_FK
		LEFT JOIN dbo.MoversSSN
		ON dbo.UserDetails.UserDetails_PK = dbo.MoversSSN.UserDetails_FK
	WHERE dbo.UserDetails.First LIKE @First + '%' AND dbo.UserDetails.Last LIKE @Last + '%'
	ORDER BY SSN,State, First, Last
-- AND dbo.Address.State ='@State' 
END

Select @TotalRec = @@ROWCOUNT
IF @TotalRec = 0
--Try to grab from the Emails table

SELECT TOP 100 
	dbo.EMails.Emails_PK ,
	dbo.Emails.Email,
	dbo.Emails.First,
	dbo.Emails.Last,
	dbo.Emails.Gender ,
	dbo.Emails.DOB ,
	dbo.Emails.Address ,
	dbo.Emails.City,
	dbo.Emails.State,
	dbo.Emails.Zip,	
	dbo.Emails.Phone,
	dbo.Emails.IPAddress,
	dbo.Emails.OptDate,
	dbo.Emails.Source,
	dbo.Emails.HouseType AS HouseTypeEmail,
	dbo.MoversEmails.Address AS AddressMov,
	dbo.MoversEmails.City AS CityMov,
	dbo.MoversEmails.State AS StateMov,
	dbo.MoversEmails.Zip AS ZipMov,
	dbo.MoversEmails.MoveDate,
	dbo.MoversEmails.MatchFlag,
	dbo.MoversEmails.MoveType,
	dbo.MoversEmails.HouseType
FROM 
	dbo.Emails 
	LEFT JOIN dbo.MoversEmails
	ON dbo.Emails.Emails_PK = dbo.MoversEmails.Emails_FK
WHERE dbo.Emails.First LIKE @First + '%' AND dbo.Emails.Last LIKE @Last + '%' 
ORDER BY State,First, Last 

--AND dbo.Emails.State = 'NY'

IF @TotalRec > 0
	PRINT STR(@TotalRec) + ' were updated';