gistfile1.txt

/****** Object:  StoredProcedure [DNorm].[usp_Data_ModeDerivation]    Script Date: 12/21/2016 12:36:54 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

use NormLogic
Go




CREATE PROC [Norm].[usp_Data_ModeDerivation] (
 @EnvironmentID varchar (100),
 @FB_ID varchar (23),
 @ServiceValue nvarchar (200),
 @OriginName1 nvarchar (255),
 @OriginName2 nvarchar (255),
 @OriginCity nvarchar (150),
 @OriginCountry nvarchar (150),
 @DestName1 nvarchar (255),
 @DestName2 nvarchar (255),
 @DestCity nvarchar (150),
 @DestCountry nvarchar (150)
)

--Create By Chris Salter 
/*
Description: 
Variables passed will attempt to be matched on in the configuration table dbo.ModebyData, 
once match is attempted and successful, 
the results will be placed into the metrics table dbo.ModebyDataMetrics.



*/
As

Begin 

Declare @TempTable1 as Table (

		FB_ID varchar (23) , 
		VEND_LABL varchar (10) , 
		ACCT_NUM_VEND_BLNG varchar (50) ,
		Vend_Srvc_Name varchar (250) ,
		FB_ACTUAL_WT SQL_Variant ,
		AL_ORIG_1 varchar (150) ,
		AL_ORIG_2 varchar (150) ,
		AL_CITY_ORIG varchar (150) ,
		AL_CNTRY_CODE_ORIG varchar (50) ,
		AL_Dest_1 varchar (150) ,
		AL_Dest_2 varchar (150) ,
		AL_CITY_Dest varchar (150) ,
		AL_CNTRY_CODE_Dest varchar (50) 

)

Declare @TempTable2 as Table (

		FB_ID varchar (23) , 
		VEND_LABL varchar (10) , 
		ACCT_NUM_VEND_BLNG varchar (50) ,
		Vend_Srvc_Name varchar (250) ,
		Vend_Srvc_Name_Weight bigint,
		Min_Weight bigint ,
		Max_Weight bigint,
		Account_Num_Weight bigint ,
		FB_ACTUAL_WT SQL_Variant,
		AL_ORIG_1 varchar (150) ,
		Origin_1_Weight bigint ,
		AL_ORIG_2 varchar (150) ,
		Origin_2_Weight bigint,
		AL_CITY_ORIG varchar (150) ,
		Origin_City_Weight bigint ,
		AL_CNTRY_CODE_ORIG varchar (50) ,
		Origin_Country_Weight bigint ,
		AL_Dest_1 varchar (150) ,
		Dest_1_Weight bigint ,
		AL_Dest_2 varchar (150) ,
		Dest_2_Weight bigint ,
		AL_CITY_Dest varchar (150) ,
		Dest_City_Weight bigint ,
		AL_CNTRY_CODE_Dest varchar (50) ,
		Dest_Country_Weight bigint,
		Norm_Mode varchar (50),
		Norm_Mode_Type varchar (50),
		[RecordDtmUtc] [datetime],
		[User] varchar (200)
)
Declare @TempTable3 as Table (

		FB_ID varchar (23) , 
		VEND_LABL varchar (10) , 
		ACCT_NUM_VEND_BLNG varchar (50) ,
		Vend_Srvc_Name varchar (250) ,
		Vend_Srvc_Name_Weight bigint,
		Min_Weight bigint ,
		Max_Weight bigint,
		Account_Num_Weight bigint ,
		FB_ACTUAL_WT SQL_Variant,
		AL_ORIG_1 varchar (150) ,
		Origin_1_Weight bigint ,
		AL_ORIG_2 varchar (150) ,
		Origin_2_Weight bigint,
		AL_CITY_ORIG varchar (150) ,
		Origin_City_Weight bigint ,
		AL_CNTRY_CODE_ORIG varchar (50) ,
		Origin_Country_Weight bigint ,
		AL_Dest_1 varchar (150) ,
		Dest_1_Weight bigint ,
		AL_Dest_2 varchar (150) ,
		Dest_2_Weight bigint ,
		AL_CITY_Dest varchar (150) ,
		Dest_City_Weight bigint ,
		AL_CNTRY_CODE_Dest varchar (50) ,
		Dest_Country_Weight bigint,
		Norm_Mode varchar (50),
		Norm_Mode_Type varchar (50),
		[RecordDtmUtc] [datetime],
		[User] varchar (200)
)

Declare @TempTable4 as Table (

		FB_ID varchar (23) , 
		VEND_LABL varchar (10) , 
		ACCT_NUM_VEND_BLNG varchar (50) ,
		Vend_Srvc_Name varchar (250) ,
		FB_ACTUAL_WT SQL_Variant  ,
		AL_ORIG_1 varchar (150) ,
		AL_ORIG_2 varchar (150) ,
		AL_CITY_ORIG varchar (150) ,
		AL_CNTRY_CODE_ORIG varchar (50) ,
		AL_Dest_1 varchar (150) ,
		AL_Dest_2 varchar (150) ,
		AL_CITY_Dest varchar (150) ,
		AL_CNTRY_CODE_Dest varchar (50) ,
		[Total_Score] bigint,
		Norm_Mode varchar (50),
		Norm_Mode_Type varchar (50),
		[RecordDtmUtc] [datetime],
		[User] varchar (200)
)


insert into @TempTable1
Select 

FB_ID, 
VEND_LABL, 
ACCT_NUM_VEND_BLNG,
	Case 
		when @ServiceValue IS Null then 'n/a' 
		else @ServiceValue 
	end as 'Vend_Srvc_Name',
FB_ACTUAL_WT as FB_ACTUAL_WT,
	Case 
		when @OriginName1 IS Null then 'n/a'
		else @OriginName1 
	end as 'AL_ORIG_1',
Case 
		when @OriginName2 IS Null then 'n/a' 
		else @OriginName2 
	end as 'AL_ORIG_2',
	Case 
		when @OriginCity IS Null then 'n/a' 
		else @OriginCity 
	end as 'AL_CITY_ORIG',
	Case 
		when @OriginCountry IS Null then 'n/a' 
		else @OriginCountry 
	end as 'AL_CNTRY_CODE_ORIG',
	Case 
		when @DestName1 IS Null then 'n/a' 
		else @DestName1 
	end as 'AL_Dest_1',
	Case 
		when @DestName2 IS Null then 'n/a' 
		else @DestName2 
	end as 'AL_Dest_2',
	Case 
		when @DestCity IS Null then 'n/a' 
		else @DestCity 
	end as 'AL_CITY_Dest',
	Case 
		when @DestCountry IS Null then 'n/a' 
		else @DestCountry 
	end as 'AL_CNTRY_CODE_Dest'
From FRGHT_BL (nolock) A
where FB_ID = @FB_ID


---------------------------------------------------------
-- Lane / Service/ Account Number Evaluation
---------------------------------------------------------

insert into @TempTable2
Select

A.FB_ID,
A.Vend_LAbl,
ACCT_NUM_VEND_BLNG,
A.Vend_Srvc_Name,
CASE 
	When B.Vend_Srvc_Name='*' then 0 
	else 1000
end as 'Vend_Srvc_Name_Weight',
0 as 'Min_Weight',
0 as 'Max_Weight',
CASE 
	When B.Vend_Acct_Num='*' then 0 
	else 100
end as 'Account_Num_Weight',
FB_Actual_WT,
AL_ORIG_1,
CASE 
	When B.Origin_Addr_1='*' then 0 
	else 10
end as 'Origin_1_Weight',
AL_ORIG_2,
CASE 
	When B.Origin_Addr_2='*' then 0 
	else 10
end as 'Origin_2_Weight',
AL_CITY_ORIG,
CASE 
	When B.Origin_City='*' then 0 
	else 100
end as 'Origin_City_Weight',
AL_CNTRY_CODE_ORIG,
CASE 
	When B.Origin_Cntry_Code='*' then 0 
	else 100
end as 'Origin_Country_Weight',
AL_Dest_1,
CASE 
	When B.Dest_Addr_1='*' then 0 
	else 10
end as 'Dest_1_Weight',
AL_Dest_2,
CASE 
	When B.Dest_Addr_2='*' then 0 
	else 10
end as 'Dest_2_Weight',
AL_CITY_Dest,
CASE 
	When B.Dest_City='*' then 0 
	else 100
end as 'Dest_City_Weight',
AL_CNTRY_CODE_Dest,
CASE 
	When B.Dest_Cntry_Code='*' then 0 
	else 100
end as 'Dest_Country_Weight',
B.Norm_Mode,
B.Norm_Mode_Type,
[RecordDtmUtc] ,
B.[User]
From @TempTable1 A
inner join [CentralConfigRepl].[dataCfg].[ModeByData] (nolock) B 
on @EnvironmentID = B.EnvId 
and A.VEND_LABL = B.Vend_Labl
and (A.AL_ORIG_1 = B.Origin_Addr_1 or B.Origin_Addr_1 = '*')
and (A.AL_ORIG_2 = B.Origin_Addr_2 or B.Origin_Addr_2 = '*')
and (A.AL_CITY_ORIG = B.Origin_City or B.Origin_City = '*')
and (A.AL_CNTRY_CODE_ORIG = B.Origin_Cntry_Code or B.Origin_Cntry_Code = '*')
and (A.AL_Dest_1 = B.Dest_Addr_1 or B.Dest_Addr_1 = '*')
and (A.AL_Dest_2 = B.Dest_Addr_2 or B.Dest_Addr_2 = '*')
and (A.AL_CITY_DEST = B.Dest_City or B.Dest_City = '*')
and ( A.Vend_Srvc_Name like B.Vend_Srvc_Name or B.Vend_Srvc_Name = '*')
and (A.AL_CNTRY_CODE_Dest = B.Dest_Cntry_Code or B.Dest_Cntry_Code = '*')

--select * from @TempTable2
---------------------------------------------------------
-- Weight Evaluation
---------------------------------------------------------

insert into @TempTable3
Select

A.FB_ID,
A.Vend_LAbl,
ACCT_NUM_VEND_BLNG,
A.Vend_Srvc_Name,
Vend_srvc_Name_Weight,
CASE 
	When B.Min_Weight is null then 0 
	else 10
end as 'Min_Weight',
CASE 
	When B.Max_Weight is null then 0 
	else 10
end as 'Max_Weight',
Account_Num_Weight,
FB_Actual_WT,
AL_ORIG_1,
Origin_1_Weight,
AL_ORIG_2,
Origin_2_Weight,
AL_CITY_ORIG,
Origin_City_weight,
AL_CNTRY_CODE_ORIG,
Origin_country_weight,
AL_Dest_1,
Dest_1_weight,
AL_Dest_2,
Dest_2_weight,
AL_CITY_Dest,
Dest_city_weight,
AL_CNTRY_CODE_Dest,
Dest_Country_weight,
B.Norm_Mode,
B.Norm_Mode_Type,
A.[RecordDtmUtc] ,
B.[User]
From @TempTable2 A
inner join [CentralConfigRepl].[dataCfg].[ModeByData] (nolock) B 
on @EnvironmentID = B.envid
and B.Vend_Labl = A.Vend_Labl
and A.RecordDtmUtc = B.RecordDtmUtc
where 
	(
		(A.FB_ACTUAL_WT >= B.Min_Weight and B.Min_weight is not null
		and 
		A.FB_ACTUAL_WT <= B.Max_Weight and B.Max_weight is not null)
	or				
		(
			(A.FB_ACTUAL_WT >= B.Min_Weight and B.Min_weight is not null
			and 
			B.Max_Weight is null)
		or 
			(A.FB_ACTUAL_WT <= B.Max_Weight and B.Max_weight is not null
			and 
			B.Min_Weight is null)
		or 
			(B.Min_Weight is null
			and 
			B.Max_Weight is null)
		)
	)

--	select * from @Temptable3
	
	declare @HighValue bigint

	set @HighValue = (Select MAX(Vend_Srvc_Name_Weight+Origin_1_Weight + Origin_2_Weight+ Origin_City_Weight + Origin_Country_Weight + Dest_1_Weight + Dest_2_Weight + Dest_City_Weight + Dest_Country_Weight+ Account_Num_Weight + Min_Weight + Max_Weight) 
	From @TempTable3)

	insert into @TempTable4
	Select 

	FB_ID,
	VEND_LABL,
	ACCT_NUM_VEND_BLNG,
	Vend_Srvc_Name,
	FB_ACTUAL_WT,
	AL_ORIG_1,
	AL_ORIG_2,
	AL_CITY_ORIG,
	AL_CNTRY_CODE_ORIG,
	AL_Dest_1,
	AL_Dest_2,
	AL_CITY_Dest,
	AL_CNTRY_CODE_Dest,
	Vend_Srvc_Name_Weight+Origin_1_Weight + Origin_2_Weight+ Origin_City_Weight + Origin_Country_Weight + Dest_1_Weight + Dest_2_Weight + Dest_City_Weight + Dest_Country_Weight+ Account_Num_Weight + Min_Weight + Max_Weight as 'Total_Score',
	Norm_Mode,
	Norm_Mode_Type,
	[RecordDtmUtc],
	[User]
	From @TempTable3
	
	If (Select Count(FB_ID) From Dbo.ModeByDataMetrics where FB_ID = @FB_ID) < 1
	Begin
	
Insert into 	Dbo.ModeByDataMetrics
	Select top 1
	@FB_ID as FB_ID,
	 Norm_Mode ,
	 Norm_Mode_Type,
	 GETDATE(),
	 'ID-'+CAST( [RecordDtmUtc] as varchar)+' user-'+[User]+' weight-'+CAST(Total_Score as varchar) as [Authority]
	From @TempTable4
	where @HighValue = [Total_Score]
	Order by Total_Score Desc,[RecordDtmUtc] desc
	
	end
	
	If (Select Count(FB_ID) From Dbo.ModeByDataMetrics where FB_ID = @FB_ID) >= 1
	Begin
	Update A
	Set A.Norm_Mode = B.Norm_Mode,
	A.Norm_Mode_Type = B.Norm_Mode_Type,
	A.[Authority] = 'ID-'+CAST([RecordDtmUtc] as varchar)+' user-'+[User]+' weight-'+CAST(Total_Score as varchar),
	A.RecordDTM = GETDATE()
	From Dbo.ModeByDataMetrics A
	inner join @TempTable4 B on A.FB_ID = @FB_ID
	where @HighValue = [Total_Score]
	
	end
	
	Update A
	set CUST_MODE = B.Norm_Mode,
	CUST_MODE_TYPE = B.Norm_Mode_Type
	From Dnorm.FbNorm A
	inner join @TempTable4 B on A.FB_ID = @FB_ID

	Select * from @TempTable4

End	



GO