Max Date using Having Criteria 1

[StevenC]

Am trying to get the max date from a table where the sum of the rows &lt;&gt; 0. ie<br>Datatable<br>CU From &nbsp;&nbsp;&nbsp;To &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SUM(Amt)<br>15255 2000-01-01 2000-03-31 12.00<br>15255 1999-10-01 1999-12-31 7,031.00<br>15255 1999-07-01 1999-09-30 6,851.00<br>15255 1999-04-01 1999-06-30 6,568.00<br><br>Need to retreive the max record with from-to dates of 2000-01-01 - 2000-03-31.<br>On Oracle 8.0<br><br>

 

[MikeLacey]

An obvious response but -<br><br>can you say:<br><br>Select Max(DATE_FLD)<br>From MY_TABLE<br>Where DATE_FLD Between DATE_VAL1 And DATE_VAL2<br> <p>Mike<br><a href=mailto:michael.j.lacey@ntlworld.com>michael.j.lacey@ntlworld.com</a><br><a href=

http://www.cargill.com/>

Cargill's Corporate Web Site</a><br>

 

[StevenC]

I can't because I need the max from and to dates that when summed do not equal 0.&nbsp;&nbsp;This is my current SQL, but I can't figure out how to choose the max from and to date row.<br><br>SELECT A.BILL_TO_CUST_ID,TO_CHAR(A.FROM_DT,'YYYY-MM-DD'),<br>TO_CHAR(A.TO_DT,'YYYY-MM-DD'),SUM( A.INVOICE_AMOUNT)<br>&nbsp;&nbsp;FROM PS_BI_HDR A<br>&nbsp;&nbsp;WHERE A.BILL_TO_CUST_ID = '52152'<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;AND A.BILL_STATUS = 'INV'<br>&nbsp;&nbsp;GROUP BY&nbsp;&nbsp;A.BILL_TO_CUST_ID,TO_CHAR(A.FROM_DT,'YYYY-MM-DD'),<br>&nbsp;&nbsp;TO_CHAR(A.TO_DT,'YYYY-MM-DD')<br>&nbsp;&nbsp;&nbsp;&nbsp;HAVING SUM( A.INVOICE_AMOUNT) &lt;&gt; 0

 

[MikeLacey]

Try <br><FONT FACE=monospace><b><br>Select CUST, Max(ADATE)<br>&nbsp;&nbsp;&nbsp;&nbsp;From (<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SELECT A.BILL_TO_CUST_ID CUST,TO_CHAR(A.FROM_DT,'YYYY-MM-DD') ADATE,<br>TO_CHAR(A.TO_DT,'YYYY-MM-DD'),SUM( A.INVOICE_AMOUNT)<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;FROM PS_BI_HDR A<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;WHERE A.BILL_TO_CUST_ID = '52152'<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;AND A.BILL_STATUS = 'INV'<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;GROUP BY A.BILL_TO_CUST_ID,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;TO_CHAR(A.FROM_DT,'YYYY-MM-DD'),<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;TO_CHAR(A.TO_DT,'YYYY-MM-DD')<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;HAVING SUM( A.INVOICE_AMOUNT) &lt;&gt; 0 <br>&nbsp;&nbsp;&nbsp;&nbsp VIRTUAL_TABLE<br>&nbsp;&nbsp;&nbsp;&nbsp;Where ADATE Between '2000-01-01' And '2000-01-31'<br></font></b> <p>Mike<br><a href=mailto:michael.j.lacey@ntlworld.com>michael.j.lacey@ntlworld.com</a><br><a href=

http://www.cargill.com/>

Cargill's Corporate Web Site</a><br>

 

[StevenC]

Thanks, Mike great idea.&nbsp;&nbsp;Worked perfectly.