Hi all,

I am facing problems in implemting the below requirement.
Can anyone help me out in solving this out.

Create table test
( county_id varchar(10),
Class int,
Status varchar(10),
begindate datetime
)
truncate table test

insert into test select 'India',5,'B001','1/2/2005'
insert into test select 'India',5,'B001','1/5/2007'
insert into test select 'India',6,'B001','2/7/2008'
insert into test select 'India',7,'B001','3/7/2008'
insert into test select 'India',8,'B001','4/7/2008'


insert into test select 'Australia',5,'B001','1/2/2005'
insert into test select 'Australia',5,'B001','1/5/2007'
insert into test select 'Australia',6,'B001','2/7/2008'
insert into test select 'Australia',7,'B001','3/7/2008'
insert into test select 'Australia',8,'B001','4/7/2008'

Basically the test table contains student information studying in a school and we need to track of how many students are moving from one class to another class.
Consider all the records having same Country ID’s.
Then for each countryID record set( ie Group by), we need perform the following.
Take first 2 records from the record set in each group then consider the student class columns of the 2 records.
If the studentGradeLevels are different then we need to consider the "Status" column of the 2 records.
And if the "Status" of the 2 records is 'B001' then we need get the earliest "begindate" of the 2 records.
Once we get the record having earliest "begindate", then for that particular class/standard we make +1 entry to the "OUT" column of the current Class and +1 to the "IN" column for the second highest earlyenddate record gradeLevel
in the TARGET Table. This process continues for all unique countryID record sets.
In the above example has only 2 sets i.e India & Australia.


create table target
(
stateID varchar(20),
classID int,
OUT int,
IN int
)


-- Sample Output should like

Stateid ClassID Out IN
--------------------------------------------------
India 5 1 -

India 6 1 1

India 7 1 1

India 8 - 1

Australia 5 1 -

Australia 6 1 1

Australia 7 1 1

Australia 8 - 1

Thanks & Regards