How can I write a definition query for feature classes to divide a larger table by year?

494
2
01-15-2019 06:53 AM
JayJohnson2
Occasional Contributor

My source data is a set of annual tables that have been merged into one.  

If I use these definition queries, only the first one works.  Is there an error in my SQL statement?

Status = 'Completed' AND Delivery_Date > date '2018-01-15 00:00:00'
Status = 'Completed' AND Delivery_Date < date '2018-01-15 00:00:00' AND Delivery_Date > date '2017-01-15 00:00:00'
Status = 'Completed' AND Delivery_Date < date '2017-01-15 00:00:00' AND Delivery_Date > date '2016-01-15 00:00:00'
Status = 'Completed' AND Delivery_Date < date '2016-01-15 00:00:00' AND Delivery_Date > date '2015-01-15 00:00:00'
Status = 'Completed' AND Delivery_Date < date '2015-01-15 00:00:00' AND Delivery_Date > date '2014-01-15 00:00:00'
Status = 'Completed' AND Delivery_Date > date '2014-01-15 00:00:00'
The data works fine as separate feature classes.  My client requested I do it this way.
2 Replies
JoshuaBixby
MVP Esteemed Contributor

For one, make sure the date field is indexed or you will be suffering some terrible performance with data from the layer.

When working with ranges in file geodatabases, whether dates or numbers in general, BETWEEN performs better.  What happens if you try:

Status = 'Completed' AND (Delivery_Date BETWEEN date '2017-01-15 00:00:00' AND date '2018-01-15 00:00:00')
0 Kudos
JayJohnson2
Occasional Contributor
Joshua,
I will try that.  My coworker Ryan did get it to work without between:
Status = 'Completed' AND Delivery_Date <=  CURRENT_DATE AND Delivery_Date >= CURRENT_DATE - 365
Status = 'Completed' AND Delivery_Date <=  CURRENT_DATE -365 AND Delivery_Date >= CURRENT_DATE -730
Status = 'Completed' AND Delivery_Date <=  CURRENT_DATE -730 AND Delivery_Date >= CURRENT_DATE -1095
Status = 'Completed' AND Delivery_Date <=  CURRENT_DATE -1095 AND Delivery_Date >= CURRENT_DATE -1460
Status = 'Completed' AND Delivery_Date <=  CURRENT_DATE -1460 AND Delivery_Date >= CURRENT_DATE -1825
Status = 'Completed' AND Delivery_Date < CURRENT_DATE -1825
Jay
0 Kudos