USE [CoalS]
GO
/****** Object:  StoredProcedure [dbo].[tspSelectRiskJunk]    Script Date: 07/18/2006 07:36:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*

EXEC CoalS.dbo.tspSelectRiskJunk
  NULL --@Address_ID int
, 190 --@Address_Number_NBR int
, NULL --@Address_High_NBR int
, NULL --@State_CD int
, NULL --@Ste_or_Apt_CD int
, NULL --@Address_Ste_or_Apt_NBR int
, NULL --@Address_Directional_Prefix_TX varchar 4
, NULL --@Address_Directional_Suffix_TX varchar 4
, NULL --@Address_ZIP_CD varchar(9)
, NULL --@Address_Street_Type_TX varchar(32)
, NULL --@Address_County_TX varchar(50)
, NULL --@Address_City_TX varchar(64)
, 'Squa' --@Address_Rootword_TX varchar(64)

*/

CREATE PROC [dbo].[tspSelectRiskJunk]
  @Address_ID                    int         = NULL
, @Address_Number_NBR            int         = NULL
, @Address_High_NBR              int         = NULL
, @State_CD                      int         = NULL
, @Ste_or_Apt_CD                 int         = NULL
, @Address_Ste_or_Apt_NBR        int         = NULL
, @Address_Directional_Prefix_TX varchar(4)  = NULL
, @Address_Directional_Suffix_TX varchar(4)  = NULL
, @Address_ZIP_CD                varchar(9)  = NULL
, @Address_Street_Type_TX        varchar(32) = NULL
, @Address_County_TX             varchar(50) = NULL
, @Address_City_TX               varchar(64) = NULL
, @Address_Rootword_TX           varchar(64) = NULL

AS

SET QUOTED_IDENTIFIER ON 
SET ANSI_NULLS ON 
SET NOCOUNT ON 

BEGIN TRANSACTION

DECLARE
	@RC int

/* ^ His Code ^ */
/* v  My Code v */


SELECT TOP 100
       ad.Address_ID
     , rs.Risk_Number
     , ad.Address_Number_NBR
     , ad.Address_High_NBR
     , ad.Address_Directional_Prefix_TX
     , ad.Address_Rootword_TX
     , ad.Address_Street_Type_TX
     , ad.Address_Directional_Suffix_TX
     , sa.Ste_or_Apt_CD
     , ad.Address_Ste_or_Apt_NBR
     , ad.Address_City_TX
     , ad.Address_County_TX
     , st.State_CD
     , ad.Address_ZIP_CD
   FROM [CoalS].[dbo].[Address] AS ad
   JOIN CoalS.dbo.State AS st
      ON st.State_ID = ad.Address_State_CD
   LEFT OUTER JOIN CoalS.dbo.Suite_or_Apartment AS sa
      ON sa.Ste_or_Apt_ID = ad.Address_Ste_or_Apt_CD
   JOIN CoalS.dbo.Risk AS rs
      ON rs.Address_ID = ad.Address_ID
ORDER BY CASE WHEN st.state_id = @State_CD  THEN 0 --change 'WA' to @incomingvar
              WHEN st.state_id IS NULL      THEN 4
              ELSE 2
         END

       , CASE WHEN Address_County_TX = @Address_County_TX                    THEN 0 --change 'Gogebic' to @incomingvar
              WHEN Address_County_TX LIKE ( @Address_County_TX + '%' )       THEN 2
              WHEN Address_County_TX LIKE ( '%' + @Address_County_TX + '%' ) THEN 4
              WHEN Address_County_TX IS NULL THEN 8
              ELSE 16
         END
--city
       , CASE WHEN Address_City_TX = @Address_City_TX                    THEN 0 --the record exactly matches the variable
              WHEN Address_City_TX LIKE ( @Address_City_TX + '%' )       THEN 2 --the record starts with the variable, i.e. Ash returns Ash, Ashe, & Asheville but NOT Washington, nor Nashville
              WHEN Address_City_TX LIKE ( '%' + @Address_City_TX + '%' )        --the record contains the variable passed in Ash returns Ash, Ashe, Asheville, Washington, Nashville, & Wash
                 AND ( st.state_id = @State_CD )                         THEN 4 --AND the state passed in matches
              WHEN Address_City_TX IS NULL                               THEN 8 --There is no City record in the database for the row
              ELSE 16
         END
--zip
       , CASE WHEN Address_ZIP_CD LIKE @Address_ZIP_CD           THEN  0 --Exact match
              WHEN Address_ZIP_CD = LEFT ( @Address_ZIP_CD , 5 ) THEN  2 --Row has exact match on first 5 characters of what is passed in
              WHEN Address_ZIP_CD LIKE ( @Address_ZIP_CD + '%' ) THEN  4 --First 5 digits of row has exact match on what is passed in
              WHEN Address_ZIP_CD IS NULL                        THEN 16 --No ZIP in the database for the row
              ELSE 8                                                     --Any other condition
         END

       , CASE WHEN Address_Rootword_TX = @Address_Rootword_TX                THEN  0 --the record exactly matches the variable
              WHEN Address_Rootword_TX LIKE ( @Address_Rootword_TX + '%' )   THEN  2 --the record starts with the variable, i.e. Ash returns Ash, Ashe, & Asheville but NOT Washington, nor Nashville
              WHEN DIFFERENCE(Address_Rootword_TX, @Address_Rootword_TX) = 4 THEN  4 --if the Difference of the Soundex of the row and the variable is 4 (They sound the same by soundex rules.)
              WHEN DIFFERENCE(Address_Rootword_TX, @Address_Rootword_TX) = 3 THEN  8 --if the Difference of the Soundex of the row and the variable is 3 (They sound similar by soundex rules.)
              WHEN Address_Rootword_TX LIKE '%' + @Address_Rootword_TX + '%' THEN 16 --the record contains the variable passed in Ash returns Ash, Ashe, Asheville, Washington, Nashville, & Wash
              ELSE 32                                                                --Any other condition
         END
       , CASE WHEN Address_Number_NBR = @Address_Number_NBR                     THEN  0 --Address Number exactly matches the one passed in
              WHEN Address_High_NBR > @Address_Number_NBR
                 AND Address_Number_NBR < @Address_Number_NBR                   THEN  2
              WHEN Address_Number_NBR /    10 = ( @Address_Number_NBR /    10 )
                 AND Address_Rootword_TX = @Address_Rootword_TX                 THEN  4 --Address Number exactly matches the one passed in (except the Ones' Position)
              WHEN Address_Number_NBR /   100 = ( @Address_Number_NBR /   100 )
                 AND Address_Rootword_TX = @Address_Rootword_TX                 THEN  8 --Address Number exactly matches the one passed in (before the Tens' Position)
              WHEN Address_Number_NBR /  1000 = ( @Address_Number_NBR /  1000 )
                 AND Address_Rootword_TX = @Address_Rootword_TX                 THEN 16 --Address Number exactly matches the one passed in (before the Hundreds' Position)
              WHEN Address_Number_NBR / 10000 = ( @Address_Number_NBR / 10000 )
                 AND Address_Rootword_TX = @Address_Rootword_TX                 THEN 32 --Address Number exactly matches the one passed in (before the Thousands' Position)
              WHEN Address_Number_NBR IS NULL                                   THEN 64 --the record is null for this row/column
              ELSE 128
         END
       , Address_Number_NBR
       , CASE WHEN Address_Directional_Prefix_TX LIKE @Address_Directional_Prefix_TX  THEN   0 --Address Prefix exactly matches
              WHEN Address_Directional_Prefix_TX LIKE 'N'                             THEN   4 --Address Prefix records that are N
              WHEN Address_Directional_Prefix_TX LIKE 'NE'                            THEN   8 --Address Prefix records that are NE
              WHEN Address_Directional_Prefix_TX LIKE 'E'                             THEN  16 --Address Prefix records that are E
              WHEN Address_Directional_Prefix_TX LIKE 'SE'                            THEN  32 --Address Prefix records that are SE
              WHEN Address_Directional_Prefix_TX LIKE 'S'                             THEN  64 --Address Prefix records that are S
              WHEN Address_Directional_Prefix_TX LIKE 'SW'                            THEN 128 --Address Prefix records that are SW
              WHEN Address_Directional_Prefix_TX LIKE 'W'                             THEN 256 --Address Prefix records that are W
              WHEN Address_Directional_Prefix_TX LIKE 'NW'                            THEN 512 --Address Prefix records that are NW (clockwise around compass)
              WHEN Address_Directional_Prefix_TX IS NULL                              THEN   2 --the record is null for this row/column (the two returns these second)
              ELSE 1024                                                                        --Any other condition
         END
       , CASE WHEN Address_Directional_Suffix_TX LIKE @Address_Directional_Suffix_TX  THEN   0 --Address Suffix exactly matches
              WHEN Address_Directional_Suffix_TX LIKE 'N'                             THEN   4 --Address Suffix records that are N
              WHEN Address_Directional_Suffix_TX LIKE 'NE'                            THEN   8 --Address Suffix records that are NE
              WHEN Address_Directional_Suffix_TX LIKE 'E'                             THEN  16 --Address Suffix records that are E
              WHEN Address_Directional_Suffix_TX LIKE 'SE'                            THEN  32 --Address Suffix records that are SE
              WHEN Address_Directional_Suffix_TX LIKE 'S'                             THEN  64 --Address Suffix records that are S
              WHEN Address_Directional_Suffix_TX LIKE 'SW'                            THEN 128 --Address Suffix records that are SW
              WHEN Address_Directional_Suffix_TX LIKE 'W'                             THEN 256 --Address Suffix records that are W
              WHEN Address_Directional_Suffix_TX LIKE 'NW'                            THEN 512 --Address Suffix records that are NW
              WHEN Address_Directional_Suffix_TX IS NULL                              THEN   2 --the record is null for this row/column (the two returns these second)
              ELSE 1024                                                                        --Any other condition
         END
       , CASE WHEN Address_Street_Type_TX LIKE @Address_Street_Type_TX THEN 0 --Address Street Type exactly matches
              WHEN Address_Street_Type_TX IS NULL  THEN 2                     --the record is null for this row/column (the two returns these second)
              ELSE 4                                                          --Any other condition (Street Types that have a record, but do not match the Street Type passed in)
         END
       , Address_Street_Type_TX
       , st.state_cd
       , Address_County_TX
       , Address_City_TX
       , Address_Rootword_TX

IF @RC <> 0 
BEGIN
	IF @@TRANCOUNT > 0 
	BEGIN
		ROLLBACK TRANSACTION
	END
	RETURN(@RC)
END

COMMIT TRANSACTION

RETURN(0)