Hi there,

I have a source table with following columns:

employer businessnumber added_date
1 sn01 2001/09/08
1 bn02 2004/07/02
2 tn01 2001/03/03
...

after conversion, I want to have something like:
employer businessnumber startdate enddate
1 sn01 2001/09/08 2004/07/01
1 bn02 2004/07/02 null
2 tn01 2001/03/03 null

so you see if source record is the last one for the employer, we'll use NULL as the enddate, if the source record is NOT the last one, we'll use the Added_date(from the next record) - 1 day.

I wonder if I can use some queries to archive this?

Thanks a lot.

I think something like this will get you what you want:


with t1 (employer, business_number, added_date, ordinal) as
(select employer,business+number,added_date,row_number() over(partition by employer order by added_date)
select t1.employer,t1.business_number,t1.added_date as start_date,
(select t2.added_date from t1 as t2 where t1.employer = t2.employer and t2.ordinal = (t1.ordinal + 1)) as enddate
from t1
order by 1,3

Andy