0

Hi i am new in oracle. i am having a problem. i need to collect datewise max time and min time from my table "dailyAtt"
here is the data formate in my table..

ID Code Ptime
1 2570 14/10/2010 1:00 Pm
2 2570 14/10/2010 2:00 Pm
3 2570 15/10/2010 7:00 Am
4 2570 15/10/2010 4:00 Pm
5 2571 14/10/2010 1:00 Pm
6 2571 14/10/2010 1:00 Pm
7 2571 15/10/2010 2:00 Pm
8 2571 17/10/2010 1:00 Pm

i need to show data in this format

Code intime outtime
2570 14/10/2010 1:00 Pm 14/10/2010 2:00 Pm
2570 15/10/2010 7:00 Pm 15/10/2010 4:00 Pm
2571 14/10/2010 1:00 Pm 14/10/2010 1:00 Pm
2571 15/10/2010 2:00 Pm 15/10/2010 2:00 Pm
2571 17/10/2010 1:00 Pm 17/10/2010 1:00 Pm

4
Contributors
3
Replies
4
Views
7 Years
Discussion Span
Last Post by tesuji
0

Hi below query might help you acheive your desired results...
select code,max(ptime),min(ptime)
from dailyatt
group by code;

Hi i am new in oracle. i am having a problem. i need to collect datewise max time and min time from my table "dailyAtt"
here is the data formate in my table..

ID Code Ptime
1 2570 14/10/2010 1:00 Pm
2 2570 14/10/2010 2:00 Pm
3 2570 15/10/2010 7:00 Am
4 2570 15/10/2010 4:00 Pm
5 2571 14/10/2010 1:00 Pm
6 2571 14/10/2010 1:00 Pm
7 2571 15/10/2010 2:00 Pm
8 2571 17/10/2010 1:00 Pm

i need to show data in this format

Code intime outtime
2570 14/10/2010 1:00 Pm 14/10/2010 2:00 Pm
2570 15/10/2010 7:00 Pm 15/10/2010 4:00 Pm
2571 14/10/2010 1:00 Pm 14/10/2010 1:00 Pm
2571 15/10/2010 2:00 Pm 15/10/2010 2:00 Pm
2571 17/10/2010 1:00 Pm 17/10/2010 1:00 Pm

0

Hello

There is something more to do.

Hint: First add a column to your table which simply indicates present and absent booking timestamps. Below is an example of a table with computed column isPresent and ordered by PersonalNumber what I had already published elsewhere:

/*
PersonalNumber  BookingStamp             isPresent
--------------------------------------------------
1000            2009-08-11 07:12:05.000  1
1000            2009-08-11 09:10:00.000  0
1000            2009-08-11 10:08:29.000  1
1000            2009-08-11 18:31:01.000  0
2000            2009-08-11 08:56:00.000  1
2000            2009-08-11 19:00:00.000  0
2000            2009-09-09 09:08:07.000  1
2000            2009-09-09 15:18:00.000  0
3000            2009-08-10 07:30:05.000  1
3000            2009-08-10 17:00:57.000  0
3000            2009-08-11 12:12:12.000  1
*/

If this additional column is given, selecting present and absent booking timestamps in same row together with corresponding personal number can simply be achieved by various methods depending on database.

-- tesu

Edited by tesuji: n/a

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.