Definition Query to find next date value in ArcSDE attribute table?

Question

I have an ArcSDE feature class that contains a date field called "SALE_DATE". This sale date represents the bid date for ALberta crown mineral rights sales. A sale occurs about twice per month (one date value approx every two weeks). Eg. for January there would be values of 1/14/2015 and 1/28/2015. There are only ever 4 sales dates release at a time (4 date values in the feature class) and they revolve as the current sale occurrs, another one is released. I need to create 4 layers from this one feature class, each representing the next sale date. The trick is I want to set them up with SQL def query syntax so that the dates automatically revolve and I dont have to manually adjust the sale date, it should just grab the next available date value that is available.

This is an example of the syntax I have for the first upcoming sale date:

PROVINCE = 'AB' AND SALE_DATE > SYSDATE AND TO_CHAR (SALE_DATE, 'MM') = TO_CHAR (SYSDATE, 'MM') AND TO_CHAR (SALE_DATE, 'DD') < (TO_CHAR (SYSDATE, 'DD') + 2)

Answer 1

A registered (non-spatial) view will be of great benefit to solve this problem:

1. Create an Oracle view definition (preferably in the schema where the spatial data are stored) which identifies the distinct dates and ranks them (likely earliest to latest.)

   CREATE or REPLACE view AB_MINERAL_SALE_DATES_V as 
      SELECT
        SALE_DATE, 
        row_number() OVER (order by saledate ASC) as DATE_RANK
      FROM
        (select distinct sale_date from my_sde_feature_class)
   ;
   

2. Register this view with SDE and with the Geodatabase from ArcCatalog by right-clicking on it >> Admin >> Register.

3. Add the registered view to your MXD along with the featureclass

4. Join the view (now a table object in Desktop) to the featureclass using the shared SALE_DATE column.
5. Create a query definition on the result where DATE_RANK = 1
6. Copy the resulting layer three additional times, changing the query definition to DATE_RANK = 2, 3, 4
7. save often, it is ArcGIS after all

This could also be accomplished by a collection of Spatial View definitions where the logic above is embedded into each spatial view. That way, the geometries and dates are queried, ranked, and selectively-reduced to only make certain geometries display for each layer. There are pros and cons to one approach over the other.

My suggestion allows for the sales data to be separate from the geometry just in case the data gets de-normalized -- (example: same rights might be sold during multiple dates.)