how to calculate datewise max time and min time?

Question

axas 0 Newbie Poster

14 Years Ago

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

oracle

4 Contributors
3 Replies
139 Views
1 Day Discussion Span
Latest Post 14 Years Ago Latest Post by tesuji

debasisdas 580 Posting Genius

14 Years Ago

Kindly post the code that you are working on.

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

yash_792 0 Newbie Poster · Answer 1 · 2010-08-17T20:43:36+00:00

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

tesuji 135 Master Poster · Answer 2 · 2010-08-18T02:07:33+00:00

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