Making Map with Color Ramp Using SQL Database Intergration?

Question

This could be a trivial question, but I've been kind of stuck on it for awhile. I'm trying to construct multiple maps that reflect aggregated data showing overall risk by county in a SQL Database (using Microsoft SQL 2012). Each entry in my data set reflects an insurance contract with a risk score associated with a FIPS county code. I would like to use the AddJoin tool to join to an associated shape layer on FIPS county codes. The resulting map would show each county's risk with an associated color ramp depending on the risk level.

EDIT: I've taken RyanDalton's advice and created a seperate table that joins my data to a \ GIS database with shape entries on matching FIPS codes using MSSQL Server. I was able to connect to my specific database and make a new query layer useing ArcPy. The only problem I'm having is creating a color ramp based on the entries in my table (i.e. create a color ramp that shows differences between counties on values of Overall Risk Score).

The below image shows what I am trying to do with ArcPy, as well as the resulting map on the right (shows overall risk for an earthquake in the US). enter image description here

Below is a rough sample of my code which doesn't output a map with a color ramp:

 import os, arcpy
 from arcpy import env
 # Declare Directories
 workingDir = r'C:\Users\I55316\Documents\DQScore\ArcPy_Analysis'
 layerDir = workingDir + r'\Layer_USNat'
 outputDir = workingDir + r'\Output'
 dbDir = workingDir + r'\DB'
 sdeFile = 'DQ.sde'
 # Declare Database Server Connection specifications
 databaseType = r'SQL_SERVER'
 dataBaseInstance = r'pmfldt43\sql2012'
 systemAuthType = 'OPERATING_SYSTEM_AUTH'
 # Declare Query Layer Specifications
 PerilType = 'EQ'
 layerName = 'AllNationalCounty_EQ'
 query = 'SELECT * FROM dq.dbo.AllNational_EQ_GIS'
 oid_fields = 'CNTY_FIPS'
 shape_type = 'POLYGON'
 srid = '4326'
 spatial_reference = arcpy.SpatialReference('WGS 1984 UTM Zone 12N')
 # Create Database connection via .sde file
 if os.path.isfile(dbDir+ '\\'+ sdeFile):
 os.remove(dbDir+ '\\'+ sdeFile)
 arcpy.CreateDatabaseConnection_management(dbDir,
 sdeFile,
 databaseType,
 dataBaseInstance,
 systemAuthType)
 arcpy.env.workspace = workingDir+'\\'+sdeFile
 arcpy.MakeQueryLayer_management(dbDir+'\\'+sdeFile,
 layerName,
 query,
 oid_fields,
 shape_type,
 srid,
 spatial_reference)
 # Declare map document
 mxd = arcpy.mapping.MapDocument(workingDir+r'\NationalDQScoring_EQ.mxd')
 mxd.author = "Krishna Soni"
 # Add Layer
 #lyrCounties = arcpy.mapping.Layer(layerDir + r'\UnitedStates_Counties_TS2.0.0.lyr')
 lyrFaultLines = arcpy.mapping.Layer(layerDir + r'\UnitedStates_FaultLines_TS2.0.0.lyr')
 # Define data frame
 df = arcpy.mapping.ListDataFrames(mxd, "Layers")[0]
 # Add Layers
 arcpy.mapping.AddLayer(df,lyrFaultLines)
 # Add graduated Colors
 for lyr in arcpy.mapping.ListLayers(mxd):
 if lyr.symbologyType == 'GRADUATED_COLORS':
 lyr.symbology.valueField = 'Overall_Risk_Avg'
 lyr.symbology.numClassed = 10

• Brad Nesom: It sounds like what your asking is doable. But how you are asking is a little confusing. Please select the edit button and add a screenshot, and tell us more about your data. Do you want to calculate multiple values to one county? or do you just have one value for each county? The latter would not really be a heat map. (neither would the former but in that case it would only be possible to get a heat map if you had locations for each of the points.
• KrishnaS: I want to calculate the aggregate value to one county. Basically I'm just doing validation of a database to show that the "Risk" calculation is accurate, and that it's reasonable given known fault lines and simulated earth quake occurrences.
• RyanKDalton: What are your data sources? Is it a shapefile, personal or file geodatabase that you are trying to join to MSSQL Server tabular data? If both the spatial and tabular data are both contained within the SQL Server database, you should consider applying the join within the database. it will be much faster and eliminate a lot of the headaches that will follow.
• Brad Nesom: So I think you want to duplicate the manual steps to create this map in a script. Right?
• Brad Nesom: So do you want us to write the py for you? What have you tried? Please edit orig with all info not comment