Oracle SQL Only select dates that align to month end or quarter end

Question

I have a query where the user inputs a start and end date.

AND PP.PATTR_CALCUL_DATE >= '31 JUN 2012'
AND PP.PATTR_CALCUL_DATE <= '30 SEP 2012'

The query returns data like this:

|    DATE    |  VALUE  |
| 01/07/2012 |   1.25  |
| 31/07/2012 |   2.25  |
| 05/09/2012 |   1.75  |
| 22/08/2012 |   3.99  |
| 30/09/2012 |   1.25  |
| 31/08/2012 |   6.37  |

Based on a varible defined by the user, the query either needs to return all the dates that align to month end in the specified range. So the result in this instance would be:

|    DATE    |  VALUE  |
| 31/07/2012 |   2.25  |
| 30/09/2012 |   1.25  |
| 31/08/2012 |   6.37  |

OR it needs to return all dates that align with Quarter end. Where the result would be:

|    DATE    |  VALUE  |
| 30/09/2012 |   1.25  |

They also would need a third option that would just return all the data in the original table

Appreciate any help please.

Thanks

Vincent Malgrat Vincent Malgrat · Accepted Answer · 2012-11-22T15:59:01

You can make a condition that will change based on a param:

/* with :param = 'date' */
SQL> WITH data AS (
  2  SELECT to_date('01/07/2012', 'dd/mm/yyyy') dat, 1.25 value FROM DUAL
  3  UNION ALL SELECT to_date('31/07/2012', 'dd/mm/yyyy'), 2.25 FROM DUAL
  4  UNION ALL SELECT to_date('05/09/2012', 'dd/mm/yyyy'), 1.75 FROM DUAL
  5  UNION ALL SELECT to_date('22/08/2012', 'dd/mm/yyyy'), 3.99 FROM DUAL
  6  UNION ALL SELECT to_date('30/09/2012', 'dd/mm/yyyy'), 1.25 FROM DUAL
  7  UNION ALL SELECT to_date('31/08/2012', 'dd/mm/yyyy'), 6.37 FROM DUAL
  8  )
  9  SELECT dat,
 10         value
 11    FROM data
 12   WHERE dat = CASE :param
 13                 WHEN 'date'    THEN dat
 14                 WHEN 'month'   THEN last_day(dat)
 15                 WHEN 'quarter' THEN add_months(trunc(dat, 'q'), 3) - 1
 16               END;

DAT              VALUE
----------- ----------
01/07/2012        1,25
31/07/2012        2,25
05/09/2012        1,75
22/08/2012        3,99
30/09/2012        1,25
31/08/2012        6,37


/* with :param = 'month' */
DAT              VALUE
----------- ----------
31/07/2012        2,25
30/09/2012        1,25
31/08/2012        6,37

/* with :param = 'quarter' */
DAT              VALUE
----------- ----------
30/09/2012        1,25

Oracle SQL Only select dates that align to month end or quarter end

1 Answers