if 
(b.Chek_date between to_date ('Oct-15-2013','mon/dd/yyyy')and to_date ('oct-31-2013','mon/dd/yyyy') then A.pay_frequency IN('2','3','4')
when b.chek_date < to_date('oct-15-2013', 'mon/dd/yyyy') then A.pay_frequency IN('1')
End if

where ( <check_date is 'Oct-15-2013','mon/dd/yyyy' to 'oct-31-2013'> AND <pay frequency =2,3,5> )
   OR ( <check_date is less the 'Oct-15-2013'> AND <pay frequency =1> )

SELECT A.COMPANY, A.Process_level, C.Head_company, D.Head_proc_Lev,  A.EMPLOYEE, 
 MAX(B.CHECK_DATE) Last_check_date,Term_date, A.Pay_frequency, A.EMP_STATUS, A.date_hired, 

A.adj_hire_date, C.GBU,C.Service_Mgr STM, C.hrp, A.Fica_NBR  FROM LAWSON.EMPLOYEE A, LAWSON.PAYMASTR 

B, LAWSON.COMPROF C, LAWSON.TAXGROUP D
 
 
  WHERE 
  
   A.EMPLOYEE = B.EMPLOYEE
AND A.PROCESS_LEVEL = C.PROCESS_LEVEL
AND A.COMPANY = B.COMPANY
AND A.COMPANY = C.COMPANY
And A.company = D.company
and A.PROCESS_LEVEL= D.process_level
and (Term_date >to_date ('Oct-02-2013','mon/dd/yyyy') or Term_date = To_date('jan-01-1700','mon/dd/yyyy') )
AND C.COMPANY = '1'
and
if 
(b.Chek_date between to_date ('Oct-15-2013','mon/dd/yyyy')and to_date ('oct-31-2013','mon/dd/yyyy') then A.pay_frequency IN('2','3','4')
when b.chek_date < to_date('oct-15-2013', 'mon/dd/yyyy') then A.pay_frequency IN('1')
End if


and A.adj_hire_date < to_date ('Oct-01-2013','mon/dd/yyyy') 

Group by  A.COMPANY,A.Process_level, C.Head_company, C.National_Name, A.Department, A.EMPLOYEE, 

A.Term_date, A.EMP_STATUS, A.date_hired, A.adj_hire_date, B.CHECK_DATE ,C.GBU, C.Service_Mgr, C.hrp, 

D.Head_proc_Lev, A.Pay_frequency, A.Fica_NBR 
ORDER BY B.CHECK_DATE DESC;

where case when x = y then a in (1,2, 3)
           else y = m
      end;
 

select a.company
    , a.process_level
    , c.head_company
    , d.head_proc_lev
    , a.employee
    , max(b.check_date) last_check_date
    , term_date
    , a.pay_frequency
    , a.emp_status
    , a.date_hired
    , a.adj_hire_date
    , c.gbu
    , c.service_mgr stm
    , c.hrp
    , a.fica_nbr
from  lawson.employee a
    , lawson.paymastr b
    , lawson.comprof c
    , lawson.taxgroup d
where a.employee = b.employee
  and a.process_level = c.process_level
  and a.company = b.company
  and a.company = c.company
  and a.company = d.company
  and a.process_level= d.process_level
  and (term_date >to_date ('Oct-02-2013','mon/dd/yyyy')
       or term_date = to_date('jan-01-1700','mon/dd/yyyy') )
  and c.company = '1'
--  and if (b.chek_date between to_date ('Oct-15-2013','mon/dd/yyyy')and to_date
--      ('oct-31-2013','mon/dd/yyyy') then a.pay_frequency in('2','3','4') when
--      b.chek_date < to_date('oct-15-2013', 'mon/dd/yyyy') then a.pay_frequency
--      in('1')
--end if
  and a.adj_hire_date < to_date ('Oct-01-2013','mon/dd/yyyy')
group by a.company
    , a.process_level
    , c.head_company
    , d.head_proc_lev
    , a.employee
    , term_date
    , a.pay_frequency
    , a.emp_status
    , a.date_hired
    , a.adj_hire_date
    , c.gbu
    , c.service_mgr 
    , c.hrp
    , a.fica_nbr
order by b.check_date desc;