December 8, 2009 at 1:24 pm
Hello,
I am working with Entry/Exit records and trying to compact them into a singular record. Below is a sample of data:
Student_ID Effective_Date Admit_Withdraw_Ind
1 12/22/2004 A
1 5/23/2005 W
1 5/26/2005 A
2 1/3/2007 A
2 5/27/2008 W
3 8/25/2008 A
When compacted, the data should end up looking like below:
Student_ID Entry_Date Withdrawal_Date
1 12/22/2004 5/23/2005
1 5/23/2005 (NULL) --he is still enrolled
2 1/3/2007 5/27/2008
3 8/25/2008 (NULL) --he is still enrolled
I am trying to split these out in SSIS. I've done something similar before with assigning a sequence to the entry/exit records, ordering by student_id, and then linking the entry/withdrawal records together.
Does anyone have any ideas as to the best way to accomplish this? I've thought about using the ROW_NUMBER() function, but am not sure that this would be the best approach for me to take.
Thanks in advance for the help!
December 8, 2009 at 1:48 pm
Using the row_number() function is what I came up with here:
create table dbo.StudentEnrollment (
StudentID int,
EffectiveDate date,
AdmitWithdrawInd char(1)
);
insert into dbo.StudentEnrollment
select 1, '2004-12-22', 'A' union all
select 1, '2005-05-23', 'W' union all
select 1, '2005-05-26', 'A' union all
select 2, '2007-01-03', 'A' union all
select 2, '2008-05-27', 'W' union all
select 3, '2008-08-25', 'A'
;
with StudentEnroll as (
select
ROW_NUMBER() over (partition by StudentID order by EffectiveDate) as RowNum,
StudentID,
EffectiveDate,
AdmitWithdrawInd
from
dbo.StudentEnrollment
)
select
se1.StudentID,
se1.EffectiveDate,
se2.EffectiveDate
from
StudentEnroll se1
left outer join StudentEnroll se2
on (se1.RowNum + 1 = se2.RowNum
and se1.StudentID = se2.StudentID)
where
se1.AdmitWithdrawInd = 'A';
go
drop table dbo.StudentEnrollment;
go
December 9, 2009 at 8:19 am
That worked great -- just what I needed!
Thanks!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply