title: Last day of the month with a twist in SQLPLUS
body: <p>I would appreciate a little expert help please.</p>
  
  <p>in an SQL SELECT statement I am trying to get the last day with data per month for the last year.
  Example, I am easily able to get the last day of each month and join that to my data table, but the problem is, if the last day of the month does not have data, then there is no returned data. What I need is for the SELECT to return the last day with data for the month.</p>
  
  <p>This is probably easy to do, but to be honest, my brain fart is starting to hurt.</p>
  
  <p>I've attached the select below that works for returning the data for only the last day of the month for the last 12 months.</p>
  
  <p>Thanks in advance for your help!</p>
  
  <pre><code>SELECT fd.cust_id,fd.server_name,fd.instance_name,
      TRUNC(fd.coll_date) AS coll_date,fd.column_name
  FROM super_table fd,
      (SELECT TRUNC(daterange,'MM')-1 first_of_month
      FROM (
      select TRUNC(sysdate-365,'MM') + level as DateRange
      from    dual
      connect by level&lt;=365)
      GROUP BY TRUNC(daterange,'MM')) fom
  WHERE fd.cust_id = :CUST_ID
  AND fd.coll_date &gt; SYSDATE-400
  AND TRUNC(fd.coll_date) = fom.first_of_month
  GROUP BY fd.cust_id,fd.server_name,fd.instance_name,
      TRUNC(fd.coll_date),fd.column_name
  ORDER BY fd.server_name,fd.instance_name,TRUNC(fd.coll_date)
  </code></pre>