3

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?

1

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.

| improve this answer | |
1

You can use FIRST and LAST along with KEEP:

When you need a value from the first or last row of a sorted group, but the needed value is not the sort key, the FIRST and LAST functions eliminate the need for self-joins or views and enable better performance.

One implementation is as follows:

SELECT JOB
, MIN(EST_COMPLETION) KEEP (DENSE_RANK FIRST ORDER BY INSERT_DATE)
, MIN(EST_COMPLETION) KEEP (DENSE_RANK LAST ORDER BY INSERT_DATE)
, COUNT(*)
FROM press_schd
GROUP BY JOB;

Result set: enter image description here

Note that the MIN aggregate function serves no purpose here because you will always get at most one row per INSERT_DATE for each JOB. It's there just so the query will compile.

DB fiddle link

| improve this answer | |
0

I would do something like this to get the min, max and number of changes for each job. However this will not be accurate if the estimated date ever gets changed to an earlier date.

SELECT job,
       MIN(est_completion),
       MAX(est_completion),
       COUNT(job)
FROM   ktimov.press_schd
GROUP BY job
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.