Edit Question - KnowledgeHub

Title Body

I have written a query in which one column is a month. From that I have to get min month, max month, and median month. Below is my query. select ext.employee, pl.fromdate, ext.FULL_INC as full_inc, prevExt.FULL_INC as prevInc, (extract(year from age (pl.fromdate))*12 +extract(month from age (pl.fromdate))) as month, case when prevExt.FULL_INC is not null then (ext.FULL_INC -coalesce(prevExt.FULL_INC,0)) else 0 end as difference, (case when prevExt.FULL_INC is not null then (ext.FULL_INC - prevExt.FULL_INC) / prevExt.FULL_INC*100 else 0 end) as percent from pl_payroll pl inner join pl_extpayfile ext on pl.cid = ext.payrollid and ext.FULL_INC is not null left outer join pl_extpayfile prevExt on prevExt.employee = ext.employee and prevExt.cid = (select max (cid) from pl_extpayfile where employee = prevExt.employee and payrollid = ( select max(p.cid) from pl_extpayfile, pl_payroll p where p.cid = payrollid and pl_extpayfile.employee = prevExt.employee and p.fromdate < pl.fromdate )) and coalesce(prevExt.FULL_INC, 0) > 0 where ext.employee = 17 and (exists ( select employee from pl_extpayfile preext where preext.employee = ext.employee and preext.FULL_INC <> ext.FULL_INC and payrollid in ( select cid from pl_payroll where cid = ( select max(p.cid) from pl_extpayfile, pl_payroll p where p.cid = payrollid and pl_extpayfile.employee = preext.employee and p.fromdate < pl.fromdate ) ) )

Tags (comma-separated) Cancel