InstantForum.NET v4.1.4Teradata Forumshttp://www.teradata.com/teradataforum/info@teradata.comSat, 06 Sep 2008 12:22:44 GMT20http://www.teradata.com/teradataforum/Topic11423-9-1.aspxHi 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.DieterTue, 27 May 2008 15:19:22 GMTdnoethhttp://www.teradata.com/teradataforum/Topic11423-9-1.aspxHi Dieter,could you plz update on this. while selecting the seq_no itself we need to have that not null condition.Thanks in advance.Tue, 27 May 2008 10:09:30 GMTMonikahttp://www.teradata.com/teradataforum/Topic11423-9-1.aspxHello,For that perhaps you can use "COALESCE(enter_gate, enter_sequence)".HTH!Regards,AdeelThu, 22 May 2008 02:54:57 GMTAdeel.Chaudhryhttp://www.teradata.com/teradataforum/Topic11423-9-1.aspxHi 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.Thu, 22 May 2008 02:52:29 GMTMonikahttp://www.teradata.com/teradataforum/Topic11423-9-1.aspxThank you very much Dieter.Your kindness help me a lot.Thu, 15 May 2008 01:07:13 GMTMonikahttp://www.teradata.com/teradataforum/Topic11423-9-1.aspxHi 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 ) dtDieterWed, 14 May 2008 15:02:26 GMTdnoethhttp://www.teradata.com/teradataforum/Topic11423-9-1.aspxThanks 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?Wed, 14 May 2008 07:18:25 GMTMonikahttp://www.teradata.com/teradataforum/Topic11423-9-1.aspxHi 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 tabDieterWed, 14 May 2008 02:27:00 GMTdnoethhttp://www.teradata.com/teradataforum/Topic11423-9-1.aspxHi 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?Tue, 13 May 2008 08:02:35 GMTMonika