I have included the complete SQL statement. I keep getting +++ in ontime and percent. select c.totallines, c.ontime, decimal(((c.ontime/c.totallines)*100),15,2) as percent from (select decimal(sum(b.totallines),15,2) as totallines, decimal(sum(b.ontime),15,2) as ontime from (select a.ord as ord, a.line as line, a.custreqdate as custreqdate, a.qtyord as qtyord, a.qtyship as qtyship, a.shipdate as shipdate, a.qtyinv as qtyinv, a.invdate as invdate, a.totallines as totallines, (case when shipdate <= custreqdate and qtyship >= qtyord and shipdate <> 0 and month(date(digits(custreqdate) concat '000000')) = month(curdate()) and year(date(digits(custreqdate) concat '000000')) = year(curdate()) then 1 else (case when invdate <= custreqdate and qtyinv >= qtyord and invdate <> 0 and month(date(digits(custreqdate) concat '000000')) = month(curdate()) and year(date(digits(custreqdate) concat '000000')) = year(curdate()) then 1 else 0 end) end) as ontime from (select integer(avg(jord)) as ord, integer(avg(jline)) as line, integer(avg(jsdte)) as custreqdate, integer(avg(lqord)) as qtyord, ifnull(sum(llsqty),0) as qtyship, ifnull(max(llsdte),0) as shipdate, ifnull(sum(ilqty),0) as qtyinv, ifnull(max(siinvd),0) as invdate, 1 as totallines from erplxusrf/dcol inner join erplxf/ecl on jord=lord and jline=lline and jprod=lprod and jfac=licfac left outer join erplxf/lll on jord=llordn and jline=llolin and jprod=llprod and jfac=llfacl left outer join erplxf/sil on jord=ilord and jline=ilseq and jprod=ilprod left outer join erplxf/sih on ilord=siord and ilcust=sicust and ilinvn=siinvn where jfac='010' and jseq=1 and jsdte between 20100501 and 20100527 and lqord<>0 group by jord,jline,jprod) a) b) c result is... Position to line . . . . . ....+....1....+....2....+....3....+....4....+....5....+....6....+.. TOTALLINES ONTIME PERCENT 3,382.00 +++++++++++++++++++++ +++++++++++++++++++++ ******** End of data ********
Free Guide: Managing storage for virtual environments
Complete a brief survey to get a complimentary 70-page whitepaper featuring the best methods and solutions for your virtual environment, as well as hypervisor-specific management advice from TechTarget experts. Don’t miss out on this exclusive content!