Generating a series of Previous Month End Dates for a given number of years in Oracle

A very short oracle query for generating a range of previous month end dates for a number of years

  with years as  
  (select extract(year from sysdate) - (level - 1) as year  
   from dual  
  connect by level <=  
    (select extract(year from sysdate) -  
        extract(year from date '2000-12-01')   --range of years sysdate to 2001 
     from dual)  
   order by year desc),  
 months as  
  (  
  select rownum as month_num,to_char(date '2000-12-01' + numtoyminterval(level,'month'),'MONTH') as month  
    from dual  
   connect by level <= 12  
 )  
 select last_day(to_date(to_char(years.year) || '-' ||   
 to_char(months.month_num) || '-1',  
         'yyyy-mm-dd')) as as_of_date  
  from years, months  
  where last_day(to_date(years.year || '-' || months.month, 'yyyy-mm')) < sysdate  

Full Year Pivot Using Oracle

A quick example of a full year pivot using Oracle

select 
  * 
FROM 
  (
    select 
      account_no, 
      to_char(
        trunc(some_date_column, 'MONTH'), 
        'MON YYYY'
      ) as transaction_month 
    from 
      some_table 
    where 
      trunc(some_date_column, 'YEAR') = '01-jan-2018'
  ) PIVOT (
    count(transaction_month) for transaction_month in (
      'JAN 2018', 'FEB 2018', 'MAR 2018', 
      'APR 2018', 'MAY 2018', 'JUN 2018', 
      'JUL 2018', 'AUG 2018', 'SEP 2018', 
      'OCT 2018', 'NOV 2018', 'DEC 2018'
    )
  )

Oracle Case statements when testing for NULL

Just a little tidbit when working with ORACLE and case statements.

The first method, where the given column name is passed to successive WHEN statements, WHEN must have a value to work with, so you have to convert the null value into something and test for it.

Method 1: Covert the incoming value first

CASE nvl(some_column,'NULL')
         WHEN 'NULL' THEN some_other_column
         WHEN 'ABC' THEN another_column
         ELSE
          some_column
         END some_column

Method 2: do the condition checks in the WHEN

CASE 
    WHEN nval(some_column,'NULL') = 'NULL' THEN some_other_column
     WHEN some_column = 'ABC' THEN another_column
     ELSE some_column
     END  some_column