Tabibitosan Method: Example 4
Collecting date range based on group:
create table ex5 (date_worked,country)
as
select to_date('1-Nov-13','dd-Mon-yy'), 'United Kingdom' from dual union all
select to_date('4-Nov-13','dd-Mon-yy'), 'United Kingdom' from dual union all
select to_date('5-Nov-13','dd-Mon-yy'), 'India' from dual union all
select to_date('6-Nov-13','dd-Mon-yy'), 'India' from dual union all
select to_date('7-Nov-13','dd-Mon-yy'), 'India' from dual union all
select to_date('8-Nov-13','dd-Mon-yy'), 'United Kingdom' from dual union all
select to_date('11-Nov-13','dd-Mon-yy'), 'United Kingdom' from dual union all
select to_date('12-Nov-13','dd-Mon-yy'), 'India' from dual union all
select to_date('13-Nov-13','dd-Mon-yy'), 'India' from dual union all
select to_date('14-Nov-13','dd-Mon-yy'), 'India' from dual union all
select to_date('15-Nov-13','dd-Mon-yy'), 'United Kingdom' from dual union all
select to_date('18-Nov-13','dd-Mon-yy'), 'United Kingdom' from dual union all
select to_date('19-Nov-13','dd-Mon-yy'), 'India' from dual union all
select to_date('20-Nov-13','dd-Mon-yy'), 'India' from dual union all
select to_date('21-Nov-13','dd-Mon-yy'), 'India' from dual union all
select to_date('22-Nov-13','dd-Mon-yy'), 'United Kingdom' from dual union all
select to_date('25-Nov-13','dd-Mon-yy'), 'United Kingdom' from dual union all
select to_date('26-Nov-13','dd-Mon-yy'), 'India' from dual union all
select to_date('27-Nov-13','dd-Mon-yy'), 'India' from dual union all
select to_date('28-Nov-13','dd-Mon-yy'), 'India' from dual union all
select to_date('29-Nov-13','dd-Mon-yy'), 'United Kingdom' from dual
/
SQL> select * from ex5;
DATE_WORK COUNTRY
--------- --------------
01-NOV-13 United Kingdom
04-NOV-13 United Kingdom
05-NOV-13 India
06-NOV-13 India
07-NOV-13 India
08-NOV-13 United Kingdom
11-NOV-13 United Kingdom
12-NOV-13 India
13-NOV-13 India
14-NOV-13 India
15-NOV-13 United Kingdom
18-NOV-13 United Kingdom
19-NOV-13 India
20-NOV-13 India
21-NOV-13 India
22-NOV-13 United Kingdom
25-NOV-13 United Kingdom
26-NOV-13 India
27-NOV-13 India
28-NOV-13 India
29-NOV-13 United Kingdom
21 rows selected.
Expected result:
COUNTRY START_DATE END_DATE
-------------- ---------- ----------
United Kingdom 01-11-2013 04-11-2013
India 05-11-2013 07-11-2013
United Kingdom 08-11-2013 11-11-2013
India 12-11-2013 14-11-2013
United Kingdom 15-11-2013 18-11-2013
India 19-11-2013 21-11-2013
United Kingdom 22-11-2013 25-11-2013
India 26-11-2013 28-11-2013
United Kingdom 29-11-2013 29-11-2013
SQL> select date_worked,
2 country,
3 row_number() over ( order by date_worked) rn,
4 row_number() over ( order by date_worked)-row_number() over (partition by country order
by date_worked) grp
5 from ex5;
DATE_WORK COUNTRY RN GRP
--------- -------------- ---------- ----------
01-NOV-13 United Kingdom 1 0
04-NOV-13 United Kingdom 2 0
05-NOV-13 India 3 2
06-NOV-13 India 4 2
07-NOV-13 India 5 2
08-NOV-13 United Kingdom 6 3
11-NOV-13 United Kingdom 7 3
12-NOV-13 India 8 4
13-NOV-13 India 9 4
14-NOV-13 India 10 4
15-NOV-13 United Kingdom 11 6
18-NOV-13 United Kingdom 12 6
19-NOV-13 India 13 6
20-NOV-13 India 14 6
21-NOV-13 India 15 6
22-NOV-13 United Kingdom 16 9
25-NOV-13 United Kingdom 17 9
26-NOV-13 India 18 8
27-NOV-13 India 19 8
28-NOV-13 India 20 8
29-NOV-13 United Kingdom 21 12
21 rows selected.
SQL> select min(date_worked),min(date_worked)
2 from
3 (
4 select date_worked,
5 country,
6 row_number() over ( order by date_worked) rn,
7 row_number() over ( order by date_worked)-row_number() over (partition by country order
by date_worked) grp
8 from ex5
9 )
10 group by grp
11 order by min(rn);
SQL> select country,min(date_worked),max(date_worked)
2 from
3 (
4 select date_worked,
5 country,
6 row_number() over ( order by date_worked) rn,
7 row_number() over ( order by date_worked)-row_number() over (partition by country order
by date_worked) grp
8 from ex5
9 )
10 group by country,grp
11 order by min(rn);
COUNTRY MIN(DATE_ MAX(DATE_
-------------- --------- ---------
United Kingdom 01-NOV-13 04-NOV-13
India 05-NOV-13 07-NOV-13
United Kingdom 08-NOV-13 11-NOV-13
India 12-NOV-13 14-NOV-13
United Kingdom 15-NOV-13 18-NOV-13
India 19-NOV-13 21-NOV-13
United Kingdom 22-NOV-13 25-NOV-13
India 26-NOV-13 28-NOV-13
United Kingdom 29-NOV-13 29-NOV-13
9 rows selected.
SQL>
No comments:
Post a Comment