You can try this. Had to make some assumptions around Jeff's excellent questions.

declare @EmployeeStatus TABLE (

[EmployeeID] [int] NOT NULL,

[EmployeeStatus] [varchar](24) NOT NULL,

[EffectiveDate] [date] NOT NULL

)

INSERT INTO @EmployeeStatus VALUES (11, 'full time', '2014-03-01');

INSERT INTO @EmployeeStatus VALUES (11, 'part time', '2014-07-21');

INSERT INTO @EmployeeStatus VALUES (11, 'full time', '2014-09-15');

INSERT INTO @EmployeeStatus VALUES (11, 'termed', '2014-11-22');

INSERT INTO @EmployeeStatus VALUES (22, 'full time', '2013-03-06');

INSERT INTO @EmployeeStatus VALUES (22, 'part time', '2014-09-21');

INSERT INTO @EmployeeStatus VALUES (22, 'full time', '2014-12-19');

INSERT INTO @EmployeeStatus VALUES (33, 'full time', '2013-06-18');

INSERT INTO @EmployeeStatus VALUES (44, 'full time', '2004-01-01');

INSERT INTO @EmployeeStatus VALUES (44, 'part time', '2014-08-21');

INSERT INTO @EmployeeStatus VALUES (44, 'full time', '2014-11-15');

INSERT INTO @EmployeeStatus VALUES (55, 'full time', '2014-05-12');

DECLARE @PayrollSchedule TABLE (

[PayrollYear] [smallint] NOT NULL,

[PayPeriod] [smallint] NOT NULL,

[PayPeriodStart] [date] NOT NULL,

[PayPeriodEnd] [date] NOT NULL

)

DECLARE @PayrollYear smallint = 2014;

INSERT INTO @PayrollSchedule VALUES (@PayrollYear, 1, '2014-01-01', '2014-01-15');

INSERT INTO @PayrollSchedule VALUES (@PayrollYear, 2, '2014-01-16', '2014-01-31');

INSERT INTO @PayrollSchedule VALUES (@PayrollYear, 3, '2014-02-01', '2014-02-15');

INSERT INTO @PayrollSchedule VALUES (@PayrollYear, 4, '2014-02-16', '2014-02-28');

INSERT INTO @PayrollSchedule VALUES (@PayrollYear, 5, '2014-03-01', '2014-03-15');

INSERT INTO @PayrollSchedule VALUES (@PayrollYear, 6, '2014-03-16', '2014-03-31');

INSERT INTO @PayrollSchedule VALUES (@PayrollYear, 7, '2014-04-01', '2014-04-15');

INSERT INTO @PayrollSchedule VALUES (@PayrollYear, 8, '2014-04-16', '2014-04-30');

INSERT INTO @PayrollSchedule VALUES (@PayrollYear, 9, '2014-05-01', '2014-05-15');

INSERT INTO @PayrollSchedule VALUES (@PayrollYear, 10, '2014-05-16', '2014-05-31');

INSERT INTO @PayrollSchedule VALUES (@PayrollYear, 11, '2014-06-01', '2014-06-15');

INSERT INTO @PayrollSchedule VALUES (@PayrollYear, 12, '2014-06-16', '2014-06-30');

INSERT INTO @PayrollSchedule VALUES (@PayrollYear, 13, '2014-07-01', '2014-07-15');

INSERT INTO @PayrollSchedule VALUES (@PayrollYear, 14, '2014-07-16', '2014-07-31');

INSERT INTO @PayrollSchedule VALUES (@PayrollYear, 15, '2014-08-01', '2014-08-15');

INSERT INTO @PayrollSchedule VALUES (@PayrollYear, 16, '2014-08-16', '2014-08-31');

INSERT INTO @PayrollSchedule VALUES (@PayrollYear, 17, '2014-09-01', '2014-09-15');

INSERT INTO @PayrollSchedule VALUES (@PayrollYear, 18, '2014-09-16', '2014-09-30');

INSERT INTO @PayrollSchedule VALUES (@PayrollYear, 19, '2014-10-01', '2014-10-15');

INSERT INTO @PayrollSchedule VALUES (@PayrollYear, 20, '2014-10-16', '2014-10-31');

INSERT INTO @PayrollSchedule VALUES (@PayrollYear, 21, '2014-11-01', '2014-11-15');

INSERT INTO @PayrollSchedule VALUES (@PayrollYear, 22, '2014-11-16', '2014-11-30');

INSERT INTO @PayrollSchedule VALUES (@PayrollYear, 23, '2014-12-01', '2014-12-15');

INSERT INTO @PayrollSchedule VALUES (@PayrollYear, 24, '2014-12-16', '2014-12-31');

--SELECT * FROM @PayrollSchedule

--SELECT * FROM @EmployeeStatus;

WITH Employees AS

(

SELECT EmployeeID, MinDate=MIN(EffectiveDate)

FROM @EmployeeStatus

GROUP BY EmployeeID

)

SELECT EmployeeID, PayPeriod, PayPeriodStart

,StatusAtPayPeriodStart=d.EmployeeStatus

,EffectiveDateAtPayPeriodStart=d.EffectiveDate

,PayPeriodEnd

,StatusAtPayPeriodEnd=c.EmployeeStatus

,EffectiveDateAtPayPeriodEnd=c.EffectiveDate

FROM @PayrollSchedule a

CROSS APPLY

(

SELECT EmployeeID

FROM Employees b

WHERE MinDate <= PayPeriodEnd

) b

OUTER APPLY

(

-- Status at pay period end

SELECT TOP 1 EffectiveDate, EmployeeStatus

FROM @EmployeeStatus c

WHERE b.EmployeeID = c.EmployeeID AND c.EffectiveDate <= PayPeriodEnd

ORDER BY c.EffectiveDate DESC

) c

OUTER APPLY

(

-- Status at pay period start

SELECT TOP 1 EffectiveDate, EmployeeStatus

FROM @EmployeeStatus d

WHERE b.EmployeeID = d.EmployeeID AND d.EffectiveDate <= PayPeriodStart

ORDER BY d.EffectiveDate DESC

) d

WHERE ISNULL(d.EmployeeStatus, '') <> 'termed'

ORDER BY EmployeeID

I also added EmployeeID=55 to test one case you didn't include.