PredictionSP

predictionsp.SQL

CREATE PROCEDURE [dbo].[usp_PredictedCustomers]
as
 
DECLARE @model varbinary(max) 
set @model = (SELECT top 1 model from dbo.predictivemodeldetail);

declare @PredictiveModelDetail int
-- Find the latest model of the type we're using for this prediction
set @PredictiveModelDetail = (
				select top 1 p.[PredictiveModelDetailId] 
					from [dbo].[PredictiveModelDetail] p join [dbo].[PredictiveModel] m on m.PredictiveModelId = p.PredictiveModelId
			    where m.ModelName = 'CUSTTREE'
				order by TrainingDate DESC) ;

with d
as
(
select C.[CustomerKey]
      ,C.[CustomerAlternateKey]
	  ,C.[FirstName]
      ,C.[MiddleName]
      ,C.[LastName]
	  ,C.[Title]
      ,AgeRanges = case 
					when datediff(year,C.[BirthDate],getdate()) <= 30 then '11-30'
					when datediff(year,C.[BirthDate],getdate()) between 31 and 50 then '31-50'
					when datediff(year,C.[BirthDate],getdate()) between 41 and 70 then '51-70'
					when datediff(year,C.[BirthDate],getdate()) > 70 then '71+'
				  end 
      ,[IncomeCategories] = case
								when C.YearlyIncome <= 20000 then 'Low'
								when C.YearlyIncome between 20001 and 50000 then 'Lower'
								when C.YearlyIncome between 50001 and 70000 then 'Middle'
								when C.YearlyIncome between 70001 and 100000 then 'Upper'
								when C.YearlyIncome > 100000 then 'Wealthy'
							end
      ,C.[MaritalStatus]
      ,C.[Gender]
      ,C.[TotalChildren]
      ,C.[NumberChildrenAtHome]
      ,[Education] = C.[EnglishEducation]
      ,[Occupation] = C.[EnglishOccupation]
      ,[IsHomeOwner] = convert(bit,C.[HouseOwnerFlag])
      ,C.[NumberCarsOwned]
      ,[YearsSinceFirstPurchase]  = Datediff(YEAR,C.DateFirstPurchase,getdate()) 
      ,C.[CommuteDistance]
      ,[CountryRegionCode] = ISNULL(G.[CountryRegionCode],'US')
	  ,[PredictiveModelDetailId] = @PredictiveModelDetail
from [dbo].[DimCustomer] C 
	left join [dbo].[DimGeography] G on c.GeographyKey = g.GeographyKey
	
)
SELECT CustomerKey,FALSE_Pred,TRUE_Pred, IsCardUser = convert(bit,iif(TRUE_pred>=.5,1,0)),PredictiveModelDetailId
FROM 
	PREDICT(
		 MODEL = @model
		,DATA = d
		)  WITH (FALSE_Pred float, TRUE_Pred float) as P
order by [CustomerKey]