Temporary Tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Temporary Tables

Author

Topic

JohnGault777
Starting Member

31 Posts

Posted - 2011-10-17 : 16:17:21

I am just starting to work with temporary tables. I need a list of items to join a few other queries to. I can't use one of the queries because the identifying category exists does not exist in all of them at the same time, so some will be left out. Anyway,

Create Table #PriorCompany (Name nvarchar(50))

Go

Insert Into #PriorCompany (Name)
Values ('Name1'), ('Name2'),..., ('Namen')

Go

Query using #PriorCompany

Drop Table #PriorCompany

I obviously abbreviated the query. Now I have two problems.

1) If it run it once (it never stops. I have to cancel it.) and then a second time, I get the following error.

Msg 2714, Level 16, State 6, Line 1
There is already an object named '#PriorCompany' in the database.

2) The query never stops running. The two queries I just complete quickly and are pretty basic. Then I try to join the two basic queries to the temporary table and it ran for 20 minutes (not on a production server) until I canceled it. The database isn't even that large (at most 40,000 to 50,000 rows, so I'm not working with 500K+ or something).

Can someone help me here?

Here's the Code:

 Create Table #PriorCompany (Name nvarchar(50))

Go 

Insert Into #PriorCompany (Name)
			Values ('Not Given'),
					('21st Century'),
					('AAA'),
					('Acceptance'),
					('ACE'),
					('Agency Insurance Company'),
					('AIG'),
					('Allied'),
					('Allstate'),
					('Allstate Floridian Indemnity Company'),
					('Alpha'),
					('American Home'),
					('American Integrity'),
					('American Reliable'),
					('American Security'),
					('American Strategic'),
					('Amerisure Mutual'),
					('Amica Mutual'),
					('Argus'),
					('ASI'),
					('Assurance Company Of America'),
					('Auto Owners'),
					('Auto Owners'),
					('Bristol West'),
					('California Casualty'),
					('Cincinnati'),
					('Citizens'),
					('Continental'),
					('Coral'),
					('Cotton States'),
					('Dairyland'),
					('Direct General'),
					('Edison'),
					('Encompass'),
					('Encompass Floridian'),
					('Esurance'),
					('Farm Bureau'),
					('Fidelity National'),
					('First Floridian'),
					('First Home'),
					('Florida Farm Bureau'),
					('Florida Select'),
					('Frontline'),
					('Gainsco'),
					('GEICO'),
					('GMAC'),
					('Guideone Elite'),
					('Hanover'),
					('Hartford'),
					('Hillcrest'),
					('Horace Mann'),
					('Infinity'),
					('Integon'),
					('Liberty'),
					('Liberty Mutual'),
					('Links Premier'),
					('Magnolia'),
					('Mendota'),
					('Mercury'),
					('Met Life'),
					('National'),
					('Nationwide'),
					('Ocean Harbor'),
					('Old Dominion'),
					('Olympus'),
					('Other'),
					('Pacific'),
					('People''s Trust'),
					('Progressive'),
					('Prudential'),
					('Response'),
					('Safe Auto'),
					('Safeco'),
					('Safety Insurance'),
					('Security First'),
					('Security National'),
					('Seguros Multiples'),
					('Seminole Casualty'),
					('Sentinel Insurance'),
					('Sentry'),
					('Southern Fidelity'),
					('Southern-Owners'),
					('St. Johns'),
					('St. Paul'),
					('Star & Shield'),
					('Star & Shield Insurance Exchange'),
					('State Farm'),
					('Sunshine State'),
					('Teachers Insurance'),
					('Titan'),
					('Tower Hill'),
					('Travelers'),
					('United'),
					('United Services'),
					('Unitrin Direct'),
					('US Security'),
					('USAA'),
					('Workers'),
					('Zenith')

			
Go

Declare @startdate datetime;
Declare @enddate datetime;
Set @startdate = '2011-10-01';
Set @enddate = '2011-10-15';  


With UserNames1 (PolicyID, UsersID, Rep_Quote) as 

( select p.policyid, p.usersid, u.fname + ' ' + u.lname 
	from policy p inner join users u
	on p.usersid = u.usersid ), -- Section of the CTE to generate the Rep who quoted the policy

UserNames2 (PolicyID, UsersID, Rep_App) as 

( select p.policyid, p.applicationusersid, u.fname + ' ' + u.lname 
	from policy p inner join users u
	on p.applicationusersid = u.usersid ), -- Section of the CTE to generate the Rep who did the Application for the policy

UserNames3 (PolicyID, UsersID, Rep_Submission) as 

( select p.policyid, p.submissionusersid, u.fname + ' ' + u.lname 
	from policy p inner join users u
	on p.submissionusersid = u.usersid ), -- Section of the CTE to generate the Rep who did the Submission for the policy
	
UserNames4 (PolicyID, UsersID, Rep_Submit) as 

( select p.policyid, p.submitusersid, u.fname + ' ' + u.lname 
	from policy p inner join users u
	on p.submitusersid = u.usersid ) -- Section of the CTE to generate the Rep who did the Submit of the policy


Select a.name, count(distinct b.insuredID) as Quotes, count(distinct c.PolicyNum) as Sales

From (Select * From #PriorCompany) as a inner join 


(SELECT     dbo.Member.memberID, dbo.Insured.insuredID, dbo.Policy.policyNum, dbo.insured.county, 
			
			Case
			When dbo.auto.bieachperson = 10000 and dbo.auto.bieachaccident = 20000 then '10/20'
			When dbo.auto.bieachperson = 25000 and dbo.auto.bieachaccident = 50000 then '25/50'
			When dbo.auto.bieachperson = 50000 and dbo.auto.bieachaccident = 100000 then '50/100'
			When dbo.auto.bieachperson = 100000 and dbo.auto.bieachaccident = 300000 then '100/300'
			When dbo.auto.bieachperson = 250000 and dbo.auto.bieachaccident = 500000 then '250/500'
			Else 'Other'
			End as BILimits,
			Case
			When dbo.auto.priorcarrier = '21st CENTURY' Then '21st Century'
			When dbo.auto.priorcarrier = 'AAA' Then 'AAA'
			When dbo.auto.priorcarrier = 'ACCEPTANCE' Then 'Acceptance'
			When dbo.auto.priorcarrier = 'ACE ' Then 'ACE'
			When dbo.auto.priorcarrier = 'AGENCY INSURANCE COMPANY' Then 'Agency Insurance Company'
			When dbo.auto.priorcarrier = 'AIG' Then 'AIG'
			When dbo.auto.priorcarrier = 'ALLIED' Then 'Allied'
			When dbo.auto.priorcarrier = 'ALLSTATE' Then 'Allstate'
			When dbo.auto.priorcarrier = 'ALLSTATE FLORIDIAN INDEMNITY COMPANY' Then 'Allstate Floridian Indemnity Company'
			When dbo.auto.priorcarrier = 'ALPHA' Then 'Alpha'
			When dbo.auto.priorcarrier = 'AMERICAN HOME' Then 'American Home'
			When dbo.auto.priorcarrier = 'AMERICAN INTEGRITY' Then 'American Integrity'
			When dbo.auto.priorcarrier = 'AMERICAN RELIABLE' Then 'American Reliable'
			When dbo.auto.priorcarrier = 'AMERICAN SECURITY' Then 'American Security'
			When dbo.auto.priorcarrier = 'AMERICAN STRATEGIC ' Then 'American Strategic'
			When dbo.auto.priorcarrier = 'AMERISURE MUTUAL ' Then 'Amerisure Mutual'
			When dbo.auto.priorcarrier = 'AMICA MUTUAL ' Then 'Amica Mutual'
			When dbo.auto.priorcarrier = 'ARGUS ' Then 'Argus'
			When dbo.auto.priorcarrier = 'ASI ' Then 'ASI'
			When dbo.auto.priorcarrier = 'ASSURANCE COMPANY OF AMERICA' Then 'Assurance Company Of America'
			When dbo.auto.priorcarrier = 'AUTO OWNERS' Then 'Auto Owners'
			When dbo.auto.priorcarrier = 'AUTO-OWNERS ' Then 'Auto Owners'
			When dbo.auto.priorcarrier = 'BRISTOL WEST' Then 'Bristol West'
			When dbo.auto.priorcarrier = 'CALIFORNIA CASUALTY' Then 'California Casualty'
			When dbo.auto.priorcarrier = 'CINCINNATI ' Then 'Cincinnati'
			When dbo.auto.priorcarrier = 'CITIZENS' Then 'Citizens'
			When dbo.auto.priorcarrier = 'CONTINENTAL ' Then 'Continental'
			When dbo.auto.priorcarrier = 'CORAL ' Then 'Coral'
			When dbo.auto.priorcarrier = 'COTTON STATES ' Then 'Cotton States'
			When dbo.auto.priorcarrier = 'DAIRYLAND' Then 'Dairyland'
			When dbo.auto.priorcarrier = 'DIRECT GENERAL' Then 'Direct General'
			When dbo.auto.priorcarrier = 'EDISON ' Then 'Edison'
			When dbo.auto.priorcarrier = 'ENCOMPASS ' Then 'Encompass'
			When dbo.auto.priorcarrier = 'ENCOMPASS FLORIDIAN' Then 'Encompass Floridian'
			When dbo.auto.priorcarrier = 'ESURANCE ' Then 'Esurance'
			When dbo.auto.priorcarrier = 'FARM BUREAU' Then 'Farm Bureau'
			When dbo.auto.priorcarrier = 'FIDELITY NATIONAL' Then 'Fidelity National'
			When dbo.auto.priorcarrier = 'FIRST FLORIDIAN' Then 'First Floridian'
			When dbo.auto.priorcarrier = 'FIRST HOME ' Then 'First Home'
			When dbo.auto.priorcarrier = 'FLORIDA FARM BUREAU ' Then 'Florida Farm Bureau'
			When dbo.auto.priorcarrier = 'FLORIDA SELECT' Then 'Florida Select'
			When dbo.auto.priorcarrier = 'FRONTLINE' Then 'Frontline'
			When dbo.auto.priorcarrier = 'GAINSCO' Then 'Gainsco'
			When dbo.auto.priorcarrier = 'GEICO ' Then 'GEICO'
			When dbo.auto.priorcarrier = 'GMAC' Then 'GMAC'
			When dbo.auto.priorcarrier = 'GUIDEONE ELITE ' Then 'Guideone Elite'
			When dbo.auto.priorcarrier = 'HANOVER ' Then 'Hanover'
			When dbo.auto.priorcarrier = 'HARTFORD ' Then 'Hartford'
			When dbo.auto.priorcarrier = 'HILLCREST' Then 'Hillcrest'
			When dbo.auto.priorcarrier = 'HORACE MANN' Then 'Horace Mann'
			When dbo.auto.priorcarrier = 'INFINITY' Then 'Infinity'
			When dbo.auto.priorcarrier = 'INTEGON' Then 'Integon'
			When dbo.auto.priorcarrier = 'LIBERTY ' Then 'Liberty'
			When dbo.auto.priorcarrier = 'LIBERTY MUTUAL ' Then 'Liberty Mutual'
			When dbo.auto.priorcarrier = 'LINKS PREMIER' Then 'Links Premier'
			When dbo.auto.priorcarrier = 'MAGNOLIA ' Then 'Magnolia'
			When dbo.auto.priorcarrier = 'MENDOTA' Then 'Mendota'
			When dbo.auto.priorcarrier = 'MERCURY ' Then 'Mercury'
			When dbo.auto.priorcarrier = 'MET LIFE' Then 'Met Life'
			When dbo.auto.priorcarrier = 'NATIONAL' Then 'National'
			When dbo.auto.priorcarrier = 'NATIONWIDE' Then 'Nationwide'
			When dbo.auto.priorcarrier = 'OCEAN HARBOR' Then 'Ocean Harbor'
			When dbo.auto.priorcarrier = 'OLD DOMINION' Then 'Old Dominion'
			When dbo.auto.priorcarrier = 'OLYMPUS ' Then 'Olympus'
			When dbo.auto.priorcarrier = 'OTHER' Then 'Other'
			When dbo.auto.priorcarrier = 'PACIFIC' Then 'Pacific'
			When dbo.auto.priorcarrier like '%People%' and dbo.Auto.priorCarrier like '%Trust%' Then 'Peoples Trust'
			When dbo.auto.priorcarrier = 'PROGRESSIVE' Then 'Progressive'
			When dbo.auto.priorcarrier = 'PRUDENTIAL' Then 'Prudential'
			When dbo.auto.priorcarrier = 'RESPONSE ' Then 'Response'
			When dbo.auto.priorcarrier = 'SAFE AUTO' Then 'Safe Auto'
			When dbo.auto.priorcarrier = 'SAFECO ' Then 'Safeco'
			When dbo.auto.priorcarrier = 'SAFETY INSURANCE' Then 'Safety Insurance'
			When dbo.auto.priorcarrier = 'SECURITY FIRST ' Then 'Security First'
			When dbo.auto.priorcarrier = 'SECURITY NATIONAL' Then 'Security National'
			When dbo.auto.priorcarrier = 'SEGUROS MULTIPLES' Then 'Seguros Multiples'
			When dbo.auto.priorcarrier = 'SEMINOLE CASUALTY' Then 'Seminole Casualty'
			When dbo.auto.priorcarrier = 'SENTINEL INSURANCE' Then 'Sentinel Insurance'
			When dbo.auto.priorcarrier = 'SENTRY' Then 'Sentry'
			When dbo.auto.priorcarrier = 'SOUTHERN FIDELITY ' Then 'Southern Fidelity'
			When dbo.auto.priorcarrier = 'SOUTHERN-OWNERS' Then 'Southern-Owners'
			When dbo.auto.priorcarrier = 'ST. JOHNS ' Then 'St. Johns'
			When dbo.auto.priorcarrier = 'ST. PAUL ' Then 'St. Paul'
			When dbo.auto.priorcarrier = 'Star & Shield' Then 'Star & Shield'
			When dbo.auto.priorcarrier = 'Star & Shield Insurance Exchange' Then 'Star & Shield Insurance Exchange'
			When dbo.auto.priorcarrier = 'STATE FARM' Then 'State Farm'
			When dbo.auto.priorcarrier = 'SUNSHINE STATE' Then 'Sunshine State'
			When dbo.auto.priorcarrier = 'TEACHERS INSURANCE' Then 'Teachers Insurance'
			When dbo.auto.priorcarrier = 'TITAN' Then 'Titan'
			When dbo.auto.priorcarrier = 'TOWER HILL' Then 'Tower Hill'
			When dbo.auto.priorcarrier = 'TRAVELERS' Then 'Travelers'
			When dbo.auto.priorcarrier = 'UNITED ' Then 'United'
			When dbo.auto.priorcarrier = 'UNITED SERVICES' Then 'United Services'
			When dbo.auto.priorcarrier = 'UNITRIN DIRECT ' Then 'Unitrin Direct'
			When dbo.auto.priorcarrier = 'US SECURITY' Then 'US Security'
			When dbo.auto.priorcarrier = 'USAA' Then 'USAA'
			When dbo.auto.priorcarrier = 'WORKERS' Then 'Workers'
			When dbo.auto.priorcarrier = 'ZENITH' Then 'Zenith'
			Else 'Not Given'
			End as 'PriorCarrier'
	
			
FROM         dbo.Member right JOIN
                      dbo.Insured ON dbo.Member.insuredID = dbo.Insured.insuredID inner JOIN
                      dbo.Policy ON dbo.Insured.insuredID = dbo.Policy.insuredID inner join
                      dbo.Auto on dbo.Policy.policyID = dbo.Auto.policyID inner join 
						UserNames1 v1 ON dbo.policy.policyid = v1.policyid
WHERE     dbo.policy.applicationusersid <> '1' and dbo.Policy.addDate > @startdate  and dbo.Policy.addDate < @enddate 
			and dbo.Policy.insuredID not in(SELECT    dbo.Insured.insuredID
			
										FROM         dbo.Member right JOIN
												  dbo.Insured ON dbo.Member.insuredID = dbo.Insured.insuredID inner JOIN
												  dbo.Policy ON dbo.Insured.insuredID = dbo.Policy.insuredID inner join
												  dbo.Auto on dbo.Policy.policyID = dbo.Auto.policyID inner join 
													UserNames1 v1 ON dbo.policy.policyid = v1.policyid
										WHERE     dbo.policy.applicationusersid <> '1' and dbo.Policy.addDate > (@startdate - 180) and dbo.Policy.addDate < (@startdate))
            and dbo.Policy.insuredID  not in (select dbo.Policy.insuredID
										from dbo.Member inner join dbo.Insured on dbo.Member.insuredID = dbo.Insured.insuredID inner join
											 dbo.Policy on dbo.insured.Insuredid = dbo.Policy.insuredID
										Where dbo.Policy.boundDate < @startdate and dbo.Policy.boundDate is not null)) as b on a.name = b.priorcarrier inner join
										

(SELECT		dbo.Member.memberID, dbo.Insured.insuredID, dbo.Policy.policyNum, dbo.insured.county, 
			
			Case
			When dbo.auto.bieachperson = 10000 and dbo.auto.bieachaccident = 20000 then '10/20'
			When dbo.auto.bieachperson = 25000 and dbo.auto.bieachaccident = 50000 then '25/50'
			When dbo.auto.bieachperson = 50000 and dbo.auto.bieachaccident = 100000 then '50/100'
			When dbo.auto.bieachperson = 100000 and dbo.auto.bieachaccident = 300000 then '100/300'
			When dbo.auto.bieachperson = 250000 and dbo.auto.bieachaccident = 500000 then '250/500'
			Else 'Other'
			End as BILimits,
			Case
			When dbo.auto.priorcarrier = '21st CENTURY' Then '21st Century'
			When dbo.auto.priorcarrier = 'AAA' Then 'AAA'
			When dbo.auto.priorcarrier = 'ACCEPTANCE' Then 'Acceptance'
			When dbo.auto.priorcarrier = 'ACE ' Then 'ACE'
			When dbo.auto.priorcarrier = 'AGENCY INSURANCE COMPANY' Then 'Agency Insurance Company'
			When dbo.auto.priorcarrier = 'AIG' Then 'AIG'
			When dbo.auto.priorcarrier = 'ALLIED' Then 'Allied'
			When dbo.auto.priorcarrier = 'ALLSTATE' Then 'Allstate'
			When dbo.auto.priorcarrier = 'ALLSTATE FLORIDIAN INDEMNITY COMPANY' Then 'Allstate Floridian Indemnity Company'
			When dbo.auto.priorcarrier = 'ALPHA' Then 'Alpha'
			When dbo.auto.priorcarrier = 'AMERICAN HOME' Then 'American Home'
			When dbo.auto.priorcarrier = 'AMERICAN INTEGRITY' Then 'American Integrity'
			When dbo.auto.priorcarrier = 'AMERICAN RELIABLE' Then 'American Reliable'
			When dbo.auto.priorcarrier = 'AMERICAN SECURITY' Then 'American Security'
			When dbo.auto.priorcarrier = 'AMERICAN STRATEGIC ' Then 'American Strategic'
			When dbo.auto.priorcarrier = 'AMERISURE MUTUAL ' Then 'Amerisure Mutual'
			When dbo.auto.priorcarrier = 'AMICA MUTUAL ' Then 'Amica Mutual'
			When dbo.auto.priorcarrier = 'ARGUS ' Then 'Argus'
			When dbo.auto.priorcarrier = 'ASI ' Then 'ASI'
			When dbo.auto.priorcarrier = 'ASSURANCE COMPANY OF AMERICA' Then 'Assurance Company Of America'
			When dbo.auto.priorcarrier = 'AUTO OWNERS' Then 'Auto Owners'
			When dbo.auto.priorcarrier = 'AUTO-OWNERS ' Then 'Auto Owners'
			When dbo.auto.priorcarrier = 'BRISTOL WEST' Then 'Bristol West'
			When dbo.auto.priorcarrier = 'CALIFORNIA CASUALTY' Then 'California Casualty'
			When dbo.auto.priorcarrier = 'CINCINNATI ' Then 'Cincinnati'
			When dbo.auto.priorcarrier = 'CITIZENS' Then 'Citizens'
			When dbo.auto.priorcarrier = 'CONTINENTAL ' Then 'Continental'
			When dbo.auto.priorcarrier = 'CORAL ' Then 'Coral'
			When dbo.auto.priorcarrier = 'COTTON STATES ' Then 'Cotton States'
			When dbo.auto.priorcarrier = 'DAIRYLAND' Then 'Dairyland'
			When dbo.auto.priorcarrier = 'DIRECT GENERAL' Then 'Direct General'
			When dbo.auto.priorcarrier = 'EDISON ' Then 'Edison'
			When dbo.auto.priorcarrier = 'ENCOMPASS ' Then 'Encompass'
			When dbo.auto.priorcarrier = 'ENCOMPASS FLORIDIAN' Then 'Encompass Floridian'
			When dbo.auto.priorcarrier = 'ESURANCE ' Then 'Esurance'
			When dbo.auto.priorcarrier = 'FARM BUREAU' Then 'Farm Bureau'
			When dbo.auto.priorcarrier = 'FIDELITY NATIONAL' Then 'Fidelity National'
			When dbo.auto.priorcarrier = 'FIRST FLORIDIAN' Then 'First Floridian'
			When dbo.auto.priorcarrier = 'FIRST HOME ' Then 'First Home'
			When dbo.auto.priorcarrier = 'FLORIDA FARM BUREAU ' Then 'Florida Farm Bureau'
			When dbo.auto.priorcarrier = 'FLORIDA SELECT' Then 'Florida Select'
			When dbo.auto.priorcarrier = 'FRONTLINE' Then 'Frontline'
			When dbo.auto.priorcarrier = 'GAINSCO' Then 'Gainsco'
			When dbo.auto.priorcarrier = 'GEICO ' Then 'GEICO'
			When dbo.auto.priorcarrier = 'GMAC' Then 'GMAC'
			When dbo.auto.priorcarrier = 'GUIDEONE ELITE ' Then 'Guideone Elite'
			When dbo.auto.priorcarrier = 'HANOVER ' Then 'Hanover'
			When dbo.auto.priorcarrier = 'HARTFORD ' Then 'Hartford'
			When dbo.auto.priorcarrier = 'HILLCREST' Then 'Hillcrest'
			When dbo.auto.priorcarrier = 'HORACE MANN' Then 'Horace Mann'
			When dbo.auto.priorcarrier = 'INFINITY' Then 'Infinity'
			When dbo.auto.priorcarrier = 'INTEGON' Then 'Integon'
			When dbo.auto.priorcarrier = 'LIBERTY ' Then 'Liberty'
			When dbo.auto.priorcarrier = 'LIBERTY MUTUAL ' Then 'Liberty Mutual'
			When dbo.auto.priorcarrier = 'LINKS PREMIER' Then 'Links Premier'
			When dbo.auto.priorcarrier = 'MAGNOLIA ' Then 'Magnolia'
			When dbo.auto.priorcarrier = 'MENDOTA' Then 'Mendota'
			When dbo.auto.priorcarrier = 'MERCURY ' Then 'Mercury'
			When dbo.auto.priorcarrier = 'MET LIFE' Then 'Met Life'
			When dbo.auto.priorcarrier = 'NATIONAL' Then 'National'
			When dbo.auto.priorcarrier = 'NATIONWIDE' Then 'Nationwide'
			When dbo.auto.priorcarrier = 'OCEAN HARBOR' Then 'Ocean Harbor'
			When dbo.auto.priorcarrier = 'OLD DOMINION' Then 'Old Dominion'
			When dbo.auto.priorcarrier = 'OLYMPUS ' Then 'Olympus'
			When dbo.auto.priorcarrier = 'OTHER' Then 'Other'
			When dbo.auto.priorcarrier = 'PACIFIC' Then 'Pacific'
			When dbo.auto.priorcarrier like '%People%' and dbo.Auto.priorCarrier like '%Trust%' Then 'Peoples Trust'
			When dbo.auto.priorcarrier = 'PROGRESSIVE' Then 'Progressive'
			When dbo.auto.priorcarrier = 'PRUDENTIAL' Then 'Prudential'
			When dbo.auto.priorcarrier = 'RESPONSE ' Then 'Response'
			When dbo.auto.priorcarrier = 'SAFE AUTO' Then 'Safe Auto'
			When dbo.auto.priorcarrier = 'SAFECO ' Then 'Safeco'
			When dbo.auto.priorcarrier = 'SAFETY INSURANCE' Then 'Safety Insurance'
			When dbo.auto.priorcarrier = 'SECURITY FIRST ' Then 'Security First'
			When dbo.auto.priorcarrier = 'SECURITY NATIONAL' Then 'Security National'
			When dbo.auto.priorcarrier = 'SEGUROS MULTIPLES' Then 'Seguros Multiples'
			When dbo.auto.priorcarrier = 'SEMINOLE CASUALTY' Then 'Seminole Casualty'
			When dbo.auto.priorcarrier = 'SENTINEL INSURANCE' Then 'Sentinel Insurance'
			When dbo.auto.priorcarrier = 'SENTRY' Then 'Sentry'
			When dbo.auto.priorcarrier = 'SOUTHERN FIDELITY ' Then 'Southern Fidelity'
			When dbo.auto.priorcarrier = 'SOUTHERN-OWNERS' Then 'Southern-Owners'
			When dbo.auto.priorcarrier = 'ST. JOHNS ' Then 'St. Johns'
			When dbo.auto.priorcarrier = 'ST. PAUL ' Then 'St. Paul'
			When dbo.auto.priorcarrier = 'Star & Shield' Then 'Star & Shield'
			When dbo.auto.priorcarrier = 'Star & Shield Insurance Exchange' Then 'Star & Shield Insurance Exchange'
			When dbo.auto.priorcarrier = 'STATE FARM' Then 'State Farm'
			When dbo.auto.priorcarrier = 'SUNSHINE STATE' Then 'Sunshine State'
			When dbo.auto.priorcarrier = 'TEACHERS INSURANCE' Then 'Teachers Insurance'
			When dbo.auto.priorcarrier = 'TITAN' Then 'Titan'
			When dbo.auto.priorcarrier = 'TOWER HILL' Then 'Tower Hill'
			When dbo.auto.priorcarrier = 'TRAVELERS' Then 'Travelers'
			When dbo.auto.priorcarrier = 'UNITED ' Then 'United'
			When dbo.auto.priorcarrier = 'UNITED SERVICES' Then 'United Services'
			When dbo.auto.priorcarrier = 'UNITRIN DIRECT ' Then 'Unitrin Direct'
			When dbo.auto.priorcarrier = 'US SECURITY' Then 'US Security'
			When dbo.auto.priorcarrier = 'USAA' Then 'USAA'
			When dbo.auto.priorcarrier = 'WORKERS' Then 'Workers'
			When dbo.auto.priorcarrier = 'ZENITH' Then 'Zenith'
			Else 'Not Given'
			End as 'PriorCarrier'
			
			
			
			
FROM    dbo.Member INNER JOIN
                      dbo.Insured ON dbo.Member.insuredID = dbo.Insured.insuredID INNER JOIN
                      dbo.Policy ON dbo.Insured.insuredID = dbo.Policy.insuredID inner JOIN
                      dbo.Auto ON dbo.Policy.policyID = dbo.Auto.policyID inner join 
						UserNames1 v1 ON dbo.policy.policyid = v1.policyid inner join
						UserNames2 v2 ON dbo.policy.policyid = v2.policyid inner join
						UserNames3 v3 ON dbo.policy.policyid = v3.policyid inner join
						UserNames4 v4 ON dbo.policy.policyid = v4.policyid
WHERE     dbo.policy.Policytype = '1' and dbo.policy.policynum not like '%Q%' 
			and (dbo.Policy.boundDate > @startdate and dbo.Policy.boundDate < @enddate)
			and dbo.Policy.renewalCount = 0
) as c on a.name = c.PriorCarrier

group by a.name

drop table #PriorCompany

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-10-17 : 17:00:10

We'll need to see your actual code in order to help.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-10-17 : 17:04:40

1)
You are likely stopping the query prior to your "drop table" statement so when you re-run you get the "table already exists" error.
You can use this code for your create statement if you want to avoid that:


if object_id('tempdb.dbo.#PriorCompany') > 0
       drop table #PriorCompany
create table #priorCompany...

2)
What Tara said

Be One with the Optimizer
TG

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-10-17 : 18:16:39

Your statement seems to be unnecessarily complex. For instance those four CTEs with UserNames don't seem to be used. You JOIN to them but you don't use anything from those JOINed CTEs.

Most of those sub-querys don't seem necessary either when a direct join to the table(s) would do. for instance:
From (Select * From #PriorCompany) as a inner join
can just be
From #PriorCompany as a

All those large, repeated, similar statements are a flag to me that it could be greatly simplified. Especially that you seem to need is a distinct count of Sales and Quotes.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources