Labeling the Most Recent Inspection

Question

I have a maintenance feature class that has many records per Unique ID of the corresponding pipe.  Is there a way to either label or symbolize the features so that the most recent inspection is the one the user sees?

The goal is for the user (using either Desktop or AGOL) to look at the map or a dashboard and quickly identify which sewers have been serviced recently and which ones have not, without having to click on each individual pipe to see the most recent date.

I've seen other posts that are similar to this in that they want to show the most recent inspection in a pop-up, or to calculate a date-since last serviced, but I'm looking for something that's a little more end-user friendly.

I appreciate any and all thoughts you can throw at me!

PipeMaintenance.jpg

Clip_138.jpg

example1.PNG

NikkiRosemeyer · Answer

Hi Jim!

Thanks for your response!  I am duplicating geometry with this feature class.  We are also doing both online and offline editing of this feature class.

I tried the definition query part of your suggestion (and I got it to work - YES!) , but it didn't return the most recent record.

I've submitted a case to Esri Technical Support.  I feel like iterations of this same question have been asked over and over through the years on this and other forums, but we just can't seem to get it to work!  Thank you so much for your input.  I really appreciate it!

JimWilliams · Answer

Hello Nikki,

I assume there are multiple work orders for each pipe. I am assuming you are not duplicating the geometry? As long as you are not needing to do offline editing or publish this as a feature class I would use a view for this and have it be a new layer. Maybe something like this:

SELECT        l.OBJECTID,l.SHAPE, lj.*
FROM            PIPES l OUTER apply
                             (SELECT TOP 1  lc.*
                               FROM Pipe_Maintenance_table lc
                               WHERE lc.ID = l. and year(Completed_Date)=year(GETDATE())
                               order by lc.Completed_Date desc
                               ) AS lj

If a view doesn't work for you try a definition query where you do a group by clause. I think this would group all you records by unique id, then get the most recent date. Just a disclaimer, I also use the object id sometimes to get more recent records vs less recent records.

InspectionDate in (Select Max(InspectionDate) from featureclass Group by uniqueID)

Might be a little confusing but hopefully this gets you on a good track. Not sure if my queries are a 100% correct either.