select TO_CHAR (TO_DATE(DATE,'YYYYMMDD'), 'DD-MON-YY'),user_id,
CASE
WHEN (flag_1=1 or flag_2=1)
and TO_CHAR (TO_DATE(date,'YYYYMMDD'), 'YYYYMMDD') >= TO_CHAR (TO_DATE(date,'YYYYMMDD')-30, 'YYYYMMDD')
THEN 1 else 0 end last_30,
CASE
WHEN (flag_1=1 or flag_2=1)
and TO_CHAR (TO_DATE(date,'YYYYMMDD'), 'YYYYMMDD') >= TO_CHAR (TO_DATE(date,'YYYYMMDD')-1, 'YYYYMMDD')
THEN 1 else 0 end last_day
from table1