sql to_char - format based on date condition

Question

This is for a monthly report that looks at last twelve months e.g. produced Jan. 2018 to look at Dec.2016 - Dec.2017.

I need to produce a case/when that formats a date based on condition -- if {prod_date} is last available month in dataset, then format as week-end date (12/2/2017,12/9/2017,12/16/2017, etc. - yyyyddmm). If, however, date is earlier than previous month, then format as 'yyyymm'.

End result groups should be:

20170212
20170912
20171612
20172312
20173012
201711
201710
201709
...
201612

Answer 1

This is how I understand your request:

1. You want to group by yyyymm for all months except for the last one.
2. You want to group by yyyyddmm for the last month, where dd shall be the week end (a Saturday).

Use a CASE expression to check the month. Truncate to ISO week to get a week's Monday. Add five days to get the Saturday. Group by the computed date string and aggregate whatever values you want. E.g.:

select
  grp,
  max(value1) as max_value1,
  sum(value2) as sum_value2
from
(
  select
    case when to_char(dt, 'yyyymm') = (select to_char(max(dt), 'yyyymm') from mytable)
      then to_char(trunc(dt, 'iw') + 5, 'yyyyddmm')
      else to_char(dt, 'yyyymm')
    end as grp,
    value1,
    value2
  from mytable
)
group by grp
order by grp desc;