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?
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
