This will work in 2005. Only the test data has changed (& variable declaration).
Please note: You should post on a 2005 forum for 2005 answers 🙂
--===== TEST DATA =========
declare @Person table
(Id int, Name varchar(20), MartialStatus int, EmploymentStatus int, Email varchar(50), JobId int );
INSERT INTO @Person
( Id, Name, MartialStatus, EmploymentStatus, Email, JobId )
VALUES
(1, 'John', 8, 6, 'John@xxx.com', 99);
INSERT INTO @Person
( Id, Name, MartialStatus, EmploymentStatus, Email, JobId )
VALUES
(2, 'John', 10, 7, 'John@xxx.com', 100);
INSERT INTO @Person
( Id, Name, MartialStatus, EmploymentStatus, Email, JobId )
VALUES
(3, 'John', NULL, NULL, 'John@xxx.com', 101);
INSERT INTO @Person
( Id, Name, MartialStatus, EmploymentStatus, Email, JobId )
VALUES
(4, 'Max', 6, 5, 'max@emailreaction.org', 102);
--select * from @Person
declare @Job table
(JobId int, GroupId int, [Desc] varchar(30) );
INSERT INTO @Job
( JobId, GroupId, [Desc] )
VALUES
(99, 50, 'blah blah');
INSERT INTO @Job
( JobId, GroupId, [Desc] )
VALUES
(100, 50, 'blah blah');
INSERT INTO @Job
( JobId, GroupId, [Desc] )
VALUES
(101, 50, 'blah blah');
INSERT INTO @Job
( JobId, GroupId, [Desc] )
VALUES
(102, 51, 'blah blah');
--select * from @Job
--I want to retrieve a record from the person table based on the group id...
--for eg. for Group Id 50 I want the following record which is mix of record 3 and 2 as:
--------------------------------------
--3, John,10,7,John@xxx.com,101
---------------------------------------
--Record num 3 should be retrieved with the marital status and employment status from record two as they were null.
--====== SUGGESTED SOLUTION =========
declare @GroupId int;
set @GroupId = 50;
with CTE as
(
select
RowId = ROW_NUMBER() over (partition by p.Name order by p.JobId desc),
p.Id,
p.Name,
p.MartialStatus,
p.EmploymentStatus,
p.Email,
p.JobId
from @job j
inner join @Person p on j.jobId = p.JobId
where j.GroupId = @GroupId
)
select a.Id,
a.Name,
MartialStatus = coalesce(a.MartialStatus,b.MartialStatus,c.MartialStatus),
EmploymentStatus = coalesce(a.EmploymentStatus,b.EmploymentStatus,c.EmploymentStatus),
a.Email,
a.JobId
from CTE a
left outer join CTE b on a.RowId +1 = b.RowId
left outer join CTE c on a.RowId +2 = c.RowId
where a.RowId = 1;