Employee active status for all Mondays in the year.

You really only have to calculate the first Monday date -- other Mondays can be calc'd by adding 7 day mutliples to that date.

For example:


IF OBJECT_ID(N'tempdb..#lawson_temp') IS NOT NULL
    DROP TABLE #lawson_temp
CREATE TABLE #lawson_temp (
    employee_nbr int,
      date_hire datetime,
      term_date datetime,
    )
INSERT INTO #lawson_temp
SELECT 70123, '20110112', '20130511'
UNION ALL
SELECT 72301, '20120416', '20130118'
/*
70123,2013-01-07 00:00:00.000, 0
70123,2013-01-14 00:00:00.000, 1
70123,2013-01-21 00:00:00.000, 1
71230,2013-01-07 00:00:00.000, 1
71230,2013-01-14 00:00:00.000, 1
71230,2013-01-21 00:00:00.000, 0
72301,2013-01-07 00:00:00.000, 1
72301,2013-01-14 00:00:00.000, 0
72301,2013-01-21 00:00:00.000, 0
*/            

DECLARE @start_date datetime
DECLARE @end_date datetime

SET @end_date = DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
SET @start_date = DATEADD(DAY, 1, DATEADD(YEAR, -2, @end_date))
            
;WITH cte_First_Monday AS (
    SELECT DATEADD(DAY, DATEDIFF(DAY, 0, DATEADD(DAY, 6, @start_date)) / 7 * 7, 0) AS first_monday
),
cte_Digits AS (
    SELECT 0 AS digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
    SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL
    SELECT 8 UNION ALL SELECT 9
),
cte_Tally AS (
    SELECT ones.digit + tens.digit * 10 + hundreds.digit * 100 AS tally
    FROM cte_Digits ones
    CROSS JOIN cte_Digits tens
    INNER JOIN cte_Digits hundreds ON
        hundreds.digit <= (DATEDIFF(DAY, @start_date, @end_date) + 1) / 7
    WHERE
        ones.digit + tens.digit * 10 + hundreds.digit * 100 BETWEEN 0 AND (DATEDIFF(DAY, @start_date, @end_date) + 1) / 7
)
SELECT
    lt.Employee_Nbr,
    DATEADD(DAY, ct.tally * 7, cfm.first_monday) AS Monday_Date,
    CASE WHEN lt.date_hire <= DATEADD(DAY, ct.tally * 7, cfm.first_monday)
          AND (lt.term_date IS NULL OR lt.term_date >= DATEADD(DAY, ct.tally * 7, cfm.first_monday))
         THEN 1 ELSE 0 END AS BitStatus,
    lt.date_hire, --for testing, to verify BitStatus, remove later
    lt.term_date --for testing, to verify BitStatus, remove later
FROM cte_Tally ct
CROSS JOIN cte_First_Monday cfm
LEFT OUTER JOIN #lawson_temp lt ON
    lt.date_hire <= @end_date
--SELECT      employee_nbr,            date_hire,            term_date FROM      lawson_temp
ORDER BY
    lt.employee_nbr, ct.tally

Having posted the above I realize you didn't want a summary (sorry, bad assumption)
So here's the table of details using the same basic structure

-- set number of years
declare @numYears as int
set @numYears = 2


-- next get recent Monday
declare @BeginsAt as datetime

-- truncate time from getdate()
set @BeginsAt = dateadd(day, datediff(day,0, getdate() ), 0)

-- get Monday using modulus
set @BeginsAt = dateadd(day,(-datediff(day,0,@BeginsAt) % 7),@BeginsAt) 


;with Ranger (id, StartAt)
as (
    /* recursively build CTE of Mondays */
    select 1 as id, @BeginsAt
    union all
    select (id + 1) , dateadd(day,-7,StartAt)
    from Ranger
    where dateadd(day,-7,StartAt) >= dateadd(year,-@numYears,@BeginsAt)
    )
select
    employee_nbr
  , startat as [Date]
  , case
        when startat >= date_hire and startat < term_date then 1 
        when startat >= date_hire and term_date is null   then 1
        else 0
     end 
     as bitStatus
from Ranger
cross join lawson_temp
-- order by bitStatus, employee_nbr -- as required

OPTION ( MAXRECURSION 110 ) --  >= (@Numyears * 55)

Select all Open in new window

this variant http://sqlfiddle.com/#!3/f6859/4