Home » SQL & PL/SQL » SQL & PL/SQL » Query for retrieving date records (Oracle 10g)
Query for retrieving date records [message #286554] Sat, 08 December 2007 03:47 Go to next message
thisisnatesh
Messages: 92
Registered: March 2007
Member
Hi

I have a table which has three records.

date1 date2

05-10-07 15-10-07

10-10-07 20-10-07

01-10-07 03-10-07

Now i want to display in the following way

date1 date2 count1

01-10-07 03-10-07 3

05-10-07 20-10-07 16

at the end i should get the records for dates an employee worked and total no of days.

Thanks in advance

Natesh


[Updated on: Sat, 08 December 2007 03:54]

Report message to a moderator

Re: Query for retrieving date records [message #286559 is a reply to message #286554] Sat, 08 December 2007 04:02 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member

what have you tried ? can you post that.and format your code.


regards,
Re: Query for retrieving date records [message #286560 is a reply to message #286554] Sat, 08 December 2007 04:04 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
AskTom "Counting the number of business days between 2 dates"

Regards
Michel
Re: Query for retrieving date records [message #286561 is a reply to message #286560] Sat, 08 December 2007 04:22 Go to previous messageGo to next message
thisisnatesh
Messages: 92
Registered: March 2007
Member
Thanks Mic for the info.
Gone through the page and i found
my requirement is different.

if u see my example data, first two records were like

05-10-07 15-10-07

10-10-07 20-10-07

for this kind of data resultant record should be

05-10-07 20-10-07

so it is like merging the dates.

when u see my 3rd record which is "01-10-07 03-10-07" ,
date values cannot be merged in the first record because
there is one date value "04-10-07" missing.

Finally query should display records like

05-10-07 20-10-07
01-10-07 03-10-07

Hope it is clearer for u now.

Dhananjay, please u also look into the requirement

Thanks
Natesh

Re: Query for retrieving date records [message #286562 is a reply to message #286561] Sat, 08 December 2007 04:42 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member

may be this should get you started.

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:12864646978683

regards
Re: Query for retrieving date records [message #286564 is a reply to message #286562] Sat, 08 December 2007 04:48 Go to previous messageGo to next message
thisisnatesh
Messages: 92
Registered: March 2007
Member
Thanks Dhananjay!!! Looking into the article. hope it helps in my case.

Thanks & Regards
Natesh
Re: Query for retrieving date records [message #286582 is a reply to message #286561] Sat, 08 December 2007 07:32 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
If you post a test case (create table and insert statements) and format your post as stated in OraFAQ Forum Guide, I'll show you how to do it.

Regards
Michel
Re: Query for retrieving date records [message #286620 is a reply to message #286561] Sun, 09 December 2007 01:04 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
drop table t purge;
create table t (date1 date, date2 date);
truncate table t;
insert into t 
select dt-dbms_random.value(1,9) date1, dt date2
from (select sysdate-dbms_random.value(1+5*level,3+5*level) dt 
      from dual connect by level <= 10)
/
select * from t order by date1, date2;
with
  step1 as (
    select date1, date2,
           case 
             when nvl(lag(date2) over (order by date1, date2), date1-1) < date1 
               then row_number() over (order by date1, date2)
           end flag
    from t
  ),
  step2 as (
    select date1, date2,
           max(flag) over (order by date1, date2) grp
    from step1
  )
select min(date1) date1, max(date2) date2, 
       round(max(date2)-min(date1)) daycount
from step2
group by grp
order by 1
/

SQL> select * from t order by date1, date2;
DATE1               DATE2
------------------- -------------------
10/10/2007 20:03:54 17/10/2007 10:45:01
17/10/2007 03:41:23 24/10/2007 01:19:57
22/10/2007 14:22:38 29/10/2007 07:28:51
24/10/2007 03:42:28 01/11/2007 22:07:53
02/11/2007 09:51:21 08/11/2007 04:38:54
03/11/2007 16:45:14 12/11/2007 00:54:21
14/11/2007 22:18:59 17/11/2007 04:09:06
19/11/2007 23:22:50 22/11/2007 00:06:18
24/11/2007 00:41:40 02/12/2007 21:07:17
25/11/2007 13:16:07 27/11/2007 09:15:50

10 rows selected.

SQL> with
  2    step1 as (
  3      select date1, date2,
  4             case 
  5               when nvl(lag(date2) over (order by date1, date2), date1-1) < date1 
  6                 then row_number() over (order by date1, date2)
  7             end flag
  8      from t
  9    ),
 10    step2 as (
 11      select date1, date2,
 12             max(flag) over (order by date1, date2) grp
 13      from step1
 14    )
 15  select min(date1) date1, max(date2) date2, 
 16         round(max(date2)-min(date1)) daycount
 17  from step2
 18  group by grp
 19  order by 1
 20  /
DATE1               DATE2                 DAYCOUNT
------------------- ------------------- ----------
10/10/2007 20:03:54 01/11/2007 22:07:53         22
02/11/2007 09:51:21 12/11/2007 00:54:21         10
14/11/2007 22:18:59 17/11/2007 04:09:06          2
19/11/2007 23:22:50 22/11/2007 00:06:18          2
24/11/2007 00:41:40 02/12/2007 21:07:17          9

5 rows selected.

Execute each step and come back if you don't understand something.

Regards
Michel
Re: Query for retrieving date records [message #286699 is a reply to message #286554] Sun, 09 December 2007 22:21 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Nice Solution Michel !!!! Nice logic !!!!

But this time you couldn't resist your self from answering OP despite of 'No response' from him after your secod last Posting Smile

Anyway Nice demonstration ... Nod

Thumbs Up
Rajuvan.

Re: Query for retrieving date records [message #286706 is a reply to message #286699] Sun, 09 December 2007 23:24 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
This is Expert forum and solution can't be found with just a clue. You can notice I let OP work on the query to find how it works (as you did). I don't think this is spoonfeeding as the query is useless if you don't understand it.
And OP answered me on my second post in another topic (in Newbies forum).

Regards
Michel

Re: Query for retrieving date records [message #286722 is a reply to message #286554] Mon, 10 December 2007 00:25 Go to previous message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

I was unaware of those cases Confused

But still i stand on my previous remark.
Nice Solution Michel !!!! Nice logic !!!!


Thumbs Up
Rajuvan.
Previous Topic: problem with to_number
Next Topic: Generate Time slots
Goto Forum:
  


Current Time: Sun Feb 09 21:02:42 CST 2025