quote:

---

Originally posted by Peso

Probably none. I think the procedure will perform worse.
The reason is that table variables doesn't have statistics, so the query engine assumes there is one and one record only in the table.
Even if there are thousands.

---

N 56°04'39.26"
E 12°55'05.63"

---

insert into #prev_vehiclestot
select coverage,
rate_zip,
previous_vehicles = case when len( previous_vehicles ) = 1 then '0000000' + previous_vehicles
when len( previous_vehicles ) = 2 then '000000' + previous_vehicles
when len( previous_vehicles ) = 3 then '00000' + previous_vehicles
when len( previous_vehicles ) = 4 then '0000' + previous_vehicles
when len( previous_vehicles ) = 5 then '000' + previous_vehicles
when len( previous_vehicles ) = 6 then '00' + previous_vehicles
when len( previous_vehicles ) = 7 then '0' + previous_vehicles
when previous_vehicles is null then '00000000' 
end
from #prev_vehicles2

update #can_vehiclestot
set rate_zip = '00000'
where rate_zip is null or rate_zip = ' ' or rate_zip = ' '

insert	#prev_vehiclestot
select	coverage,
	COALESCE(NULLIF(rate_zip, ''), '00000') AS rate_zip,
	REPLACE(STR(COALESCE(previous_vehicles, 0), 8), ' ', '0') AS previous_vehicles
from	#prev_vehicles2

update #can_vehiclestot
set rate_zip = '00000'
where rate_zip is null or rate_zip = ' ' or rate_zip = ' '

insert into #zips
select distinct coverage, rate_zip from #can_vehiclestot

update #zips
set rate_zip = '00000'
where rate_zip is null or rate_zip = ' ' or rate_zip = ' '

rate_zip = case when e.Driver_Class in ('300','301','302','303','304','400','401','402','403','404') then '99200'
when e.Driver_Class in ('100','101','102') then '99250'
when e.Driver_Class in ('114','115','116','214','215','216','314','315','316','414','415','416') then '99600'
when d.Business_Use = 'Y' and e.Driver_Class not in ('100','101','102','300','301','302','303','304','400','401','402','403','404') then '99300'
else '99100'
end,
insurer_vehicles = case when e.cancel_code in ('CR','UD','UI','UL','UV','UW','RW') then count( e.vehicle_number ) end,
nonpay_vehicles = case when e.cancel_code in ('AD','AP','AR','FN','NA','ND','NF','NI','NP','NS') then count( e.vehicle_number ) end,
insured_vehicles = case when e.cancel_code in ('II','IR') then count( e.vehicle_number ) end 

FROM		History AS a
LEFT JOIN	Vehicle AS b ON b.policy_base = a.policy_base
			AND b.policy_suffix = a.policy_suffix
			AND b.vehicle_number = a.vehicle_number
			AND (b.current_flag = 'Y' or b.endorse_status = 'D')
LEFT JOIN	driver AS c ON c.policy_base = a.policy_base
		AND c.policy_suffix = a.policy_suffix
		AND c.driver_number = a.driver_number
		AND (c.current_flag = 'Y' or c.endorse_status = 'D')
WHERE		a.policy_state = 'TX'
		AND a.rate_zip IS NOT NULL
		AND a.rate_table >= @start_rate_table
		AND a.rate_table <= @end_rate_table

ALTER PROCEDURE dbo.Texas_Quarterly_Market_Report
(
	@current_qtr TINYINT,
	@current_year int,
	@start_rate_table int,
	@end_rate_table int
)
AS

SET NOCOUNT ON

-- Check for invalid user supplied parameter values
IF @current_qtr IS NULL OR @current_qtr NOT BETWEEN 1 AND 4 
	BEGIN
		RAISERROR('Invalid value (%d) for current quarter selected.', 16, 1, @current_qtr)
		RETURN -100
	END

IF @current_year IS NULL OR @current_year NOT BETWEEN 2000 AND 2099
	BEGIN
		RAISERROR('Invalid value (%d) for current year selected.', 16, 1, @current_year)
		RETURN -110
	END

-- Declare work variables
DECLARE	@start_date DATETIME,
	@end_date DATETIME,
	@end_date_prev DATETIME,
	@current_month CHAR(2),
	@accounting_date CHAR(4),
	@begin_period CHAR(6),
	@end_period CHAR(6),
	@filename VARCHAR(12)

-- Set dates depending on quarter entered
SELECT	@start_date = DATEADD(MONTH, 12 * @current_year + 3 * @current_qtr - 22803, 0),
	@end_date = DATEADD(MONTH, 12 * @current_year + 3 * @current_qtr - 22800, -1),
	@end_date_prev = DATEADD(MONTH, 12 * @current_year + 3 * @current_qtr - 22803, -1),
	@current_month = REPLACE(STR(3 * @current_qtr, 2), ' ', '0'),
	@accounting_date = CONVERT(CHAR(4), @end_date, 12),
	@begin_period = CONVERT(CHAR(6), @start_date, 112),
	@end_period = CONVERT(CHAR(6), @end_date, 112)

-- Stage data to work with later
SELECT		CASE a.coverage
			WHEN 'BI' THEN 1
			when 'CL' THEN 3
		END AS coverage,
		a.driver_class,
		CASE
			WHEN a.Driver_Class LIKE '[34]0[01234]' THEN '99200'
			WHEN a.Driver_Class IN ('100', '101', '102') THEN '99250'
			WHEN a.Driver_Class LIKE '[1234]1[456]' THEN '99600'
			WHEN c.Business_Use = 'Y' AND a.Driver_Class NOT IN ('100', '101', '102', '300', '301', '302', '303', '304', '400', '401', '402', '403', '404') THEN '99300'
			ELSE '99100'
		END AS rate_zip,
		CASE
			WHEN a.cancel_code IN ('CR', 'UD', 'UI', 'UL', 'UV', 'UW', 'RW') THEN 1
			ELSE 0
		END AS insurer_vehicles,
		CASE
			WHEN a.cancel_code IN ('AD', 'AP', 'AR', 'FN', 'NA', 'ND', 'NF', 'NI', 'NP', 'NS') THEN 1
			ELSE 0
		END AS nonpay_vehicles,
		CASE
			WHEN a.cancel_code IN ('II', 'IR') THEN 1
			WHEN a.cancel_code IS NULL THEN 1
			WHEN a.cancel_code LIKE '% %' THEN 1
			ELSE 0
		END AS insured_vehicles,
		CASE 
			WHEN a.coverage IN ('BI', 'CL') THEN written_premium
			ELSE 0
		END AS Written,
		CASE 
			WHEN a.coverage IN ('FE') THEN written_premium
			ELSE 0
		END AS Fees,
		CASE
			WHEN a.accounting_period >= @begin_period AND a.accounting_period <= @end_period THEN 1
			ELSE 0
		END AS [Current]		
INTO		#Texas
FROM		History AS a
LEFT JOIN	Vehicle AS b ON b.policy_base = a.policy_base
			AND b.policy_suffix = a.policy_suffix
			AND b.vehicle_number = a.vehicle_number
			AND (b.current_flag = 'Y' or b.endorse_status = 'D')
LEFT JOIN	driver AS c ON c.policy_base = a.policy_base
		AND c.policy_suffix = a.policy_suffix
		AND c.driver_number = a.driver_number
		AND (c.current_flag = 'Y' or c.endorse_status = 'D')
WHERE		a.policy_state = 'TX'
		AND a.rate_zip IS NOT NULL
		AND a.rate_table >= @start_rate_table
		AND a.rate_table <= @end_rate_table

/*
	Now you have a staging table with all data you need.
	Use SUM() OVER (PARTITION BY ...) to accumulate the data you need.
*/