hiveql - Joining table on previous month with Hive -

i trying join table based on previous month after several failed attempts i'm not sure how solve this.

i have larger query join section pretty simple, first attempt this:

left outer join (select user_id, coalesce(count(*),0) count_m,mth  table_name  group user_id,mth ) m on month(main_table.local_date) -1 = m.mth , m.user_id = main_table.user_id 

but of course if have date in january , subtract 1 0, not 12.

the next attempt hive's datesub function:

left outer join (select user_id, coalesce(count(*),0) count_m,mth  table_name  group user_id,mth ) m on month(date_sub(main_table.local_date,31)) = m.mth , m.user_id = main_table.user_id 

but again problem obvious - not every month has 31 days end problems dates previous month not correctly matched (although @ least solves january - december problem previous approach.

i tried use case approach in on command, handle jan-dec problem, before realizing hive accept equi-joins.

any appreciated.

subtract number of days in month date , extract month. see code below:

on month(date_sub(main_table.local_date,day(main_table.local_date))) = m.mth