Performance history queries, release 2

I’ve found a bit of a mistake in one of my historical posts, so it’s time to make it right.

SELECT JCPID as INNERPID, JCVERS as INNERVERS, simd || vrjd, 
  count(1) as INNERCOUNT, 
  Avg(86400*(JCETDTIM-JCSTDTIM)) as INNERAVERAGE, 
  min(86400*(JCETDTIM-JCSTDTIM)) AS INNERMIN, 
  max(86400*(JCETDTIM-JCSTDTIM)) AS INNERMAX 
from svm910.f986114,ol910.f9860, pd910.f983051
where trim(jcpid) = trim(siobnm) and trim(jcvers) = trim (vrvers) and trim(jcpid) = trim (vrpid)
and JCETDTIM < TO_DATE('01072015','DDMMYYYY') and JCETDTIM > TO_DATE('01062015', 'DDMMYYYY')
group by jcpid, JCVERS, simd || vrjd ;

Above is the new improved code, but the difference is subtle (just like the b in subtle).  I’m using date comparison, not string comparison of the dates, which is more accurate. My SQL would have been correct if I put YYYYMMDD, because then then string comparison is accurate for dates – but because I was putting the day first – things were a little “all over the shop”.

The old code was here:

http://shannonscncjdeblog.blogspot.co.nz/2015/06/nice-ube-batch-performance-summary.html