Teradata Query

Database
Enthusiast

Teradata Query

Hi all,
I have a table emp. The details are :
emp_id enter_time out_time
111 2008-05-13 11:39:38 2008-05-13 12:09:38
111 2008-05-13 12:39:38 2008-05-13 09:39:38
112 2008-05-13 11:40:38 2008-05-13 12:09:38
112 2008-05-13 11:39:38 2008-05-13 09:39:38
..
..
..
...
for n no. of employees.

One employee can have more then one entry in the table.
I Need to populate the first_entered and last_out_time.
The query output should look like as :

emp_id enter_time out_time first_entered Last_out_time
111 2008-05-13 11:39:38 2008-05-13 12:09:38 2008-05-13 11:39:38 2008-05-13 21:39:38
111 2008-05-13 12:39:38 2008-05-13 21:39:38 2008-05-13 11:39:38 2008-05-13 21:39:38
112 2008-05-13 11:40:38 2008-05-13 12:09:38 2008-05-13 11:39:38 2008-05-13 22:39:38
112 2008-05-13 12:39:38 2008-05-13 13:39:38 2008-05-13 11:39:38 2008-05-13 22:39:38
112 2008-05-13 15:39:38 2008-05-13 22:39:38 2008-05-13 11:39:38 2008-05-13 22:39:38
..
..
..
...
Can you plz help or suggest me on this?

8 REPLIES
Junior Contributor

Re: Teradata Query

Hi Monika,

SELECT
emp_id,
enter_time,
out_time,
MIN(enter_time) OVER (PARTITION BY emp_id) AS first_entered,
MAX(out_time) OVER (PARTITION BY emp_id) AS Last_out_time
FROM tab

Dieter
Enthusiast

Re: Teradata Query

Thanks Dieter. Thank you very much.
Could anyone or you can suggest on this too.

I have a table emp. The details are :
emp_id enter_sequence enter_gate
111 1 8
111 2 3
112 1 1
112 2 7
112 3 3 ..
..
..
...
for n no. of employees.

One employee can have more then one entry in the table.
I Need to populate the first_entered_gate and Last_enter_gate.
The query output should look like as :

emp_id enter_sequence enter_gate first_entered_gate Last_enter_gate
111 1 8 8 3
111 2 3 8 3
112 1 1 1 3
112 2 7 1 3
112 3 3 1 3
..
..
..
...
Can you plz help or suggest me on this?
Junior Contributor

Re: Teradata Query

Hi Monika,
in that case you have to nest OLAP functions:

SELECT
emp_id,
enter_sequence,
enter_gate,
MIN(CASE WHEN enter_sequence = minseq THEN enter_gate END) OVER (PARTITION BY emp_id) AS first_entered_gate,
MAX(CASE WHEN enter_sequence = maxseq THEN enter_gate END) OVER (PARTITION BY emp_id) AS Last_enter_gate
FROM
(
SELECT
emp_id,
enter_sequence,
enter_gate,
MIN(enter_sequence) OVER (PARTITION BY emp_id) AS minseq,
MAX(enter_sequence) OVER (PARTITION BY emp_id) AS maxseq
FROM tab
) dt

Dieter
Enthusiast

Re: Teradata Query

Thank you very much Dieter.
Your kindness help me a lot.
Enthusiast

Re: Teradata Query

Hi Dieter,
could you plz suggest me if the enter_gate value is null then i don't want that null value.
it means, it has to pick enter_sequence number considering the enter_gate value is not null. could you please suggest on this.
Teradata Employee

Re: Teradata Query

Hello,

For that perhaps you can use "COALESCE(enter_gate, enter_sequence)".

HTH!

Regards,

Adeel
Enthusiast

Re: Teradata Query

Hi Dieter,
could you plz update on this. while selecting the seq_no itself we need to have that not null condition.
Thanks in advance.
Junior Contributor

Re: Teradata Query

Hi Monika,
Adeel already gave you he correct answer:
COALESCE returns the first parameter which is not null, you just have to adjust it to your needs.

Dieter