topic Re: How can I write a definition query for feature classes to divide a larger table by year? in Spatial Data Science Questions https://community.esri.com/t5/spatial-data-science-questions/how-can-i-write-a-definition-query-for-feature/m-p/247442#M573 <HTML><HEAD></HEAD><BODY><DIV>Joshua,</DIV><DIV> </DIV><DIV>I will try that.&nbsp; My coworker Ryan did get it to work without between:</DIV><DIV> </DIV><DIV>Status = 'Completed' AND Delivery_Date &lt;=&nbsp; CURRENT_DATE AND Delivery_Date &gt;= CURRENT_DATE - 365</DIV><DIV>Status = 'Completed' AND Delivery_Date &lt;=&nbsp; CURRENT_DATE -365 AND Delivery_Date &gt;= CURRENT_DATE -730</DIV><DIV>Status = 'Completed' AND Delivery_Date &lt;=&nbsp; CURRENT_DATE -730 AND Delivery_Date &gt;= CURRENT_DATE -1095</DIV><DIV>Status = 'Completed' AND Delivery_Date &lt;=&nbsp; CURRENT_DATE -1095 AND Delivery_Date &gt;= CURRENT_DATE -1460</DIV><DIV>Status = 'Completed' AND Delivery_Date &lt;=&nbsp; CURRENT_DATE -1460 AND Delivery_Date &gt;= CURRENT_DATE -1825</DIV><DIV>Status = 'Completed' AND Delivery_Date &lt; CURRENT_DATE -1825</DIV><DIV> </DIV><DIV>Jay</DIV><DIV> </DIV></BODY></HTML> Tue, 15 Jan 2019 18:48:55 GMT JayJohnson2 2019-01-15T18:48:55Z How can I write a definition query for feature classes to divide a larger table by year? https://community.esri.com/t5/spatial-data-science-questions/how-can-i-write-a-definition-query-for-feature/m-p/247440#M571 <HTML><HEAD></HEAD><BODY><P>My source data is a set of annual tables that have been merged into one. &nbsp;</P><P></P><P>If I use these definition queries, only the first one works.&nbsp; Is there an error in my SQL statement?</P><P></P><DIV>Status = 'Completed' AND Delivery_Date &gt; date '2018-01-15 00:00:00'</DIV><DIV>Status = 'Completed' AND Delivery_Date &lt; date '2018-01-15 00:00:00' AND Delivery_Date &gt; date '2017-01-15 00:00:00'</DIV><DIV>Status = 'Completed' AND Delivery_Date &lt; date '2017-01-15 00:00:00' AND Delivery_Date &gt; date '2016-01-15 00:00:00'</DIV><DIV>Status = 'Completed' AND Delivery_Date &lt; date '2016-01-15 00:00:00' AND Delivery_Date &gt; date '2015-01-15 00:00:00'</DIV><DIV>Status = 'Completed' AND Delivery_Date &lt; date '2015-01-15 00:00:00' AND Delivery_Date &gt; date '2014-01-15 00:00:00'</DIV><DIV>Status = 'Completed' AND Delivery_Date &gt; date '2014-01-15 00:00:00'</DIV><DIV> </DIV><DIV>The data works fine as separate feature classes.&nbsp; My client requested I do it this way.</DIV></BODY></HTML> Tue, 15 Jan 2019 14:53:20 GMT https://community.esri.com/t5/spatial-data-science-questions/how-can-i-write-a-definition-query-for-feature/m-p/247440#M571 JayJohnson2 2019-01-15T14:53:20Z Re: How can I write a definition query for feature classes to divide a larger table by year? https://community.esri.com/t5/spatial-data-science-questions/how-can-i-write-a-definition-query-for-feature/m-p/247441#M572 <HTML><HEAD></HEAD><BODY><P>For one, make sure the date field is indexed or you will be suffering some terrible performance with data from the layer.</P><P></P><P>When working with ranges in file geodatabases, whether dates or numbers in general, BETWEEN performs better.&nbsp; What happens if you try:</P><PRE class="line-numbers language-sql"><CODE><SPAN class="keyword token">Status</SPAN> <SPAN class="operator token">=</SPAN> <SPAN class="string token">'Completed'</SPAN> <SPAN class="operator token">AND</SPAN> <SPAN class="punctuation token">(</SPAN>Delivery_Date <SPAN class="operator token">BETWEEN</SPAN> date <SPAN class="string token">'2017-01-15 00:00:00'</SPAN> <SPAN class="operator token">AND</SPAN> date <SPAN class="string token">'2018-01-15 00:00:00'</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="line-numbers-rows"><SPAN>‍</SPAN></SPAN></CODE></PRE></BODY></HTML> Tue, 15 Jan 2019 16:22:38 GMT https://community.esri.com/t5/spatial-data-science-questions/how-can-i-write-a-definition-query-for-feature/m-p/247441#M572 JoshuaBixby 2019-01-15T16:22:38Z Re: How can I write a definition query for feature classes to divide a larger table by year? https://community.esri.com/t5/spatial-data-science-questions/how-can-i-write-a-definition-query-for-feature/m-p/247442#M573 <HTML><HEAD></HEAD><BODY><DIV>Joshua,</DIV><DIV> </DIV><DIV>I will try that.&nbsp; My coworker Ryan did get it to work without between:</DIV><DIV> </DIV><DIV>Status = 'Completed' AND Delivery_Date &lt;=&nbsp; CURRENT_DATE AND Delivery_Date &gt;= CURRENT_DATE - 365</DIV><DIV>Status = 'Completed' AND Delivery_Date &lt;=&nbsp; CURRENT_DATE -365 AND Delivery_Date &gt;= CURRENT_DATE -730</DIV><DIV>Status = 'Completed' AND Delivery_Date &lt;=&nbsp; CURRENT_DATE -730 AND Delivery_Date &gt;= CURRENT_DATE -1095</DIV><DIV>Status = 'Completed' AND Delivery_Date &lt;=&nbsp; CURRENT_DATE -1095 AND Delivery_Date &gt;= CURRENT_DATE -1460</DIV><DIV>Status = 'Completed' AND Delivery_Date &lt;=&nbsp; CURRENT_DATE -1460 AND Delivery_Date &gt;= CURRENT_DATE -1825</DIV><DIV>Status = 'Completed' AND Delivery_Date &lt; CURRENT_DATE -1825</DIV><DIV> </DIV><DIV>Jay</DIV><DIV> </DIV></BODY></HTML> Tue, 15 Jan 2019 18:48:55 GMT https://community.esri.com/t5/spatial-data-science-questions/how-can-i-write-a-definition-query-for-feature/m-p/247442#M573 JayJohnson2 2019-01-15T18:48:55Z