Updating point symbology based on sql expression - is this the right way to go?

MegPeterson — Thu, 12 Apr 2012 20:46:53 GMT

Hi all - I am a newbie to Sql and am trying to convert some sql statements that a consultant put together for our old IMS/SQL set up, to work with our new ArcSDE/SQL set up and am wondering what the right approach is. At the end of this post are the original Sql statements for reference. I feel like this is an easier solution through ArcMap (V.10) etc, but I'm just not putting it together.

The situation is:

I have a point file that has a 1:Many relationship with a non-spatial table. For example: Point #1 has 3 different maintenance issues that need fixing at that location.

I need the symbology of the points to be based on the completion of the issues:

When none of the issues have been fixed, the point is symbolized as a red circle.

When some of the issues have been fixed, the point turns to a green, half circle.

When all 3 issues have been fixed, the point turns to a green circle.

In the past version, this was accomplished by basing the symbology on a "STATUS" field that referenced the 'CompletedDate' field in the related table.

If the date was null (ie: nothing had been worked on), the Status field would = 0, and the point color = Red.

If the date was not null, Status = 1 (and the point would be half green), and

If the CompletedDate (as a DateCount ) in the related table was equal to the number of issues in the RecordCount field, the point would turn green.

Below are the old 2005 sql statements. I'm wondering if this is something that I can run from inside the MXD instead of needing to go into the acutal SQL database. That whenever there is a date entered in the CompletedDate field the Status field will automatically update and voila(!), the point color will change.

We're using Enterprise v.10, SQL Server 2008, Flex 4.0 (where the editing will happen). We are not using versioned tables.

Any words of wisdom, or suggestions on how to do this?

Thank you, Meg

____________________________________
STORED PROCEDURE

CREATE PROCEDURE [sql_data].[sp_Update_ADASTATS]
AS
truncate table sql_data.ADASTATS;
INSERT INTO sql_data.ADASTATS SELECT DISTINCT FACILITYZONEID UNIQCODE, '0', COUNT(*) FROM sql_data.ADA_Actions GROUP BY FACILITYZONEID;
UPDATE sql_data.ADASTATS SET STATUS=1 WHERE sql_data.ADASTATS.UNIQCODE IN (SELECT DISTINCT FACILITYZONEID UNIQCODE FROM sql_data.ADA_Actions WHERE CompletedDate IS NOT NULL);
UPDATE sql_data.ADASTATS SET STATUS=2 WHERE sql_data.ADASTATS.UNIQCODE IN (SELECT DISTINCT UNIQCODE FROM sql_data.vw_ADASTATS WHERE DateCount = RecCount);
GO

____________________________________
VIEW

SELECT     Sql_data.ADASTATS.UNIQCODE, Sql_data.ADASTATS.RECCOUNT, COUNT(Sql_data.ADA_Actions.CompletedDate) AS DateCount
FROM         Sql_data.ADA_Actions INNER JOIN
                      Sql_data.ADASTATS ON Sql_data.ADA_Actions.FacilityZoneID = Sql_data.ADASTATS.UNIQCODE
WHERE     (Sql_data.ADA_Actions.CompletedDate IS NOT NULL)
GROUP BY Sql_data.ADASTATS.UNIQCODE, Sql_data.ADASTATS.RECCOUNT

topic Updating point symbology based on sql expression - is this the right way to go? in Data Management Questions

Updating point symbology based on sql expression - is this the right way to go?