quote:

---

Originally posted by vijayisonly

What have you tried so far?

---

declare @positions table 
(positionid int, positiontitle varchar(20),organizationid int)

insert @positions
select 1,'director',1 union all
select 2,'asst director', 1 union all
select 3,'employee',1 

declare @PersonPositions table
(
PersonPositionID int,
PersonID int,
PositionID int,
AssignmentStartDate datetime,
AssignmentEndDate datetime
)
insert @PersonPositions
select 1,1,1,'2009-01-01','2009-06-01' union all
select 3,3,3,'2009-01-01','2009-12-31'

declare @Persons table
(
PersonID int,
LastName varchar(20),
FirstName varchar(20)
)

insert @Persons
select 1,'director','first' union all
select 3,'employee','first'

declare @Organizations table
(
OrganizationID int,
OrganizationName varchar(20)
)
insert @Organizations 
select 1,'OrgOne'

SELECT d.OrganizationName, 
       a.positiontitle, 
       CASE 
         WHEN b.PersonID IS NULL 
         THEN 'Position has never been filled' 
         ELSE CASE 
                WHEN datediff(mm,getdate(),b.AssignmentEndDate) > 0 
                THEN c.FirstName + ' ' + c.LastName 
                ELSE 'Position vacant for ' + cast(datediff(mm,b.AssignmentEndDate,getdate()) AS CHAR(4)) + 'months ' 
              END 
       END, 
       b.AssignmentStartDate, 
       b.AssignmentEndDate 
FROM   @positions a 
       LEFT JOIN @PersonPositions b 
         ON a.positionid = b.PositionID 
       LEFT JOIN @Persons c 
         ON b.PersonID = c.PersonID 
       LEFT JOIN @Organizations d 
         ON a.organizationid = d.OrganizationID

OrganizationName     positiontitle                                                  AssignmentStartDate     AssignmentEndDate
-------------------- -------------------- ----------------------------------------- ----------------------- -----------------------
OrgOne               director             Position vacant for 2   months            2009-01-01 00:00:00.000 2009-06-01 00:00:00.000
OrgOne               asst director        Position has never been filled            NULL                    NULL
OrgOne               employee             first employee                            2009-01-01 00:00:00.000 2009-12-31 00:00:00.000