How to get MIN and MAX date from table data

oracleselect

We have a nightly job that uses sql loader to insert data from a csv file. Here is a small sample of the data:

select * from ktimov.press_schd order by job;

PLANT JOB EST_COMPLETION INSERT_DATE
10    111 6/26/2017      5/10/2017
10    111 7/1/2017       5/11/2017
10    111 7/5/2017       5/12/2017
10    222 6/20/2017      5/10/2017
10    222 6/26/2017      5/11/2017
10    333 7/26/2017      5/10/2017
10    333 6/30/2017      5/11/2017
10    333 8/10/2017      5/12/2017
10    333 8/6/2017       5/13/2017
10    333 8/7/2017       5/14/2017

INSERT_DATE is default sysdate of when the record was inserted.
EST_COMPLETION is an estimate of when the JOB will be completed. So for example:

On 5/10/17, JOB# 111 was estimated to be completed by 6/26. Then on 5/11 the estimated completion was changed to 7/1.

I'm looking to query this data to show the following:

JOB#, First EST Date, Last Est date, Count of how many times it was changed.

The end result should be like this:

JOB FIRST_DATE LAST DATE Count_Changes
111 6/26/2017  7/5/2017  3
222 6/20/2017  6/26/2017 2
333 7/26/2017  8/7/2017  5

Any ideas how I can accomplish this?

Best Answer

Here's a simple method. We use a CTE to get the first and last INSERT_DATEs for each job; then, join to the main table to retrieve the estimated completions dates from those first and last loads:

WITH JobFirstLast (job, first_load, last_load, load_count) AS
     (SELECT job,
             MIN(INSERT_DATE) as first_load,
             MAX(INSERT_DATE) as last_load,
             COUNT(job) as load_count
        FROM ktimov.press_schd
       GROUP BY job
     )
SELECT j.job
      ,f.EST_COMPLETION as first_est_completion
      ,l.EST_COMPLETION as last_est_completion
      ,j.load_count - 1 as times_changed
  FROM JobFirstLast j
         LEFT  JOIN ktimov.press_schd f ON (j.job = f.job AND j.first_load = f.INSERT_DATE)
         LEFT  JOIN ktimov.press_schd l ON (j.job = l.job AND j.last_load = l.INSERT_DATE);

NOTE: I'm assuming that the first load is simply establishing the initial values, and that only subsequent loads are counted as changes. If you really want the total number of loads, change j.load_count - 1 to j.load_count.

Related Question