CREATE TABLE #month0to6 (vin nvarchar(20))
DECLARE @startDate as nvarchar(10), SCF as nvarchar
SET @startDate = '10/31/2007'
--Insert 0 to 6 month data
INSERT INTO #month0to6 (vin)
SELECT DISTINCT tblVehicles.VEH_vin FROM tblROTotal
--INNER JOIN tblApplications ON tblROTotal.ROH_DLR_APP_ID = tblApplications.APP_ID
INNER JOIN tblVehicles ON tblROTotal.ROH_VEH_ID = tblVehicles.VEH_ID
INNER JOIN tblMakeModel ON tblVehicles.VEH_MM_ID = tblMakeModel.MM_ID
INNER JOIN tblCustomer ON tblCustomer.Cust_ID = tblROTotal.ROH_Cust_ID
INNER JOIN tblLongPayTypes ON tblLongPayTypes.LPT_ID = tblROTotal.ROD_LPT_ID
WHERE (
tblROTotal.ROH_DLR_APP_ID IN (
SELECT DealerAPP_ID FROM tblEmailLoginDealerAccess WHERE UserName = @username
)
AND (ROH_CLOSE_DATE <= DATEADD(d, -0, CONVERT(varchar, @startDate , 101)))
AND (ROH_CLOSE_DATE >= DATEADD(d, -180, CONVERT(varchar, @startDate , 101)))
--paytypes
AND (LEFT(LPT_Desc,1) = 'C' OR LEFT(LPT_Desc,1) = 'W')
--Last x years from user info
AND veh_year>=@startYear
--Vehicle Makes from user info
AND MM_Outside_Make IN (
SELECT make FROM tblWizardUserPrimaryMakes WHERE username = @username
)
--AND (MM_Outside_Make = 'Subaru' OR MM_Outside_Make = 'FORD' OR MM_Outside_Make = 'KIA')
AND LEFT(Cust_Zip, 3) IN (
SELECT SCF FROM tblWizardUserSCFs WHERE username = @username
)
--AND (LEFT(Cust_Zip,3) = '105' OR LEFT(Cust_Zip,3) = '125' OR LEFT(Cust_Zip,3) = '109')
)