CODEWITHSUNDEEP
sqloracle-database
C Mo
C Mo

Reputation: 3

Get an employees start and end date for each position from an employee history table

I need to have in one sql query an output in one line that has the start date and end date of each position for an employee.

Current table structure is:

| EMP_ID | SYST_LOGIN | TEAM | POSITION | LOCATION | START_DATE | END_DATE |
|--------|------------|------|----------|----------|------------|----------|
| 123456 | FNAM.LNAM  | A    | POS1     | LOC1     | 01-JAN-17  | 31-JAN-17|
| 123456 | FNAM.LNAM  | A    | POS1     | LOC2     | 01-FEB-17  | 15-FEB-17|
| 123456 | FNAM.LNAM  | B    | POS1     | LOC2     | 16-FEB-17  | 15-MAR-17|
| 123465 | FNAM.LNAM  | C    | POS2     | LOC2     | 16-MAR-17  | NULL     |

I need the out put to be:

| EMP_ID | SYST_LOGIN | POSITION | LOCATION | START_DATE | END_DATE  |
|--------|------------|----------|----------|------------|-----------|
| 123456 | FNAM.LNAM  | POS1     | LOC2     | 01-JAN-17  | 15-MAR-17 |
| 123465 | FNAM.LNAM  | POS2     | LOC2     | 16-MAR-17  | NULL      |

A new employee table was built and the business is requested that all changes to an employee create a new record in the table opposed to just updating the new location. So now I want to look at an employees position and know the start date and end date. Thank you for your help.

Gordon Linoff's query below worked for this.

****Updated second scenario that I ran into****

| EMP_ID | SYST_LOGIN | TEAM | POSITION | LOCATION | START_DATE | END_DATE |
|--------|------------|------|----------|----------|------------|----------|
| 123456 | FNAM.LNAM  | A    | POS1     | LOC1     | 01-JAN-17  | 31-JAN-17|
| 123456 | FNAM.LNAM  | A    | POS1     | LOC2     | 01-FEB-17  | 15-FEB-17|
| 123456 | FNAM.LNAM  | B    | POS1     | LOC2     | 16-FEB-17  | NULL     |

Using Gordon Linoff's query he gave below:

select EMP_ID, SYST_LOGIN, POSITION, LOCATION, min(start_date) as start_date, max(end_date) as end_date from t group by EMP_ID, SYST_LOGIN, POSITION, LOCATION

Using this I get the following results:

 | EMP_ID | SYST_LOGIN | POSITION | LOCATION | START_DATE | END_DATE  |
 |--------|------------|----------|----------|------------|-----------|
 | 123456 | FNAM.LNAM  | POS1     | LOC2     | 01-JAN-17  | 15-FEB-17 |

Due to the fact the employee is still active the END_DATE on the last record is NULL and is not used when using max(END_DATE).

Upvotes: 0

Views: 1415

Answers (2)

Kris Komba
Kris Komba

Reputation: 31

I had a problem like yours and this is my approach by using laravel and its scope..

    public function scopeEmploye_Location_By_Year_Month($query, $year, $month)
    {
        $from = date($year . '-' . $month . '-' . '01');
        $to = date($year . '-' . $month . '-' . 't');

        return $query->where(function ($query) use ($from, $to) {
            $query->where('START_DATE', '<=', $from)->where('END_DATE', '>=', $from);
        })->orWhere(function ($query) use ($from, $to) {
            $query->where('START_DATE', '<=', $to)->where('END_DATE', '>=', $to);
        })->orWhere(function ($query) use ($from, $to) {
            $query->where('START_DATE', '>=', $from)->where('END_DATE', '<=', $to);
        })->orWhere(function ($query) use ($from, $to) {
            $query->where('START_DATE', '<=', $from)->where('END_DATE', '=', null);

        });
    }

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269873

Assuming there are no gaps, then this is just aggregation:

select EMP_ID, SYST_LOGIN, POSITION, LOCATION,
       min(start_date) as start_date, max(end_date) as end_date
from t
group by EMP_ID, SYST_LOGIN, POSITION, LOCATION;

Upvotes: 0

Related Questions