Hi Tom,
I have been on your site numerous times and learnt a lot from your opinions and solutions.
I am looking for SQL that displays in chronological order in which country "Tom" lived.
Below are my details:
--create table.
create table t02(
fname varchar2(10),
city varchar2(10),
country varchar2(5),
start_date date,
end_date date);
/
insert into t02 values('Tom','Boston','US','01-JAN-2001', '31-JUL-2001');
insert into t02 values('Tom','Nashua','US','01-AUG-2001', '31-DEC-2001');
insert into t02 values('Tom','New York','US','01-JAN-2002', '31-DEC-2002');
insert into t02 values('Tom','Mumbai','IND','01-JAN-2003', '31-DEC-2003');
insert into t02 values('Tom','Dallas','US','01-JAN-2004', '31-DEC-2004');
insert into t02 values('Tom','Austin','US','01-JAN-2005', '31-DEC-2006');
/
commit;
/
select * from t02
order by start_date
/
fname City Country start_Date End_date
------- ------ --------- ----------- ---------
Tom Boston US 01-JAN-01 31-JUL-01
Tom Nashua US 01-AUG-01 31-DEC-01
Tom New York US 01-JAN-02 31-DEC-02
Tom Mumbai IND 01-JAN-03 31-DEC-03
Tom Dallas US 01-JAN-04 31-DEC-04
Tom Austin US 01-JAN-05 31-DEC-06
/I would like to have sql that shows in which country Tom lived in the chronological order
for e.g:
The output I am expecting from sql is below:
fname Country start_Date End_date
------- -------- ----------- ---------
Tom US 01-JAN-01 31-DEC-02
Tom IND 01-JAN-03 31-DEC-03
Tom US 01-JAN-04 31-DEC-06
Thanks in Advance.
Tabibitosan to the rescue!
You can use this to group together consecutive rows. To do this here, you need to calculate two things:
- A row number for each fname, ordered by (start) date
- A row number for each fname and country also ordered by (start) date
e.g.:
select row_number() over (partition by fname order by start_date) rn,
row_number() over (partition by fname, country order by start_date) crn,
t.*
from t02 t;
RN CRN FNAME CITY COUNTRY START_DATE END_DATE
1 1 Tom Boston US 01-JAN-2001 00:00:00 31-JUL-2001 00:00:00
2 2 Tom Nashua US 01-AUG-2001 00:00:00 31-DEC-2001 00:00:00
3 3 Tom New York US 01-JAN-2002 00:00:00 31-DEC-2002 00:00:00
4 1 Tom Mumbai IND 01-JAN-2003 00:00:00 31-DEC-2003 00:00:00
5 4 Tom Dallas US 01-JAN-2004 00:00:00 31-DEC-2004 00:00:00
6 5 Tom Austin US 01-JAN-2005 00:00:00 31-DEC-2006 00:00:00 The consecutive rows are those where the difference between these values is the same. So you can group them by subtracting one from the other.
Do this and you have a simple group by name, country and group. The dates are the min start and max end in each:
with rws as (
select row_number() over (partition by fname order by start_date) -
row_number() over (partition by fname, country order by start_date) grp,
t.*
from t02 t
)
select fname, country,
min(start_date) st,
max(end_date) en
from rws r
group by fname, country, grp
order by min(start_date) ;
FNAME COUNTRY ST EN
Tom US 01-JAN-2001 00:00:00 31-DEC-2002 00:00:00
Tom IND 01-JAN-2003 00:00:00 31-DEC-2003 00:00:00
Tom US 01-JAN-2004 00:00:00 31-DEC-2006 00:00:00