Query for retrieving date records [message #286554] |
Sat, 08 December 2007 03:47  |
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 #286561 is a reply to message #286560] |
Sat, 08 December 2007 04:22   |
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 #286620 is a reply to message #286561] |
Sun, 09 December 2007 01:04   |
 |
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
|
|
|
|
|
|