I am still working on a solution to this, (I thought I had something but just saw your reply to Jeff)...

In the meantime I came up with a better way to populate your payroll schedule table...

First the function:

IF OBJECT_ID('tempdb.dbo.GetDupNums') IS NOT NULL DROP FUNCTION dbo.GetDupNums;

GO

CREATE FUNCTION dbo.GetDupNums(@Dups tinyint, @MaxN int, @ZeroOrOne bit)

RETURNS TABLE WITH SCHEMABINDING AS

RETURN

/*

Purpose:

Return a column of ints from 1 through @MaxN;

The columns will be duplicated (@dups) number of times (e.g. @dups = 2, you get two 1's, two 2's, etc...);

It also includes a psuedo ROW_NUMBER() column starting at @ZeroOrOne

Usage:

-- Syntax example (Returns INT)

SELECT RowNum, N

FROM dbo.GetDupNums(@Dups, @MaxN, @ZeroOrOne)

Notes:

0. This function is based on Itzek Ben-Gan's getnumbers function and Jeff Moden's fnTally() function

1. This is my BETA version of the function (still testing & updating)

2. Will work with SQL Server 2005+

3. The function also returns a PSEUDO-ROW_NUMBER() column (why not?)

4. To start a sequence at 0, @ZeroOrOne must be 0 or NULL. Any other value that's convertable to the BIT data-type

will cause the sequence to start at 1.

5. A negetive number will break it (as it will has an improper value to be passed to the top clause)

5a. This can be fixed by replacing the TOP clause by use ROWS instead

(This would, however, make the function SQL Server 2012+ compatible)

5b. Alternatively you could wrap and ABS around the TOP clause then include a "WHERE N < @Dups*@maxN" clause

Either fix would return 0 rows instead of throwing an error

6. Made dups tinyint to reduce enforce a non-negetive int (and 255 should be enough)

7. DONT Sort by either column unless you must! This will decrease performance

- I'm working on this... just put this function together, still considering the best way to deal with that

- It DOES return the rows in order (but as we all know, an ORDER BY is required for a sort order is to be guaranteed).

Revision History:

Rev 00 - 3/27/2015 - Alan Burstein

*/

WITH

L1(N) AS

( SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1), --8

L3(N) AS (SELECT 1 FROM L1 a, L1 b, L1 c), -- 512

TallyOh(N) AS

( SELECT 0 UNION ALL

SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))

FROM L3 a, L3 b, L3 c ) -- 134,217,728 (+1 for oh)

SELECT TOP (@Dups*@maxN)

RowNum = N+ISNULL(@ZeroOrOne,0),

N = (N/@Dups)+1

FROM TallyOh;

GO

And how you would use it to create the pay periods table (note: with a function like this it is easy to create multiple years and do so in nasty fast fashion.

DECLARE @payrollyear smallint = 2014;

DECLARE @Dups int = 2, @MaxN int = 12;

SELECT

PayrollYear = @payrollyear,

ParyPeriod = RowNum,

PayPeriodStart =

CASE RowNum%2

WHEN 1 THEN dateadd(month,n-1,concat('1/1/',@payrollyear))

ELSE dateadd(month,n-1,concat('1/16/',@payrollyear))

END,

PayPeriodEnd =

CASE RowNum%2

WHEN 1 THEN dateadd(month,n-1,concat('1/15/',@payrollyear))

ELSE dateadd(day,-1,dateadd(month,n,concat('1/1/',@payrollyear)))

END

FROM dbo.GetDupNums(@Dups, @MaxN, 1);

It's worth noting too (for anyone else you is working on this) that this query:

SELECT *

FROM @PayrollSchedule ps

LEFT JOIN @EmployeeStatus es

ON es.EffectiveDate >= ps.PayPeriodStart

AND es.EffectiveDate <= ps.PayPeriodEnd;

gets you pretty close. For example it lets us know that employee 11 was full time on 3-1 through 3-15 and part time beginning 7-16 through 7-31.

Again, still pluggin away...

Edit: Comment Typo...