Postgresql how to get last year_month from previous year in where clause-CodePudding

I need to show how many active customers we had and the end of the year. Therefore I need to get always last year_month from the previous year. Working with PostgreSQL.

Here my SQL to get the customer base on monthly (year_month) view.

select *

from (

with data as (
select 
a.brand,
a.d,
a.activations,
t.terminations,
a.activations-t.terminations count
from (select c.brand, dd.year_month d,
             COALESCE(case when dd.year_month is not null then count(c.customer_number) else 0 end, 0) as activations
             from generate_series(current_date - interval '8 years', current_date, '1 day') d
             left join dim_date dd on dd."date" = d.d
             left join r_contracts_report c on to_date(c.service_start_date, 'dd.mm.yyy') = d 
             where c.contract_status in ('aktiv', 'Kündigung vorgemerkt', 'gekündigt')
             and c.contract in ('3048', '3049', '3050', '3055', '3056')
             group by dd.year_month,
             brand) a,
             (select c.brand, dd.year_month d,
             COALESCE(case when dd.year_month is not null then count(c.customer_number) else 0 end, 0) as terminations
             from generate_series(current_date - interval '8 years', current_date, '1 day') d
             left join dim_date dd on dd."date" = d.d
             left join r_contracts_report c on to_date(c.termination_date, 'dd.mm.yyy') = d 
             where c.contract_status in ('aktiv', 'Kündigung vorgemerkt', 'gekündigt')
             and c.contract in ('3048', '3049', '3050', '3055', '3056')
             group by dd.year_month,
             brand) t
where a.d = t.d
and a.brand = t.brand)

select
  d.d year_month,
  d.brand,
  sum(count) over (order by d.d asc rows between unbounded preceding and current row) eop
from data d
where d.brand = '3'

) as foo

Using after "as foo" the following where clause I get the customer base for the last 12 months: WHERE year_month >= to_char ((current_date - INTERVAL '12 months'), 'YYYY-MM')

And result looks like this:

But I always want to have only the December of the previous year. In this case it would be '2021-12'.

CodePudding user response：

...
where year_month = '2021-12'

or automatically for the previous year:

...
where year_month = (extract(year from current_date) - 1)::text || '-12'

But this is a really inefficient way to get this data.