All Forums Database
Sunar 59 posts Joined 02/08
13 May 2008
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:38112 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_time111 2008-05-13 11:39:38 2008-05-13 12:09:38 2008-05-13 11:39:38 2008-05-13 21:39:38111 2008-05-13 12:39:38 2008-05-13 21:39:38 2008-05-13 11:39:38 2008-05-13 21:39:38112 2008-05-13 11:40:38 2008-05-13 12:09:38 2008-05-13 11:39:38 2008-05-13 22:39:38112 2008-05-13 12:39:38 2008-05-13 13:39:38 2008-05-13 11:39:38 2008-05-13 22:39:38112 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?

dnoeth 4628 posts Joined 11/04
14 May 2008

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 tabDieter

Dieter

Sunar 59 posts Joined 02/08
14 May 2008

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_gate111 1 8 8 3111 2 3 8 3112 1 1 1 3112 2 7 1 3 112 3 3 1 3.........Can you plz help or suggest me on this?

dnoeth 4628 posts Joined 11/04
14 May 2008

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 ) dtDieter

Dieter

Sunar 59 posts Joined 02/08
15 May 2008

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

Sunar 59 posts Joined 02/08
22 May 2008

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.

Adeel Chaudhry 773 posts Joined 04/08
22 May 2008

Hello,For that perhaps you can use "COALESCE(enter_gate, enter_sequence)".HTH!Regards,Adeel

-- If you are stuck at something .... consider it an opportunity to think anew.

Sunar 59 posts Joined 02/08
27 May 2008

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.

dnoeth 4628 posts Joined 11/04
27 May 2008

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

Dieter

You must sign in to leave a comment.