|
|
|
Forum Member
 
Group: Forum Members
Last Login: 7/3/2008 4:50:48 AM
Posts: 33,
Visits: 186
|
|
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?
Regards:
Monika
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 6/25/2008 7:26:06 AM
Posts: 474,
Visits: 202
|
|
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
|
|
|
|
|
Forum Member
Group: Forum Members
Last Login: 7/3/2008 4:50:48 AM
Posts: 33,
Visits: 186
|
|
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?
Regards:
Monika
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 6/25/2008 7:26:06 AM
Posts: 474,
Visits: 202
|
|
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
|
|
|
|
|
Forum Member
Group: Forum Members
Last Login: 7/3/2008 4:50:48 AM
Posts: 33,
Visits: 186
|
|
Thank you very much Dieter.
Your kindness help me a lot.
Regards:
Monika
|
|
|
|
|
Forum Member
Group: Forum Members
Last Login: 7/3/2008 4:50:48 AM
Posts: 33,
Visits: 186
|
|
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.
Regards:
Monika
|
|
|
|
|
Forum Guru
Group: Forum Members
Last Login: 2 days ago @ 9:11:30 AM
Posts: 63,
Visits: 185
|
|
Hello,
For that perhaps you can use "COALESCE(enter_gate, enter_sequence)".
HTH!
Regards,
Adeel
|
|
|
|
|
Forum Member
Group: Forum Members
Last Login: 7/3/2008 4:50:48 AM
Posts: 33,
Visits: 186
|
|
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.
Regards:
Monika
|
|
|
| | |